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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mladen Gogala
Sent: Tuesday, August 17, 2021 8:26 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
