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

Maybe it is in one of the the INIT.ORA parameters that influence the optimizer (optimizer_search_level, number of permutations).

Try and experiment with them !

Toine.
<monsri_at_my-deja.com> schreef in berichtnieuws 8tk1ln$htf$1_at_nnrp1.deja.com...
> Hi,
>
> 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 - 22:25:55 CET