Reason for SEQUENTIAL SCAN

Could someone help explain why the first SQL statement performs a SEQUENTIAL SCAN on 'qadq' and the second statement doesn't. Surely a sub select should be performed before hand so that the index is known? Is there any way to optimise such queries. Thanks Chris Koziol QUERY 1: ------ delete from qadg where tgpa in ( select tadg from aadg where tgpa = 3 ) Estimated Cost: 29 Estimated # of Rows Returned: 1 1) qadg: SEQUENTIAL SCAN 2) aadg: INDEX PATH (First Row) Filters: aadg.tgpa = 3 (1) Index Keys: tadg Lower Index Filter: aadg.tadg = qadg.tgpa NESTED LOOP JOIN (Semi Join) QUERY: ------ delete from qadg where tgpa in ( 3 ) Estimated Cost: 1 Estimated # of Rows Returned: 1 1) qadg: INDEX PATH (1) Index Keys: tgpa Lower Index Filter: qadg.tgpa = 3 ---------- Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Are your statistics up-to-date and at least at MEDIUM level? Also you have Optimization Goal set to FIRST_ROWS which may be affecting it. Art S. Kagel kozioldejanews@my-deja.com wrote: > > Could someone help explain why the first > SQL statement performs a SEQUENTIAL SCAN on > 'qadq' and the second statement doesn't. Surely a > sub select should be performed before hand so that > the index is known? Is there any way to optimise > such queries. > > Thanks > > Chris Koziol > > QUERY 1: > ------ > delete from qadg where tgpa in ( select tadg from > aadg where tgpa = 3 )> > Estimated Cost: 29 > Estimated # of Rows Returned: 1 > > 1) qadg: SEQUENTIAL SCAN > > 2) aadg: INDEX PATH (First Row) > > Filters: aadg.tgpa = 3 > > (1) Index Keys: tadg > Lower Index Filter: aadg.tadg = qadg.tgpa > NESTED LOOP JOIN (Semi Join) > > QUERY: > ------ > delete from qadg where tgpa in ( 3 )> > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > > 1) qadg: INDEX PATH > > (1) Index Keys: tgpa > Lower Index Filter: qadg.tgpa = 3 > > ---------- > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.