dkeith2 wrote:

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

How maby records on each table, especially on RNDC14 ?

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 -- Link cluase
AND F.NDC9 = L.NDC9 -- Link clause
AND B.CPNUM = F.CPNUM -- Link Clause
AND L.TRADENAME LIKE '%'||UPPER('aceta')||'%' -- Filter clause that could not use an index

Since your query has no possibility to filter using an index, one of the tables must go natural, FB tends to start with the smaller one.

You should have no change on the execution plan, but for me, is very much readable if you use SQL-92 joins, you can clearly see what is link conditions and what is filter condition.



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 JOIN
RNDC14 R on (R.NDC9 = L.NDC9) JOIN
CP_FDA_MAP F on (F.NDC9 = L.NDC9) JOIN
CPNUM_BRANDNAME B on (B.CPNUM = F.CPNUM)
WHERE
L.TRADENAME LIKE '%'||UPPER('aceta')||'%'

if L.TradeName is indexed and has a good selectivity FB would probably start with L as the first table and use and index on TradeName if you change your where clause to something like
WHERE
L.TRADENAME LIKE 'aceta%'

I know you are doing a diferent search, for the case sensitiveness you could use a case insensitive collation, and the saerch could still use an index, for the wildcard at the begining you have no choice, if you really need it, there is no way to use an index.

Think for yourself, looking on the index of a firebird book, you can't remember exactly how was the word, you just remember that it has "sact" as part of it, you will need to look trough the entire index to find "transaction", you could not just go to the "T" part of the index.

see you !




--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br