Thank you for your inputs. I read the Oracle note but *"**In this case a
composite index is split logically into smaller subindexes. The number of
logical subindexes depends on the cardinality of the initial column."
*couldn't
understand this bold line.
Best Regards,
AMIT SAROHA
On Tue, Aug 17, 2021 at 2:40 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
Exactly what Mr. Powell wrote.
AND, you might check the date on your reference. What you wrote used to be
true in dinosaur time until someone(s) (several independent, likely)
remarked to Oracle, hey, when my index is much smaller per row and in total
than my table but I don’t want to waste update hilarity with a another
leading index, couldn’t you just search the index for the second column?
And Oracle did that one better by skipping through the structure. From
stats they have a good idea whether or not that is a good way to do a
particular row source operation. They can also do a fast full scan which
can be fast if the index contains decent filtering with respect to the
query regardless of the other of the columns compared to pawing through the
table or partition(s).
Good luck.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Powell, Mark
*Sent:* Tuesday, August 17, 2021 2:05 PM
*To:* ORACLE-L (oracle-l@xxxxxxxxxxxxx)
*Subject:* Re: Access and Filter Predicate on same execution plan line
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
