RE: Access and Filter Predicate on same execution plan line

If your first column is just two values, say, either Y or N, then a skip scan filtering on the second in each of Y and N will be the good polar case of skip scan. (Well, actually all values equal would be even better, but that would also be a silly column to have and index).

If you have 42 billion different values for the first column only a few of which have the desired value in the second column, that will be toward the polar bad case.

It is entirely possible you've never seen a good one and that they occurred as the least bad way of getting at the data marginally improved over a full table scan when even though slow the size of the index being skip scanned is a tiny fraction of the size of the table (due to populated row width). Even if it turns out it reads every block in the index that can be a lot less than all the blocks in the table.

They can fill a gap when a relatively slower index skip scan is less bad than maintenance on every insert and delete and updates involving columns in the index.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Tuesday, August 17, 2021 8:26 PM
To: oracle-l_at_freelists.org
Subject: Re: Access and Filter Predicate on same execution plan line

Index skip scan usually is not very fast. I guess it might be fast but I haven't seen such a case. Flying saucers might exist but I haven't seen one yet. And when you take a look at the number of the logical gets caused by the index skip scan, you will see something very similar to the fast full scan.

On 8/17/21 4:31 PM, Andy Sayer wrote:
> An index skip scan is simple and can be very fast (nothing like a fast
> full scan).

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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





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