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