Hi emb_blaster!

> Well if it don't change the PLAN you can do this by your hand adding this PLAN after > the SELECT:
> PLAN JOIN (COPY_CLASSIFICATION INDEX (IDX_UFoCmlswm8xwnGRmBa7/yg==),
> A INDEX
> (IDX_/F13/OidwbfyhwFbjRjfng==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))

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.

>> This will prevent the use of IDX_/F13/OidwbfyhwFbjRjfng== (by the way, I've never
>> seen this kind of index naming before) and hopefully be enough for the optimizer
>> to pick the optimal plan.

> :O... I din't understanded this part... I thoutgh that we wanna use that index in >COPY_CLASSIFICATION, and not to deactivate the use of the index in PHYSICAL_COPY... Ô.o
> 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).

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