HELP: pb with a NESTED LOOPS join vs HASH JOIN...

Hi,

[Quoted] I have a puzzling pb with a SQL statement here: I do a SELECT with 3 EXISTS sub-queries, which takes ages to complete. As soon as I remove one of the EXISTS clauses, I get a reply within a few tenths of seconds.

Here is my statement (here with the 3 EXISTS):
> select count(1)
> from myTable1 f,
> myTable2 s
> where f.form_type = '5953'
> and exists (select 9
> from myTable3 v
> where f.num_form = v.num_form
> and v.field_id = 304
> and v.value_integer = '000503')
> AND Exists (select 9
> from myTable3 v
> where f.num_form = v.num_form
> and v.field_id = 303
> and v.value_string = 'DE')
> AND Exists (select 9
> from myTable3 v
> where f.num_form = v.num_form
> and v.field_id = 363
> and v.value_integer = '98')
> and f.num_form = s.num_form
> order by f.num_form, s.contextid;

I've noticed the following with an explain plan: when I keep the 3 EXISTS sub-queries, the optimizer performs a NESTED LOOPS join:
> FILTER
> NESTED LOOPS
> TABLE ACCESS FULL myTable1
> TABLE ACCESS FULL myTable2
> INDEX RANGE SCAN myTable3_NUMFRMFLDIDVALINT
> INDEX RANGE SCAN myTable3_NUMFRMFLDIDSTRING
> INDEX RANGE SCAN myTable3_NUMFRMFLDIDVALINT
> FILTER
> NESTED LOOPS
> ... [repeated 5 times]
> SORT AGGREGATE
> ...

but strangely as soon as I remove one of the EXISTS statements (when I remove any of them), I get the following:
> FILTER
> HASH JOIN
> TABLE ACCESS FULL myTable1
> TABLE ACCESS FULL myTable2
> INDEX RANGE SCAN myTable3_NUMFRMFLDIDSTRING
> INDEX RANGE SCAN myTable3_NUMFRMFLDIDVALINT
> FILTER
> HASH JOIN
> ... [idem: repeated 5 times]
> SORT AGGREGATE
> ...

Obviously the result is much quicker when the optimizer is set to HASH JOIN by Oracle, but my question is: how come Oracle switches to NESTED LOOPS when I have more than 2 EXISTS sub-queries ????

Thanks for any help !

Regards,
Seb

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 30 2000 - 15:47:22 CET