That percona thing is quite cute - but suffers from two major defects (as
noted in the closing paragraph).
It's basically doing an index fast full scan across an index that would be
about 20 bytes per table row.
It can only work with equality.
With that in mind the more important defect in Oracle is that it's not
coded to do an index fast full scan followed by table access by rowid,
otherwise it might not be very difficult to emulate it with a
function-based index and pl/sql functions. (Messy to write the query,
though, if it were all in end-user land).
Regards
Jonathan Lewis
On Tue, 17 Aug 2021 at 19:22, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
To tell the truth, skip scan is far from ideal. It is similar to fast full
index scan. It reads all the values of the first column and then returns
the rows with requested values of the 2nd column. What Oracle would need is
this:
https://www.percona.com/blog/2019/06/14/bloom-indexes-in-postgresql/
On 8/17/21 2:05 PM, Powell, Mark wrote:
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
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
-- //www.freelists.org/webpage/oracle-l
