Correlated subquery

Hi All, Is the following select stmt a correlated subquery:- In page:4-31 of "Informix Performance guide - Version 9.1 " it is mentioned as correlated subquery. select item from a where item in (select item from b where b.num = 50); Output of set explain shows that optimizer scans table "a" first, does this mean that for each record of table "a" examined by optimizer, it will execute the "select" on table b ??? QUERY: ------ select item from a where item in (select item from b where num = 777) Estimated Cost: 2 Estimated # of Rows Returned: 2 Maximum Threads: 0 1) informix.a: SEQUENTIAL SCAN Filters: informix.a.item = ANY <subquery> Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 2 Maximum Threads: 0 1) informix.b: SEQUENTIAL SCAN Filters: informix.b.num = 777 Why shouldn't optimizer scan table "b" first, store the value b.item in memory and then scan table "a". Thanks for your help in advance. Venky **** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

No, it is not a correlated subquery, since there is no 'correlation' (i.e. selection on inner based on outer values) between the tables. venkateshs@aol.com wrote in message ... >Hi All, > >Is the following select stmt a correlated subquery:- > >In page:4-31 of "Informix Performance guide - Version 9.1 " it is mentioned >as correlated subquery. > >select item from a > where item in (select item from b > where b.num = 50);> > > > >Output of set explain shows that optimizer scans table "a" first, does >this mean that >for each record of table "a" examined by optimizer, it will execute the >"select" on table b ??? > >QUERY: >------ >select item from a where item in (select item from b where num = 777)> >Estimated Cost: 2 >Estimated # of Rows Returned: 2 >Maximum Threads: 0 > > 1) informix.a: SEQUENTIAL SCAN > > Filters: informix.a.item = ANY <subquery> > > Subquery: > --------- > Estimated Cost: 1 > Estimated # of Rows Returned: 2 > Maximum Threads: 0 > > 1) informix.b: SEQUENTIAL SCAN > > Filters: informix.b.num = 777 > > >Why shouldn't optimizer scan table "b" first, store the value b.item in >memory and then >scan table "a". > > >Thanks for your help in advance. > > >Venky > > > > >**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

HI Buddy, I believe that your statement is not a correlated subquery. It is just having a subquery in which case informix will execute first the subquery and then execute the outer query for the results from subquery. TO get good performance it will be better to have index on num column in table b and item column in table a. Other than that informix should execute it pretty fast. Khem Chander kchande@yahoo.com