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.

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

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

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Friday, March 31, 2006 5:01 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Select not using index - 1.5.3 & 2.0

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@...>
wrote:

>
> Any idea why the optimizer (on both FB 1.5.3 and 2.0rc1) chooses a
> natural plan and ignores the index on PBM_CLAIM.DATESBM?

The index has bad (but not as terrible as I have seen selectivity,
around 600 if my math is right). The optimiser has judged it as not
overly useful. To put it another way, the optimiser has come up with
different ways it can solve the query, and either because there is a
faster way or because the indices are not accurate, the optimiser
estimates that a natural read on MDDB_DRUG is less expensive.

Remember that the ideal plan is data dependent and should vary from one
database to another. If you are using a test database, I recommend where
possible using a large 'real' database for testing, as test data can
often mislead as to which plan would be used in real life.

> The duplicates in the index shouldn't be a problem, there number of
> transactions a day vary from less than a hundred to just over a
> thousand.
>
> select
> p.ACCOUNTID, p.SRVPROVID, p.PRODUCTID, d.GPI from
> pbm_claim p
> JOIN MDDB_DRUG d
> ON d.PRODUCTID = p.PRODUCTID
> where
> p.datesbm < ?
>
> PLAN SORT (JOIN (D NATURAL, P INDEX (I_PBM_CLAIM_NDC)))
>
> ALTER TABLE MDDB_DRUG ADD CONSTRAINT PK_MDDB_DRUG PRIMARY KEY
> (PRODUCTID); CREATE DESC INDEX I_PBM_CLAIM_DATESBM_DESC ON PBM_CLAIM
> (DATESBM); CREATE ASC INDEX I_PBMCLAIM_DATESBM ON PBM_CLAIM (DATESBM);

> CREATE ASC INDEX I_PBM_CLAIM_NDC ON PBM_CLAIM (PRODUCTID); CREATE ASC
> INDEX I_PBMCLAIM_DECIMALQTY ON PBM_CLAIM (DECIMALQTY);

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 Firebird, foreign keys automatically
create a supporting index, and there are known quirks when the same
index is defined twice. It may be that simple.

What you can do if you are sure the in the real world the plan it
generates is suboptimal is to prevent it from using an index. This will
artifically increase the cost of the join to the suboptimal plan, which
makes it less desirable to the optimiser which should hopefully then
choose the optimal plan.

select
p.ACCOUNTID, p.SRVPROVID, p.PRODUCTID, d.GPI from
pbm_claim p
JOIN MDDB_DRUG d
ON d.PRODUCTID = p.PRODUCTID+0
where
p.datesbm < ?

Adding 0 should prevent I_PBM_CLAIM_NDC from being available to the
suboptimal plan. (I usually get the +0 mixed up so if it doesn't change
it, move it to the other side of the = sign).

Adam







++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links