Re: Access and Filter Predicate on same execution plan line

I’ve put together a small live SQL demo to show that it does a lot less work in the typical use case
https://livesql.oracle.com/apex/livesql/s/l8dde4tiz2j1ry74njwjh5jen

Of course, there were bugs in older versions which effected the CBOs preferences for skip scans which may have tarnished its reputation. But so long as you have sensible optimizer parameters (the default ones) and semi-reliable statistics you should be fine.

Thanks,
Andy

On Wed, 18 Aug 2021 at 01:52, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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
>
>
>

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