几种索引扫描方式

最新推荐文章于 2020-06-08 18:37:56 发布

转载最新推荐文章于 2020-06-08 18:37:56 发布 · 1.4k 阅读

文章标签：

#object #sql #table #branch #statistics #null

Oracle基础同时被 3 个专栏收录

89 篇文章

订阅专栏

oracle性能优化

34 篇文章

订阅专栏

Sql语句优化

9 篇文章

订阅专栏

1）索引唯一扫描如果查询时是通过unique或primary key约束来保证只返回一条数据，那么优化器就会选择索引唯一扫描，这是访问一条数据的最快方式。

2）索引范围扫描

索引键非唯一，当遇到如下条件时会使用索引范围扫描：

1.col=：b1

2.col<:b1

3.col>:b1

3)索引降序范围扫描

与2）中的情况相同，只不过2）默认是安装升序进行查找的，而这里是按降序进行查找，如：

select line_item_id,order_id from order_items where order_id<:b1 order by order_id desc;

4) 跳跃式索引(Skip Scan Index)。当表有一个复合索引，而在查询中有除了索引中第一列的其他列作为条件，并且优化器模式为CBO，这时候查询计划就有可能使用到SS，另外通过使用提示index_ss（CBO下）来强制使用SS。跳跃式索引使复合索引从逻辑上分为几个小的子索引，分的条件就是复合索引列的第一个字段，可以这样理解，Oracle 将索引从逻辑上划分为a.num_distinct个子索引，每次对一个子索引进行扫描。因此SS的索引扫描成本为a.num_distinct.而且使用SS的条件需要第一列的distinct num要足够小

5）index full scan和Index Fast Full Scan（全索引扫描和快速全索引扫描）

index full scan和index fast full scan是指同样的东西吗？答案是no。两者虽然从字面上看起来差不多，但是实现的机制完全不同。我们一起来看看两者的区别在哪里?
首先来看一下IFS,FFS能用在哪里：在一句sql 中，如果我们想搜索的列都包含在索引里面的话，那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句：
　　
　　SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
　　
　　SQL> CREATE INDEX ind_test_id ON TEST(object_id);
　　
　　SQL> INSERT INTO TEST
　　SELECT　 *
　　FROM dba_objects
　　WHERE object_id IS NOT NULL AND object_id > 10000
　　ORDER BY object_id DESC;
　　
　　17837 rows created.
　　
　　SQL> analyze table test compute statistics for table for all columns for all indexes;
　　
　　Table analyzed.
　　SQL> set autotrace trace;
　　
　　SQL> select object_id from test;
　　
　　17837 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
　　　 1　　0　 TABLE ACCESS (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
　　
　　这时候Oracle 会选择全表扫描，因为 object_id 列默认是可以为null的，来修改成 not null:
　　
　　
　　SQL>alter table test modify(object_id not null);
　　
　　SQL> select object_id from test;
　　
　　17837 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
　　　 1　　0　 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)
　　
　　当然我们也可以使用index full scan:
　　
　　
　　SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test;
　　
　　17837 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
　　　 1　　0　 INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)
　　
　　我们看到了两者都可以在这种情况下使用，那么他们有什么区别呢？有个地方可以看出两者的区别，来看一下两者的输出结果，为了让大家看清楚一点，我们只取10行。
　　
　　INDEX FAST FULL SCAN
　　
　　SQL> select object_id from test where rownum<11;
　　
　　 OBJECT_ID
　　----------
　　　　 66266
　　　　 66267
　　　　 66268
　　　　 66269
　　　　 66270
　　　　 66271
　　　　 66272
　　　　 66273
　　　　 66274
　　　　 66275
　　10 rows selected.
　　
　　
　　INDEX FULL SCAN
　　
　　SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test　where rownum<11;
　　
　　 OBJECT_ID
　　----------
　　　　 10616
　　　　 12177
　　　　 12178
　　　　 12179
　　　　 12301
　　　　 13495
　　　　 13536
　　　　 13539
　　　　 13923
　　　　 16503
　　10 rows selected.
　　
　　可以看到两者的结果完全不一样，这是为什么呢？这是因为当进行index full scan的时候oracle 定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
　　
　　而index fast full scan则不同，它是从段头开始，读取包含位图块，root block,所有的branch block, leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。