full index scan and fast full index scan

最新推荐文章于 2022-01-24 06:00:00 发布

zhou689689

最新推荐文章于 2022-01-24 06:00:00 发布

阅读量707

点赞数

分类专栏：经验

本文链接：https://blog.csdn.net/zhou689689/article/details/8765406

版权

经验专栏收录该内容

6 篇文章 0 订阅

订阅专栏

官方文档：
Full Index Scan
In a full index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified. A full scan can eliminate sorting because the data is ordered by index key.
Fast Full Index Scan
A fast full index scan is a full index scan in which the database reads the index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint.

理解：
full index scan：想要的结果是有序，读取数据块是一块一块的操作，他会从索引的根节点向下扫描到叶子节点，然后使用双向列表的方式顺序读取数据块。
fast full index scan：想要的结果是无序的，读取数据块是multiple I/O操作，从根几点开始向下扫描到叶子节点后使用的是单次I/O读取多个数据块。
例子：
index fast full scan与index full scan试验
--创建测试表test3
SQL> create table test3 as select * from dba_objects;

表已创建。
--创建索引
SQL> create index idx_test3_id on test3(object_id);

索引已创建。

--收集test3的统计信息
SQL> exec dbms_stats.gather_table_stats('SYSTEM','TEST3',cascade=>true);

PL/SQL 过程已成功完成。

SQL> set autot traceonly;
SQL> select object_id from test3;

已选择71947行。

执行计划
----------------------------------------------------------
Plan hash value: 3306317399

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       | 71947 |   351K|   280   (1)| 00:00:04 |
|   1 | TABLE ACCESS FULL| TEST3 | 71947 |   351K|   280   (1)| 00:00:04 |
---------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       5760 consistent gets
          0 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71947 rows processed
--我们看见上面的执行计划走了全表扫描，这是因为没有对oracle索引中不会存储null值，
而我们的查询中有要返回所有的值，索引会走全表扫面。
--如果此时加上限定条件 where objec_id is not null 则会走 index fast full scan
-更改列object_id属性为 not null
SQL> alter table test3 modify (object_id not null);

表已更改。

SQL> select object_id from test3;

已选择71947行。

执行计划
----------------------------------------------------------
Plan hash value: 1226029696

-------------------------------------------------------------------------------------
| Id | Operation            | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              | 71947 |   351K|    45   (0)| 00:00:01 |
|   1 | INDEX FAST FULL SCAN| IDX_TEST3_ID | 71947 |   351K|    45   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
        208 recursive calls
          0 db block gets
       4982 consistent gets
          0 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
      71947 rows processed
--如上所示，限定为not null 后走了index fast full scan
--添加限定条加走 index full scan
SQL> select /*+ index(test3 idx_test3_id) */ object_id from test3;

已选择71947行。

执行计划
----------------------------------------------------------
Plan hash value: 3789893995

---------------------------------------------------------------------------------
| Id | Operation        | Name         | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              | 71947 |   351K|   160   (0)| 00:00:02 |
|   1 | INDEX FULL SCAN | IDX_TEST3_ID | 71947 |   351K|   160   (0)| 00:00:02 |
---------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       4946 consistent gets
          0 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71947 rows processed
--添加order by也会走index full scan，这里会注意到index full scan的开销是160，
而index fast full scan的开销只有45
SQL> select object_id from test3 order by object_id;

已选择71947行。

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       4946 consistent gets
          0 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71947 rows processed
--添加hint使带有order by字句的查询强制走index fast full scan，
但是注意cast情况还是和index full scan大致相同
SQL> select /*+ index_ffs(test3 idx_test3_id) */ object_id from test3 order by o
bject_id;

已选择71947行。

执行计划
----------------------------------------------------------
Plan hash value: 1094006042

--------------------------------------------------------------------------------
--------------
| Id | Operation             | Name         | Rows | Bytes |TempSpc| Cost (%CP
U)| Time     |
--------------------------------------------------------------------------------
--------------
|   0 | SELECT STATEMENT      |              | 71947 |   351K|       |   267   (
2)| 00:00:04 |
|   1 | SORT ORDER BY        |              | 71947 |   351K|   856K|   267   (
2)| 00:00:04 |
|   2 |   INDEX FAST FULL SCAN| IDX_TEST3_ID | 71947 |   351K|       |    45   (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------

统计信息
----------------------------------------------------------
         48 recursive calls
          0 db block gets
        175 consistent gets
          1 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      71947 rows processed

SQL> select /*+ index_ffs(test3 idx_test3_id) */ object_id from test3;

已选择71947行。

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       4953 consistent gets
          0 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71947 rows processed

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select /*+ index_ffs(test3 idx_test3_id) */ object_id from test3;

已选择71947行。

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       4953 consistent gets
        161 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71947 rows processed
--使用hint，提示走全表扫描
SQL> select /*+ full(test3) */ object_id from test3;

已选择71947行。

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       5760 consistent gets
       1025 physical reads
          0 redo size
     843616 bytes sent via SQL*Net to client
      53148 bytes received via SQL*Net from client
       4798 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      71947 rows processed

--执行count(*)则会走index fast full scan
SQL> select count(*) from test3;

执行计划
----------------------------------------------------------
Plan hash value: 2224805646

------------------------------------------------------------------------------
| Id | Operation             | Name         | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    45   (0)| 00:00:01 |
|   1 | SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TEST3_ID | 71947 |    45   (0)| 00:00:01 |
------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        167 consistent gets
          0 physical reads
          0 redo size
        345 bytes sent via SQL*Net to client
        392 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
--添加hint，强制走index full scan
SQL> select /*+ index(test3 idx_test3_id) */ count(*) from test3;

执行计划
----------------------------------------------------------
Plan hash value: 2484012340

-------------------------------------------------------------------------
| Id | Operation        | Name         | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |   160   (0)| 00:00:02 |
|   1 | SORT AGGREGATE |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| IDX_TEST3_ID | 71947 |   160   (0)| 00:00:02 |
-------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        160 consistent gets
          0 physical reads
          0 redo size
        345 bytes sent via SQL*Net to client
        392 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> spool off
--总结：
--1、按索引顺序查询的一定会走index full scan，因为他是按照索引的顺序single I/O操作来完成的，即一块一块的读取。
--2、输出结果不需要是有序的则会走index fast full scan，因为这样他的效率会更高一些，
oracle是执行的multiple I/O操作来完成的，一次读取多个数据块，读取个数由参数db_file_multiblock_read_count来决定的

zhou689689

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
full index scan and fast full index scan

官方文档：Full Index ScanIn a full index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the
复制链接

扫一扫