The partial index is not involved when filtering conditions through OR.

[sim1984]

I created partial index

CREATE INDEX IDX_TRIAL_LINE_PLACE_WIN
ON TRIAL_LINE(PLACE_WON)
WHERE PLACE_WON = 1 OR PLACE_WON = 2 OR PLACE_WON = 3;

And execute query:

SELECT COUNT(*)
FROM TRIAL_LINE
WHERE PLACE_WON = 1

plan:

PLAN (TRIAL_LINE NATURAL)

Select Expression
    -> Aggregate
        -> Filter
            -> Table "TRIAL_LINE" Full Scan

However, if you run a query like this, the index will be used:

SELECT COUNT(*)
FROM TRIAL_LINE
WHERE PLACE_WON = 1 OR PLACE_WON = 2

PLAN (TRIAL_LINE INDEX (IDX_TRIAL_LINE_PLACE_WIN, IDX_TRIAL_LINE_PLACE_WIN, IDX_TRIAL_LINE_PLACE_WIN))

Select Expression
    -> Aggregate
        -> Filter
            -> Table "TRIAL_LINE" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "IDX_TRIAL_LINE_PLACE_WIN" Full Scan
                    -> Bitmap Or
                        -> Bitmap
                            -> Index "IDX_TRIAL_LINE_PLACE_WIN" Range Scan (full match)
                        -> Bitmap
                            -> Index "IDX_TRIAL_LINE_PLACE_WIN" Range Scan (full match)

Naturally, the index will be used for the full expression:

SELECT COUNT(*)
FROM TRIAL_LINE
WHERE PLACE_WON = 1 OR PLACE_WON = 2 OR PLACE_WON = 3

It seems to me that this clearly contradicts what is described in the release note:

The search condition defined for the index contains ORed boolean expressions and one of them is explicitly included in the WHERE condition;

[sim1984]

The database is the same as in #7799

Before checking, I removed the regular (not partial) index:

DROP INDEX TRIAL_LINE_PLACE_IDX;

[dyemanov]

The current matching logic is not recursive. The optimizer actually sees (PLACE_WON = 1 OR PLACE_WON = 2) OR PLACE_WON = 3 and checks PLACE_WON = 1 against both (PLACE_WON = 1 OR PLACE_WON = 2) and PLACE_WON = 3 parts without success, obviously. It can be fixed/improved, for sure.

But I see here a more important issue - excessive index usage. For condition WHERE PLACE_WON = 1 OR PLACE_WON = 2 OR PLACE_WON = 3 I see four (!) index scans. It should be either three range scans (for all its parts, with bitmap OR afterwards) or even better just a single full index scan for the whole condition).

[dyemanov]

I've committed a fix for your particular case, please verify.

However, I see (at least two) other related problems to be addressed:

1. Match index WHERE PLACE_WON = 1 OR PLACE_WON = 2 OR PLACE_WON = 3 to condition WHERE PLACE_WON IN (1, 2)
2. Match index WHERE PLACE_WON IN (1, 2, 3) to condition WHERE PLACE_WON IN (1, 2)

They will be addressed by separate commits.

[sim1984]

Now a partial index with 3 or more ORs is processed correctly, firstly it is used, and secondly it does not produce unnecessary index scans.

[sim1984]

A partial index with IN would also be interesting. Including such cases

1. Match index WHERE PLACE_WON IN (1, 2, 3) to condition WHERE PLACE_WON = 1
2. Match index WHERE PLACE_WON IN (1, 2, 3) to condition WHERE PLACE_WON = 2