> FYI, this is the smallest usable part of a much larger query.
>
> > If you are using a test database
>
> Production database backed up to my test system.

Good.

>
> > The optimiser has judged it as not overly useful.
>
> You're probably right, at run time the where clause ends up dropping
> about only about ten percent of the data set.

I suspected that may be the case. An index is only useful if, as a
result of using that index, fewer data pages have to be read from
disk. Furthermore, you need to read the index pages from the disk, so
using the index may actually cause more disk activity then just
reading the entire table from disk.

Now, your query is only knocking out 10% of the records. That means
that to not have to read a particular data page, that page must
contain absolutely no 'interesting' records. With 90% of the records
'interesting', how many pages do you suspect are 'not interesting'. If
this figure is not higher than the number of index pages that must be
read, then you are going backwards performance-wise.

Of course caching comes into play, so it is hard to make exact
predictions, so 'the proof is in the pudding'. Using your test
database (which should be representative of a typical larger
installation), test whether the plan you believe is better is in fact
better. (It may be, it is not always accurate to judge how useful an
index is by a single number). Do several tests in various order
between the two queries, and if the only you thought was better turns
out to be significantly enough better, then use the '+0 trick' to
assist the optimiser. Other dbms have hint clauses that are used for
the same reason.

>
> > May I ask why you have defined the index 'I_PBM_CLAIM_NDC' when it
> looks to me like it is a foreign key?
>
> In a perfect world I would have defined it as a foreign key, but often
> we get claims for items that don't exist (Wolter Kluwers Health doesn't
> list them, it was entered incorrectly, yada yada) but the claim has to
> exist in the system for other business reasons.

That is fine, I was just worried you might have both.

It may be better to define a table that sits between the claim and the
product tables. 'claimproduct' for example. You could store the
claimproductid in the claim table, and have a nullable productid in
the claimproduct table. That is just a suggestion, I don't know the
ins and outs of your design so it may not be appropriate.

Adam