--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> > -- Your query
>....
> > PLAN JOIN (COPY_CLASSIFICATION NATURAL, A INDEX
> > (IDX_/F13/OidwbfyhwFbjRjfng==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))
> Somehow, the optimizer thinks going NATURAL on COPY_CLASSIFICATION is the best way to execute your query. Your LEFT JOIN attempt revealed that this is not true, so what you're looking for is a way to hint to the optimizer that it ought to use a different plan.

Hi Set, Alec,

I thougth that this was the problem... it's almost like mine some time ago... That's can cause the Left join choose the index and then be quick too (althought that I din't understand theses "choices" very well... hmmm yet... :)...
Anyway, like Dmitry said, maybe a just SET STATISTICS shold do the trick?
Please, try this Alec:
SET STATISTICS INDEX IDX_UFoCmlswm8xwnGRmBa7/yg==;

Then try that "my first query" again.

SELECT * FROM PHYSICAL_COPY a
INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
COPY_CLASSIFICATION."COPY_ID"
INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID"
WHERE (a."REPOSITORY_TYPE" = 'blah')

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 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.
>
> HTH,
> Set

: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?

thanks,