`

跳跃式索引(Skip Scan Index)的浅析[转]

阅读更多

跳跃式索引(Skip Scan Index)的浅析

[English]

作者: fuyuncat

来源: www.HelloDBA.com

  

Oracle9i中,有一个新的特性:跳跃式索引(Skip Scan Index)。当表有一个复合索引,而在查询中有除了索引中第一列的其他列作为条件,并且优化器模式为CBO,这时候查询计划就有可能使用到SS。此外,还可以通过使用提示index_ssCBO下)来强制使用SS

举例:

SQL> create table test1 (a number, b char(10), c varchar2(10));

Table created.

SQL> create index test_idx1 on test1(a, b);

Index created.

SQL> set autotrace on

SQL> select /*+index_ss(test1 test_idx1)*/* from test1 a

2 where b ='a';

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=2 Card=1 Bytes=32)

2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE)

    
但并不是任何情况下都会使用到SS。在Oracle的官方文档中,除了提到需要CBO,并且对表进行过分析外,还需要保证第一列的distinct value非常小。这一段是从官方文档上摘取的关于SS的一段解释:

 

Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.

 

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.

 

Oracle并没有公布过关于SS更多的内部技术细节。但注意上面的这句话: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.Oralce会对复合索引进行逻辑划分,分成多个子索引。可以这样理解,Oracle将索引从逻辑上划分为a.num_distinct个子索引,每次对一个子索引进行扫描。因此SS的索引扫描成本为a.num_distinct.

下面做一些试验,看看在什么情况下Oracle采用SS.

首先要保证使用SS的几个必要条件:

·        OptimizerCBO

·        相关表要有正确的统计数据

·        Oracle DB版本为9i以上

下面就是一个使用到SS的特殊条件:第一列的distinct num要足够小。小到什么程度呢?

还是以上面的表为例(省略中间的麻烦步骤,取两个临界值做实验):

取第一列distinct number37

SQL> truncate table test1;

 

Table truncated.

 

SQL> begin

  2  for i in 1..100000 loop

  3  insert into test1 values (mod(i,37), to_char(i), to_char(i));

  4  end loop;p;

  5  commit;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> analyze table test1 compute statistics;

 

Table analyzed.

 

SQL> set autotrace on explain

SQL> select * from test1

  2  where b = '500';

 

         A B          C

---------- ---------- ----------

        19 500        500

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=17)

   1    0   TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=17)

 

再取第一列distinct number36

SQL> truncate table test1;

 

Table truncated.

 

SQL> begin

  2  for i in 1..100000 loop

  3  insert into test1 values (mod(i,36), to_char(i), to_char(i));

  4  end loop;

  5  commit;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> analyze table test1 compute statistics;

 

Table analyzed.

 

SQL> select * from test1 where b = '500';

 

         A B          C

---------- ---------- ----------

        32 500        500

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=17)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=12 Card=1 B

          ytes=17)

 

   2    1     INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C

          ard=1)

 

从上面试验结果看,FTScost37。当第一列distinct number小于这个值时,Oracle选择了SS

 

继续试验:

SQL> select count(*) from test1

  2  where b <= '1';

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=10)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C

          ard=1 Bytes=10)

 

注意:在b’10’是比’1’大的最小值(char(10)类型)

SQL> select count(*) from test1

  2  where b <= '10';

 

  COUNT(*)

----------

         2

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=10)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=773 Bytes=7

          730)

观察结果,这时候影响的因素是cardinality了。第二个查询计划中的cardinality值(773)正是b<=’10’cardinality值:

SQL> set autotrace off

SQL> select 100000*(to_number('31302020202020202020', 'xxxxxxxxxxxxxxxxxxxx')-to

_number('31202020202020202020', 'xxxxxxxxxxxxxxxxxxxx'))/(to_number('39393939392

020202020', 'xxxxxxxxxxxxxxxxxxxx')-to_number('31202020202020202020', 'xxxxxxxxx

xxxxxxxxxxx'))+1 from dual;

 

100000*(TO_NUMBER('31302020202020202020','XXXXXXXXXXXXXXXXXXXX')-TO_NUMBER('3120

 

--------------------------------------------------------------------------------

 

                                                                      772.791768

 

再看一个含有第一列条件的等效的语句:

SQL> set autotrace on explain

SQL> select count(*) from test1

  2  where a>=0

  3  and b <='1';

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=12)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C

          ard=1 Bytes=12)

 

再做几个有趣的试验,下面的试验条件是不满足SS的,但是请注意查询返回列队查询计划的影响:

SQL> truncate table test1;

 

Table truncated.

 

SQL> begin

  2  for i in 1..100000 loop

  3  insert into test1 values (i, to_char(i), to_char(i));

  4  end loop;

  5  commit;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> analyze table test1 compute statistics;

 

Table analyzed.

 

SQL> select * from test1

  2  where b = '500';

 

         A B          C

---------- ---------- ----------

       500 500        500

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=19)

   1    0   TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=19)

 

改变返回列:

SQL> select count(*) from test1

  2  where b = '500';

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=10)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost

          =34 Card=1 Bytes=10)

 

再改变一种:

SQL> select a from test1

  2  where b = '500';

 

         A

----------

       500

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=14)

   1    0   INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=3

          4 Card=1 Bytes=14)

 

使用RBO呢?

SQL> select /*+rule*/a from test1

  2  where b = '500';

 

         A

----------

       500

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TEST1'

 

值得一提的是,上述任何一个例子在8i中执行的话,都不会使用到索引(无论是否符合SS的条件)。

分享到:
评论

相关推荐

    MySQL 8.0 之索引跳跃扫描(Index Skip Scan)

    MySQL 8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况,优化器依然能组使用组合索引。 talk is cheap ,show me the code 实践 使用官方文档的例子...

    Oracle 9i用索引跳跃扫描提高查询速度

    Oracle 9i 引入了一项名为索引跳跃扫描(Index Skip Scan)的创新特性,这一特性显著提升了针对特定查询场景的性能,尤其是在处理连接索引和多值索引的查询时。传统的索引通常依赖于查询语句中完全匹配索引的所有列...

    Oracle CBO优化模式中的5种索引访问方法浅析

    4.索引跳跃扫描(INDEX SKIP SCAN) 5.索引快速全扫描(INDEX FAST FULL SCAN) 索引唯一扫描(INDEX UNIQUE SCAN) 通过这种索引访问数据的特点是对于某个特定的值只返回一行数据,通常如果在查询谓语中使用UNIQE和...

    索引跳跃扫描案例优化分享.txt

    索引跳跃扫描(Index Skip Scan)是Oracle数据库中的一种特殊查询技术,它主要用于提高某些特定查询性能,特别是那些涉及多个列且这些列在索引中的顺序不匹配的情况。 1. **基本概念**: - **背景**:在传统的索引...

    index索引的8种使用模式

    INDEX SKIP SCAN(索引跳跃扫描) INDEX SKIP SCAN用于多列复合索引,其中查询条件仅涉及部分列,且这些列的值在索引中不连续。这种模式允许Oracle跳过不必要的索引部分,直接访问满足条件的记录。例如: ```sql ...

    基本索引原理PPT学习教案.pptx

    5. **跳跃式索引扫描(Skip-Scan Index Access)** 自Oracle 9i开始引入,跳跃式扫描允许在非第一列上进行索引查询。当索引的第一列有较低选择性时,数据库会创建多个查询,每个查询针对一个特定的值。这提高了某些...

    跳跃表skiplist参考文档

    跳跃表(Skiplist)是一种在Redis中广泛使用的数据结构,它作为一种概率性的平衡树替代方案,通过概率性平衡而非严格平衡,实现了更简单、更快的插入和删除算法。本篇将深入探讨跳跃表的基本概念、工作原理以及其在...

    NTP 微调和跳跃模式说明

    微调方式是一种平滑、缓慢的渐进式调整,而跳跃式调整则是一种步进式调整。 微调方式(Slew): 微调方式是 NTP 服务的默认方式,当时钟差距小于 128ms 时,使用微调方式调整时间。在微调方式下,ntpd 服务将时钟...

    跳跃式营销方案

    跳跃式营销方案是一种创新的市场推广策略,旨在通过非传统的方式和快速的步骤来吸引消费者注意力,提升品牌知名度,以及迅速扩大市场份额。这种营销方法强调的是打破常规,以出其不意的方式触及目标受众,从而在竞争...

    个人整理,oracle访问路径(全表扫描、回表、索引范围扫描等等)

    五、索引跳跃扫描(Index Skip Scan) 索引跳跃扫描是一种使用索引来访问表的方式。它跳过索引中的一些记录,以找到匹配的记录。索引跳跃扫描通常用于以下情况: * 查询条件包含索引列,但不是唯一索引。 * 需要...

    参考资料-跳跃式营销方案.zip

    跳跃式营销方案是一种创新的营销策略,旨在打破传统的线性营销模式,通过一系列精心设计的活动和策略,迅速提升品牌知名度、吸引目标客户并促进销售。在这个“参考资料-跳跃式营销方案.zip”压缩包中,包含了一份名...

    oracleSQL优化

    - **索引跳跃式扫描(Index Skip Scan)**:在多列索引中跳过部分索引项,适用于部分列有索引,部分列无索引的情况。 - **索引全扫描(Index Full Scan)**:遍历整个索引,通常在全表扫描比索引扫描更优时发生。 ...

    VB.net编写的SkipList 跳跃链表

    跳表(Skiplist)是一种高效的数据结构,它在实现上类似于多层索引的跳跃式访问,由Marc P. Lehmann在1990年提出。VB.NET是一种基于.NET Framework的面向对象的编程语言,它提供了丰富的库和工具,使得开发者能够...

    oracle索引介绍

    - 自Oracle 9i起,引入了一种新的索引扫描模式——索引跳跃扫描(Index Skip Scan),即使在WHERE子句中不包含前导列的情况下也能利用索引。 - Oracle会根据统计信息自动选择最合适的访问路径。通过提示(Hint)...

    探讨多点跳跃式无线网络于近年之研究趋势与模拟实验导引.pdf

    ### 探讨多点跳跃式无线网络于近年之研究趋势与模拟实验导引 #### 多点跳跃式无线网络(Ad-hoc Wireless Networks)概述 多点跳跃式无线网络,通常被称为移动自组织网络(MANETs),是一种无中心、自组型的无线...

    浅析InnoDB索引结构

    【InnoDB索引结构浅析】 InnoDB是MySQL数据库中常用的一种存储引擎,以其支持事务处理和行级锁定而著名。在InnoDB中,索引的构建和组织方式对数据库性能有着重大影响。本文将深入探讨InnoDB的索引特性,特别是聚集...

    跳跃式营销方案.doc

    【跳跃式营销】是一种创新的营销策略,旨在通过迅速跨越传统市场增长阶段,实现销售额的大幅跃升。在这个案例中,XX,一个历史悠久的前列腺用药品牌,通过一系列整合营销传播活动,成功地从2003年的5000万销售额(不...

Global site tag (gtag.js) - Google Analytics