Select from a subquery

[ruimgp]

Hi all,

I want to do something like this

select f1,f2,f3
from
(select c1,c2,c3
from tab2 )

But it does not work

I´ve done this way

Select f1,f2,f3 from tab1 where EXISTS (select f1,f2,f3 from tab1 )

and it works, but when i cross diferent tables are the results realible ?

What i really want to do is a query where the first 2 fields work as key from the subquery ...


Thanks in advance.
R

 

[vpshriyan]

Hi,

A subquery need to be told which are the fields to be joined to get the tuples. In the following example; the select containing EXISTS keyword will be efficient because it can use the existing base indexes for the purpose.

create temp table tab1 (f1 smallint,f2 smallint,f3 smallint) with no log;
create temp table tab2 (x1 smallint,x2 smallint,x3 smallint) with no log;

insert into tab1 values (1,1,1);
insert into tab1 values (2,2,2);
insert into tab1 values (3,3,3);

insert into tab2 values (2,2,2);

select f1,f2,f3 from tab1 where exists (select x1 from tab2 where f1=x1 and f2=x2 and f3=x3);

select f1,f2,f3 from tab1 where f1||f2||f3 in (select x1||x2||x3 from tab2);

Regards,
Shriyan

 

[ruimgp]

Ok that work´s fine!

1)And if I want do so something like this?

Select f1,f2,f3 from tab1
where f1 = ( select max(f1) from tab1
where f4 > 0 )

Can somebody help me with the sintax, in Oracle this works fine but in Informix doesn´t work.
If I write this down, does it retrieve the correct information?

Select a.f1,a.f2,a.f3 from tab1 a
where a.f3 Exists ( select max(b.f3) from tab1 b
where a.f1=b.f1
and b.f4 > 0)

2)Just one more question
I do I know the cost of the query in order to optimize it, wich conditions it verifies first, etc.

Thanks in advance
Rui

 

[vpshriyan]

Hi Rui,

Select f1,f2,f3 from tab1
where f1 = ( select max(f1) from tab1
where f4 > 0 )

is syntaxwise perfect for informix; if your intention is to get the details of the row containing max value for column f1 and f4 is holding some positive value.

If one prefix SQL statement with "set explain on" statement, a file called sqexplain.out will be created at the current directory. It's contents shows estimated cost among other details. This cost is arrived by adding the cost factors involved in disk I/O and CPU cost. Roughly It can be transformed into the following formula.

Query Cost = (disk I/O cost) + W * (CPU cost)
W = Fixed Weightage factor value for processing each row.

Obviously the lesser the cost, faster will be the response time. But query optimization should not solely based on this criterion. Numerous other factors does affect the query response time. For example join method, number of tables involved, scan method, statistics avilability, indices presence, hardware speed, size of memory etc.etc.

Regards,
Shriyan