Subject Re: Slow query with 2 joins @L
Author emb_blaster
> This will not work. Since the IDX_UFoCmlswm8xwnGRmBa7/yg== index only refers to a field in the JOIN clause, it cannot be used when COPY_CLASSIFICATION is first in the PLAN.

ohh yeahh! I can see it... I'm now in my mind hearing something like Homer Simpson voice saying: "duhh!"

> > Deactivating the use of that index would not cause a slower query?
> The IDX_/F13/OidwbfyhwFbjRjfng== index refers to the COPY_ID field and cannot be used when this table is the first in the plan. What we want, is for the optimizer to choose the IDX_wOHPDCBu0ruVfoUjPp2ujg== index which refers to REPOSITORY_TYPE. Since REPOSITORY_TYPE is the only field in the WHERE clause, only an index on this field can be used for the first table in the PLAN (all other options include NATURAL).

hmm yeahh.. so, maybe this is the index that has stats problems rigth? Anyway it should update all the indexes stats, I guess.

> NATURAL*2 is of course bad, and that is the very reason for preventing the IDX_/F13/OidwbfyhwFbjRjfng== index. The optimizer should be intelligent enough not to choose such a plan and try other options. Hopefully, it will end up with a good plan like
> Set

yah! its a lot clear now that you explaned.
Thanks for your always helpfull replys. I learn a lot from them.
Indeed I learn a lot from all you of this list. :)