> Have you timed your query with the plans hardcoded to ascertain that it really is quicker using your preferred plan, Rick?

No, but I'm familiar with the data set and identified this query as running slow (on FB 2.5.2) compared to its speed on a (now decommissioned) FB 1.5.6 box.

> query itself, the index definitions with a little bit of additional information about their selectivity and tell us which Firebird version you're using

SELECT
ca.ID
FROM
V_ALLOC_GPI ca
WHERE
ca.RPL_GRP = ? AND
ca.GPI = ? AND
ca.TEE = ? AND
ca.UNIT_DOSE_USE = ? AND
(ca.UNIT_DOSE_USE = '' OR ca.PKG_SZ = ?) AND
ca.IS_BRAND = ?
ORDER BY
SIGN(ca.DECIMALQTY), ca.DATESBM

The view is

SELECT
FROM
V_ALLOCATION ca
JOIN V_RPL_GRP_MEMBERS rgm
ON ca.ACCOUNTID = rgm.CHC AND ca.SRVPROVID = rgm.PHARMACY
JOIN MDDB_DRUG d
ON d.PRODUCTID = ca.PRODUCTID
WHERE
ca.DATESBM >= rgm.OLDEST

And I expected the date to come in to play from the WHERE clause in the view. The view V_ALLOCATION contains no joins, and contains the table (aliased as 'c') where I expected two indices to be used.

Index I_PBM_CLAIM_NDC (3)
Depth: 3, leaf buckets: 2749, nodes: 4415377
Average data length: 0.03, total dup: 4391899, max dup: 23871
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 2748

Index I_PBMCLAIM_DATESBM (1)
Depth: 3, leaf buckets: 2723, nodes: 4415377
Average data length: 0.00, total dup: 4411954, max dup: 6433
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 2722

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Tuesday, April 23, 2013 1:32 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SV: Forcing optimizer to use index

>I have a query that needs to use an index that the optimizer isn't
>using. How can I get it to add the index to the query plan?
>The current plan is
>
>PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
>(I_PBM_CLAIM_NDC), AC RGM SA INDEX (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C
>INDEX (PK_CHC), AC RGM PRG INDEX (PK_CRM_PHARM_RPL_GRP), AC RGM RG
>INDEX
>(PK_CRM_RPL_GRP)))
>
>And it needs to be
>
>PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
>(I_PBM_CLAIM_NDC, I_PBMCLAIM_DATESBM), AC RGM SA INDEX
>(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX
>(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP)))
>
>Without I_PBMCLAIM_DATESBM instead of scanning a few months of data
>it's scanning a few years.

Have you timed your query with the plans hardcoded to ascertain that it really is quicker using your preferred plan, Rick? I doubt you'll get much help from this list without including the query itself, the index definitions with a little bit of additional information about their selectivity and tell us which Firebird version you're using. You may be able to reduce the query (and highlight your problem) by eliminating the non-important tuples of your query (you definitely need AC D and AC CA C, but the rest may not matter for your problem), but it might also be that someone will spot a better way to write your query if you include the entire text.

One thing regarding your desired plan: I think (don't know, it is just a hunch) that using several indexes for a table is most useful if it is the first table in the plan, so it might be better to have a plan like:

PLAN SORT (JOIN (AC CA C INDEX(I_PBMCLAIM_DATESBM, <possibly another index>), AC D INDEX (<index linking to AC CA C>), AC RGM SA INDEX(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP)))

Set

------------------------------------

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

Visit http://www.firebirdsql.org 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