index full scan 和index fast full scan
RDBMS 12.2.0.1
参考文档：
Optimizer Access Paths (oracle.com)
根据官方文档，简单总结下：
index full scan和index fast full scan的主要区别，是读取索引的记录是否有序。
index full scan -- 读取索引，有序 ，使用单块IO读取索引 （单块读IO效率较高）
index fast full scan -- 读取索引，无序 ，使用多块IO读取索引
-- 以下为官方文档的说明
8.3.4 Index Full Scans
An index full scan reads the entire index in order. An index full scan can eliminate a separate sorting operation because the data in the index is ordered by index key.
This section contains the following topics:
8.3.4.1 When the Optimizer Considers Index Full Scans
The optimizer considers an index full scan in a variety of situations.
The situations include the following:
-
A predicate references a column in the index. This column need not be the leading column.
-
No predicate is specified, but all of the following conditions are met:
-
All columns in the table and in the query are in the index.
-
At least one indexed column is not null.
-
-
A query includes an
ORDER BYon indexed non-nullable columns.
8.3.4.2 How Index Full Scans Work
The database reads the root block, and then navigates down the left hand side of the index (or right if doing a descending full scan) until it reaches a leaf block.
Then the database reaches a leaf block, the scan proceeds across the bottom of the index, one block at a time, in sorted order. The database uses single-block I/O rather than multiblock I/O.
The following graphic illustrates an index full scan. A statement requests the departments records ordered by department_id.
8.3.5 Index Fast Full Scans
An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.
This section contains the following topics:
8.3.5.1 When the Optimizer Considers Index Fast Full Scans
The optimizer considers this scan when a query only accesses attributes in the index.
Note:
Unlike a full scan, a fast full scan cannot eliminate a sort operation because it does not read the index in order.
The INDEX_FFS(table_name index_name) hint forces a fast full index scan.
See Also:
Oracle Database SQL Language Reference to learn more about the INDEX hint
8.3.5.2 How Index Fast Full Scans Work
The database uses multiblock I/O to read the root block and all of the leaf and branch blocks. The databases ignores the branch and root blocks and reads the index entries on the leaf blocks.
-- 以下为测试
-- index full scan
[email protected]> set autotrace traceonly
[email protected]> SELECT department_id, department_name
2 FROM departments
3 ORDER BY department_id;
27 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3145200496
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 432 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
50 recursive calls
0 db block gets
112 consistent gets
2 physical reads
0 redo size
1358 bytes sent via SQL*Net to client
618 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
27 rows processed
[email protected]> -- index fast full scan
-- 不加hint 提示，使用的是 index full scan
[email protected]> SELECT COUNT(*) FROM departments;
Execution Plan
----------------------------------------------------------
Plan hash value: 373888742
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| DEPT_ID_PK | 27 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
607 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 fast full scan
[email protected]> SELECT /*+ INDEX_FFS(departments dept_id_pk) */ COUNT(*) FROM departments;
Execution Plan
----------------------------------------------------------
Plan hash value: 2315979460
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
542 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
[email protected]> -- 再次使用两个语句，验证两种扫描方式的效率 ，可以看到 index full scan 效率较高 （一致读，物理读等等的比较）
SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;
SELECT first_name FROM employees e;[email protected]> SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1847877723
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 749 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 749 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
129 recursive calls
0 db block gets
202 consistent gets
2 physical reads
0 redo size
2842 bytes sent via SQL*Net to client
684 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
107 rows processed[email protected]> SELECT first_name FROM employees e;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 749 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_NAME_IX | 107 | 749 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2842 bytes sent via SQL*Net to client
684 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
[email protected]> END
上一篇： MySQL优化
下一篇： 删除数据库中id重复的记录
推荐阅读
-
[20180316]为什么不使用INDEX FULL SCAN (MIN/MAX).txt
-
index range scan,index fast full scan,index skip scan发生的条件
-
index full scan 和index fast full scan
-
[20180316]为什么不使用INDEX FULL SCAN (MIN/MAX).txt
-
index range scan,index fast full scan,index skip scan发生的条件
-
通过案例学调优之--Index FULL SCAN和Index FAST FULL SCAN
-
通过案例学调优之--Index FULL SCAN和Index FAST FULL SCAN