Re: Access and Filter Predicate on same execution plan line

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Powell, Mark
> *Sent:* Tuesday, August 17, 2021 2:05 PM
> *To:* ORACLE-L (oracle-l_at_freelists.org)
> *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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Amit Saroha <eramitsaroha_at_gmail.com>
> *Sent:* Tuesday, August 17, 2021 12:24 PM
> *To:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *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
>
>
>

--
http://www.freelists.org/webpage/oracle-l