--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@...> wrote:

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

Alexandre -

Thanks for your reply. L.TRADENAME does have a good index on it and
the index is used, as that table shows -0- non-indexed reads and 6572
indexed reads.

Record Counts:
RNDC14: 1808
CP_FDA_MAP: 24048
CPNUM_BRANDNAME: 8871
NDCLISTINGS: 60672

RNDC14 is clearly the smallest table, but shouldn't the index be used
regardless?

Thanks.

David Keith