Re: Query tuning

Bruno D. wrote:

> What about this real examples:
>
> select ep.no_emiarte, e.titre_o_prg
> from entete_prog ep, entete e, remontage r
> where ep.no_emiarte=e.no_emiarte
> and ep.no_emiarte=r.no_emiarte
> and ep.remontage=r.remontage
> and ep.no_emiarte in ('002255-000'); ---------------------> 200 ms
>
> select ep.no_emiarte, e.titre_o_prg
> from entete_prog ep, entete e, remontage r
> where ep.no_emiarte=e.no_emiarte
> and ep.no_emiarte=r.no_emiarte
> and ep.remontage=r.remontage
> and ep.no_emiarte in (select '002255-000' from dual); -----> 13 seconds
> !!!!!!!

Interesting..

> In the second case, the 'explain plan' shows that oracle uses index no
> more !!!

Exactly. You have answered your own question. :-)

Obviously the execution plan is different given the significant difference in performance.

The actual question then is why does the Oracle CBO decide to use a different execution plan for these two queries. I believe the answer to that is what the Oracle CBO in your case mistakenly thinks is the cost for the subselect.

A work around can be to do away with the sub-select all together and make it part of the join.

E.g.
select
  t1.c1,
  t2.c2
from t1, t2, t3

where t1.c1 = t2.c2
and   t2.c2 = t3.c3 
and   t3.c3 like '%AAA%'

Other obvious question is if the relevant tables and indexes are analayzed?

--
Billy