The first suggestion uses this plan: PLAN JOIN (D INDEX (I_G),DP INDEX
(U_DP)) [U_DP is a unique index using on dp.N]
The second suggestion uses this plan: PLAN JOIN (DP NATURAL,D INDEX
(I_G,PK_P))

I'll have to see if the first suggestion helps, as there are 94 hits out
of 95 values when using the first two characters :)

Breaking it out in to four exists statements, it now uses the
appropriate indexes:

(tc.R = 1 AND
EXISTS (
SELECT 1
FROM TAB1 dp
JOIN TAB2 d ON d.P=dp.N
WHERE dp.A='Y' AND d.G STARTING WITH SUBSTRING(tc.T FROM 1 FOR 2)
)
) OR (...repeat for R[2,4])

PLAN JOIN (D1 INDEX (I_G),DP1 INDEX (U_DP))
PLAN JOIN (D2 INDEX (I_G),DP2 INDEX (U_DP))
PLAN JOIN (D3 INDEX (I_G),DP3 INDEX (U_DP))
PLAN JOIN (D4 INDEX (I_G),DP4 INDEX (U_DP))

Some of the plan issues may also be related to the fact that we have an
old version of 1.5; we'll have to see how this performs on 2.1.1.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Wednesday, April 30, 2008 6:53 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Using an index with OR

Sorry, Rick, I cannot tell you why (others can), my guess is that since
the OR involves several tables (tc and d), it is too varying clauses for
an index to be of use.

I do, however, expect that mixing the two statements together will make
use of the index (alternatively, you could have four EXISTS clauses
or'ed together):

EXISTS (
SELECT 1
FROM TAB1 dp
JOIN TAB2 d ON d.P=dp.N
WHERE dp.A='Y' AND d.G STARTING WITH SUBSTRING(tc.T FROM 1 FOR 2) AND
((tc.R=1) OR
(tc.R=2 AND d.G STARTING WITH SUBSTRING(tc.TCC FROM 1 FOR 4)) OR
(tc.R=3 AND d.G STARTING WITH SUBSTRING(tc.TCC FROM 1 FOR 6)) OR
(tc.R=4 AND d.G STARTING WITH tc.TCC))
)

and am curious whether the following will work or not (I suspect not, I
expect the substrings to be padded with blanks):

EXISTS (
SELECT 1
FROM TAB1 dp
JOIN TAB2 d ON d.P=dp.N
WHERE dp.A='Y' AND
d.G STARTING WITH
case
when tc.R=1 then SUBSTRING(tc.TCC FROM 1 FOR 2)
when tc.R=2 then SUBSTRING(tc.TCC FROM 1 FOR 4)
when tc.R=3 then SUBSTRING(tc.TCC FROM 1 FOR 6)
when tc.R=4 then tc.TCC
end
)

But I have no good explanation to your WHY, just workarounds that may
prevent trouble.

Set

Rick Debay wrote:

> Can I get an explanation why an index isn't used for the second query?
> Should I incorporate tc.R in to an index (its selectivity is
> questionable, see below).
>
> The table alias 'tc' is from the query that contains this EXISTS

clause.

>
> -- uses index on d.G and dp.N
> EXISTS (
> SELECT 1
> FROM TAB1 dp
> JOIN TAB2 d ON d.P=dp.N
> WHERE dp.A='Y' AND d.G STARTING WITH SUBSTRING(tc.T FROM 1 FOR 2)
> )
>
> -- only uses index on dp.N, d is natural
> EXISTS (
> SELECT 1
> FROM TAB1 dp
> JOIN TAB2 d ON d.P=dp.N
> WHERE dp.A='Y' AND
> ((tc.R=1 AND d.G STARTING WITH SUBSTRING(tc.TCC FROM 1 FOR 2))

OR

> (tc.R=2 AND d.G STARTING WITH SUBSTRING(tc.TCC FROM 1 FOR 4))

OR

> (tc.R=3 AND d.G STARTING WITH SUBSTRING(tc.TCC FROM 1 FOR 6))

OR

> (tc.R=4 AND d.G STARTING WITH tc.TCC))
> )
>
> Tc.R distribution of values
>
> R-VAL COUNT
> -----------
> 1 94
> 2 601
> 3 825
> 4 5448

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.