|Subject||Re: Slow query with 2 joins @L|
> 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?hmm yeahh.. so, maybe this is the index that has stats problems rigth? Anyway it should update all the indexes stats, I guess.
> 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).
> 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 likeyah! its a lot clear now that you explaned.
> PLAN JOIN (A INDEX (IDX_wOHPDCBu0ruVfoUjPp2ujg==), COPY_CLASSIFICATION INDEX (IDX_UFoCmlswm8xwnGRmBa7/yg==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))
Thanks for your always helpfull replys. I learn a lot from them.
Indeed I learn a lot from all you of this list. :)