At 01:33 PM 3/09/2004 +0200, you wrote:

>Hi Set !
>
>Thanks for the help.
>
>
>From: "Svein Erling Tysvær" <svein.erling.tysvaer@...>
> >
> > inner join presence pres on (L1.id+0 = pres.outletid)
> > inner join sys_idlists_idx L2 on (pres.status = L2.id+0)
>
>That was it ! I tried this, but on the first join only. With both, the good
>plan is chosen.
>
>There is still something I don't quite understand. How come the optimizer
>can hesitate that way ? I find it scary because it makes things
>unpredictable. My app works ok for hours, then crawl, then come back to
>normal, etc... This without much change to data itself. The pb once occured
>right after a restore.
>
>Any ideas ?

The optimizer makes cost-based decisions when it chooses a plan. In
considering indexes on the basis of selectivity, it uses the index
statistics as calculated when the first user logged in. You have that
problem index there (low selectivity, and it will get worse as you add more
rows that mostly share a single value). Set's strategy, for making it
ignore that index, will help avoid this slow-down effect from the optimizer
mistakenly choosing that index, based on old statistics.

./helen