Subject | Re: [firebird-support] Using an index with OR |
---|---|

Author | Svein Erling Tysvaer |

Post date | 2008-04-30T22:53:01Z |

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:

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