David,

dkeith2 wrote:

> Alexandre -
>
> Thanks for your reply. L.TRADENAME does have a good index on it and
> the index is used

I dont think so... no index could be used for a LIKE '%foo%' expression,
I think the index used is the PK/FK index

> , as that table shows -0- non-indexed reads and 6572
> indexed reads.
>

It used an index not necessarily the index on TradeName, it used the
index base on field NDC9, acording to you plan the index used is
IDX_NDCLISTINGS_NDC9, that I think is based on field NDC9 not on field
TradeName.

If you used a like clause as I said on the prior message, probably the
plan would be something like:
Indexed Read on Table L using index on Field TradeName to filter out the
records, then
read Table R using the FK/PK index (NDC9), then
read Table F using the FK/PK index (NDC9), then
read Table B using the FK.PK index (CPNUM) and then
sort it all in memory to remove the duplicates

(The order of R, F and B could differ, but should not matter for the
example I gave)

This way you will have a fully indexed search

The example you provided could not use an index to filter out the
records, so at least one table would need to be scan naturally, FB
chooses the smaller one.

> Record Counts:
> RNDC14: 1808
> CP_FDA_MAP: 24048
> CPNUM_BRANDNAME: 8871
> NDCLISTINGS: 60672
>
> RNDC14 is clearly the smallest table,

That explain why optimizer chooses to use it as the first for the scanning

> but shouldn't the index be used
> regardless?
>

Only if you provided a where clause that could use an index

will use an index:
where L.TradeName = 'Something'
where L.TradeName starts with 'Something'
where L.TradeName like 'Something%'

will not use an index
where L.TradeName <> 'Something'
where L.TradeName containing 'Something'
where L.TradeName like '%Something%'


your plan

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

says:
Read table R from the first record to the end in the storage order, then:
read the record from table F using index IDX_CP_FDA_MAP_NDC9 to match
the clause (F.NDC9 = R.NDC9), then
read the record from table B using index IDX_CPNUM_CPNUM to match the
clause (B.CPNUM = F.CPNUM), then
read the record from table L using index IDX_NDCLISTINGS_NDC9 to match
the clause (L.NDC9 = R.NDC9), then
filter out the results where L.TRADENAME LIKE '%'||UPPER('aceta')||'%'
and then
sort out the result set in memory to remove all the duplicates



see you !


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