Amit, Oracle can use a multi-column index even if the first column in the index
is not used in a WHERE clause condition if the optimizer thinks doing so in a
benefit. If you have access to Oracle Support, you can read the following
document for a little bit of information on the feature. More than likely
there are only a few distinct values for the leading column for this feature to
be invoked.
Index Skip Scan Feature (Doc ID 212391.1)
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Amit Saroha <eramitsaroha@xxxxxxxxx>
Sent: Tuesday, August 17, 2021 12:24 PM
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
Subject: Access and Filter Predicate on same execution plan line
Dear All,
Please help me understand why the access and filter predicates are present in
the below plan on the same index. There is an index present on the
(process_flag, request_id, item_id) column and what I am aware of is that
Oracle doesn't make use of the second column in the index if the first column
is not present in the where clause (in my case process_flag is not present in
the statement).
Plan hash value: 4207199320
--------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 5 | 535 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | XXOM01T | 5 | 535
| 3 (0) | 00:00:01 |
|* 2 | INDEX SKIP SCAN |
XXOM01T_N1 | 5 | | 1 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("REQUEST_ID"=92830170)
filter("REQUEST_ID"=92830170)
15 rows selected.
Best Regards,
AMIT
