Re: Access and Filter Predicate on same execution plan line

Hi Amit,

As hand-wavy explanations go, that one is pretty bad.

An index skip scan is simple and can be very fast (nothing like a fast full scan).

The important concept to remember is that the index is stored using the order specified by the columns used to create it. The branch blocks of the index contain enough of the index keys so that you can effectively locate the first leaf blocks that contain values you are searching for with equality conditions.

An index skip scan first gets the minimum value for the first column of the index -this is easy as it just has to look at the first index key. It can then effectively search with an equality condition for this column (equal to the min) and the second column (equal to your input value). It then needs to find the next smallest value by searching for the minimum value of the first column where it is greater than the actual minimum - again this is easy as you just need to search the branch blocks for the point at which column 1 is larger than the value. It then repeats this method until it gets to the end of the index (where there is no greater value for the first column).

The mechanism is more effective when there are less distinct values of the first column.

Hope that helps,
Andy

On Tue, 17 Aug 2021 at 21:05, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> 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