索引跳过扫描提升了对非前缀列的索引扫描因为扫描索引块通常比扫描表数据块更快。非前缀索引是指其第一列不包含关键列的索引。如果将前缀索引想象为类似分区表这个概念会更容易理解。在分区对象中分区键此处为首列定义了存储在哪一列的分区数据中。在索引情况下每个键下方的每一行前缀列都会在该键下排序。因此在对前缀索引进行跳扫描时前缀值被跳过非前缀列作为逻辑子索引访问。后列在前缀列内排列因此可以进行“正常”索引访问而无需忽略前缀。在这种情况下复合指数被逻辑上拆分为更小的子指数。逻辑子索引的数量取决于初始列的基数。因此即使首列未被用于 where 子句也可以使用索引。Applies ToAll UsersSummaryThis document explains the index skip scan hint usage.For developers and DBAs who need to know the syntax of the index skip scan hint.SolutionIndex 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.Example query and explain plan:drop table at2;create table at2(a varchar2(3),b varchar2(10),c varchar2(5));beginfor i in 1..1000loopinsert 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 traceonlyselect * from at2 where b352;--------------------------------------------------------------------------| 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(B352)filter(B352)set autotrace off
index skip scan 和oracle partition index 未加分区键类似
发布时间：2026/6/11 18:59:57
索引跳过扫描提升了对非前缀列的索引扫描因为扫描索引块通常比扫描表数据块更快。非前缀索引是指其第一列不包含关键列的索引。如果将前缀索引想象为类似分区表这个概念会更容易理解。在分区对象中分区键此处为首列定义了存储在哪一列的分区数据中。在索引情况下每个键下方的每一行前缀列都会在该键下排序。因此在对前缀索引进行跳扫描时前缀值被跳过非前缀列作为逻辑子索引访问。后列在前缀列内排列因此可以进行“正常”索引访问而无需忽略前缀。在这种情况下复合指数被逻辑上拆分为更小的子指数。逻辑子索引的数量取决于初始列的基数。因此即使首列未被用于 where 子句也可以使用索引。Applies ToAll UsersSummaryThis document explains the index skip scan hint usage.For developers and DBAs who need to know the syntax of the index skip scan hint.SolutionIndex 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.Example query and explain plan:drop table at2;create table at2(a varchar2(3),b varchar2(10),c varchar2(5));beginfor i in 1..1000loopinsert 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 traceonlyselect * from at2 where b352;--------------------------------------------------------------------------| 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(B352)filter(B352)set autotrace off
