索引跳跃扫描 Index Skip Scan Feature (Doc ID 212391.1)

Index Skip Scan Feature (Doc ID 212391.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Information in this document applies to any platform.
 

PURPOSE

This document explains the index skip scan hint usage. 

本文档介绍了索引跳跃扫描Hint的用法。

SCOPE

For developers and DBAs who need to know the syntax of the index skip scan hint.

DETAILS

Index skip scans improve index scans against non-prefix columns since it is often faster to scan index blocks than scanning table data blocks. A non-prefix index is an index which does not contain a key column as its first column.
索引跳过扫描改进了针对非前缀列的索引扫描，因为扫描索引块通常比扫描表数据块更快。非前缀索引是不包含键列作为其第一列的索引。
This concept is easier to understand if one imagines a prefix index to be similar to a partitioned table. In a partitioned object the partition key (in this case the leading column) defines which partition data is stored within. In the index case every row underneath each key (the prefix column) would be ordered under that key. Thus in a skip scan of a prefixed index, the prefixed value is skipped and the non-prefix columns are accessed as logical sub-indexes. The trailing columns are ordered within the prefix column and so a 'normal' index access can be done ignoring the prefix.
如果人们认为前缀索引类似于分区表，则更容易理解这一概念。在分区对象中，分区键（在本例中为前导列）定义了存储在其中的分区数据。在索引情况下，每个键下面的每一行（前缀列）将在该键下排序。因此，在跳过对前缀索引的扫描时，将跳过前缀值，并且将非前缀列作为逻辑子索引进行访问。尾随列在前缀列中排序，因此可以忽略前缀而进行“常规”索引访问。
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. Hence it is now possible to use the index even if the leading column  is not used in a where clause.
在这种情况下，组合索引在逻辑上分为较小的子索引。逻辑子索引的数量取决于初始列的基数。因此，即使在where子句中未使用前导列，现在也可以使用索引。
Example query and explain plan:

drop table at2;
create table at2(a varchar2(3),b varchar2(10),c varchar2(5));

begin
  for i in 1..1000
  loop
  insert into at2 values('M', i, 'M');
  insert into at2 values('F', i, 'F');
  end loop;
  end;
/
create index at2_i on at2(a,b,c);
exec dbms_stats.gather_table_stats(OWNNAME => NULL, TABNAME => 'at2',
       CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1');

set autotrace traceonly
select * from at2 where b='352';

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     2 |    14 |     3   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | AT2_I |     2 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"='352')
       filter("B"='352')

set autotrace off