> 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
>
> PLAN JOIN (A INDEX (IDX_wOHPDCBu0ruVfoUjPp2ujg==), COPY_CLASSIFICATION INDEX (IDX_UFoCmlswm8xwnGRmBa7/yg==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))
>
> 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. :)

Regards,