> -- Your query
> 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')
>
> PLAN JOIN (COPY_CLASSIFICATION NATURAL, A INDEX
> (IDX_/F13/OidwbfyhwFbjRjfng==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))
> 334757 fetches, 0 marks, 4209 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 41609 index, 41586 seq.
> Delta memory: -1000 bytes.
> Total execution time: 0.829s

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.

Try

SELECT * FROM PHYSICAL_COPY a
INNER JOIN COPY_CLASSIFICATION cc ON a.COPY_ID+0 = cc.COPY_ID
INNER JOIN COPY c ON cc.COPY_ID = c.ID
WHERE a.REPOSITORY_TYPE = 'blah'

(or use ||'' rather than +0 if COPY_ID is not a number)

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