Hi,

I'm having some trouble with the query below. Both tables contains
alot off records. I don't understand why when using a left join the
UC_TEST_DETAIL_CODE_VNR can't be used for ordering, which results in
slow performance.

SELECT
A.*,
B.DESCRIPTION
FROM
TEST_DETAIL A
LEFT JOIN
TEST B
ON
B.ID = A.FID
ORDER BY
A.CODE,A.VNR

plan on executtion using a left join
PLAN SORT (JOIN (A NATURAL,B INDEX (PK_TEST_ID)))

plan on executtion using an inner join
PLAN JOIN (A ORDER UC_TEST_DETAIL_CODE_VNR,B INDEX (PK_TEST_ID))

When i add the inner join plan to the query when using a left join, i
get :
index cannot be used in the specified plan.
index UC_TEST_DETAIL_CODE_VNR cannot be used in the specified plan.

Greetings,
Filip Moons

DDL:
CREATE TABLE TEST (
ID BIGINT NOT NULL,
CODE VARCHAR(20) NOT NULL,
DESCRIPTION VARCHAR(40));

ALTER TABLE TEST ADD CONSTRAINT UC_TEST_CODE UNIQUE (CODE);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST_ID PRIMARY KEY (ID);

CREATE TABLE TEST_DETAIL (
ID BIGINT NOT NULL,
FID BIGINT,
CODE VARCHAR(20) NOT NULL,
VNR INTEGER NOT NULL,
DESCRIPTION VARCHAR(40));

ALTER TABLE TEST_DETAIL ADD CONSTRAINT UC_TEST_DETAIL_CODE_VNR UNIQUE
(CODE, VNR);
ALTER TABLE TEST_DETAIL ADD CONSTRAINT PK_TEST_DETAIL_ID PRIMARY KEY
(ID);
CREATE INDEX IDX_TEST_DETAIL_FID ON TEST_DETAIL (FID);