I have a 4 table join that is using indicies on 3 of the 4 tables, but
I can not find a way to get it to use the index on table # 4. Here's
the query:


SELECT DISTINCT L.NDC9 AS FDA_NDC,F.NDC9 AS ELS_NDC,R.NDC9 AS NDDF_NDC,
L.TRADENAME,B.BRAND,R.LN
FROM NDCLISTINGS L,RNDC14 R,CP_FDA_MAP F,CPNUM_BRANDNAME B
WHERE R.NDC9 = L.NDC9
AND F.NDC9 = L.NDC9
AND B.CPNUM = F.CPNUM
AND L.TRADENAME LIKE '%'||UPPER('aceta')||'%'

The 'RNDC14' table, aliased as R, has the following indicies:

CREATE INDEX IDX_RNDC14_BN_LN ON RNDC14 (BN, LN);
CREATE INDEX IDX_RNDC14_NDC9 ON RNDC14 (NDC9);

...and the following Primary Key:
ALTER TABLE RNDC14 ADD CONSTRAINT PK_RNDC14 PRIMARY KEY (NDC9, NDC)
USING INDEX IDX_PK_RNDC14;

When I join these tables using any order or link columns possible in
this scenario, I always end up with this plan:
Plan
PLAN SORT (JOIN (R NATURAL, F INDEX (IDX_CP_FDA_MAP_NDC9), B INDEX
(IDX_CPNUM_CPNUM), L INDEX (IDX_NDCLISTINGS_NDC9)))

Adapted Plan
PLAN SORT (JOIN (R NATURAL, F INDEX (IDX_CP_FDA_MAP_NDC9), B INDEX
(IDX_CPNUM_CPNUM), L INDEX (IDX_NDCLISTINGS_NDC9)))

I get 1808 non-indexed reads and -0- indexed reads from the RNDC14
table. What can I do to
force the optimizer to use the indicies on RNDC14?

Thanks.

David Keith