原文发表在http://l4j.cc/2019/04/21/oralce-access-path-introduce/
本文梳理、归纳了在之前工作中常见的一些执行计划。了解ORACLE中有哪些可能的执行计划,以及什么情况下适合哪种执行计划是进行SQL优化的基础。
表访问相关
Full Table Scans
全表扫描首先会读取表中的所有行,然后过滤掉不满足条件的数据。全表扫描时,数据库会以此读取HWM下的所有格式化了的数据块,此时数据库通常会做multiblock read来提高性能,单次读取的数据块由DB_FILE_MULTIBLOCK_READ_COUNT
参数指定。
以下情况会做全表扫描:
- 查询列上不存在索引
- 在索引列上使用了函数
- 执行SELECT COUNT(*)语句,存在索引,但是索引包含空值
- 未使用B-TREE索引的前导列。如存在employees(first_name,last_name)的索引,但是查询条件为WHERE last_name=‘KING’。但是优化器有可能选择index skip scan
- 查询选择性很低的时候
- 统计信息陈旧
- 当表很小的时候,包含的数据块数n小于DB_FILE_MULTIBLOCK_READ_COUNT参数指定的值
- 当表具有很高的并行度的时候
- 使用了FULL的hints
下面是一个做全表扫描的列子,其执行计划的关键字为’TABLE ACCESS FULL’:
SQL> select owner,table_name from test_env.tb_table_list where owner='AUDSYS';
OWNER TABLE_NAME
-------------------- ------------------------------
AUDSYS AUD$UNIFIED
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select owner,table_name from test_env.tb_table_list where owner='AUDSYS'
Plan hash value: 1475094007
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TB_TABLE_LIST |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Table Access By Rowid
ROWID是数据在数据库中存储位置的内部表示,它是用来定位单个行最快的方式。通常数据库通过索引检索数据行或者指定rowid查询的时候会使用这种访问方式。
SQL> select owner,table_name from test_env.tb_table_list where TABLE_NAME='ACCESS$';
OWNER TABLE_NAME
-------------------- ------------------------------
SYS ACCESS$
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select owner,table_name from test_env.tb_table_list where
TABLE_NAME='ACCESS$'
Plan hash value: 3473397811
------------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 2 | INDEX RANGE SCAN | IDX_TB_TABLE_LIST_TBNAME |
------------------------------------------------------------------------
TABLE ACCESS BY INDEX ROWID BATCHED
表示数据库通过索引中得到的rowid来检索数据,BATCHED
的访问方式,表示数据库会从索引中检索一批ROWID,然后按块顺序访问行,减少访问数据块的次数来提升性能。这是ORACLE 12C的一个新特性。在11g中上面的执行计划表示为TABLE ACCESS BY INDEX ROWID
。
Sample Table Scans
这是抽样检索数据的数据访问方式。使用SAMPLE关键字对表中数据进行抽样的时候会使用该执行计划,在执行计划中表现为TABLE ACCESS SAMPLE
关键字。它有以下两种形式:
- SAMPLE (sample_percent)
数据库读取表中指定百分比的行数据。 - SAMPLE BLOCK (sample_percent)
数据库会读取指定百分比的表数据块。
sample_percent的百分比在[0.000001,100) 范围内。
B-tree索引相关
Index Unique Scans
只有通过CREATE UNIQUE INDEX
创建唯一索引,并且在查询的时候谓词条件为等于的时候才会以该方式访问数据。唯一约束(unique和primary key),但是创建的非唯一索引是不足以让查询走Index Unique Scan的。在执行计划中的关键字是INDEX UNIQUE SCAN
。
SQL> CREATE UNIQUE INDEX TEST_ENV.IDX_TB_TABLE_LIST_SID ON TEST_ENV.TB_TABLE_LIST(SID);
索引已创建。
SQL> SELECT SID,OWNER,TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST WHERE SID='58223719640640AB8C603BC1B15D5C51';
SID OWNER TABLE_NAME
------------------------------------ -------------------- ------------------------------
58223719640640AB8C603BC1B15D5C51 SYS ACCESS$
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT SID,OWNER,TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST WHERE
SID='58223719640640AB8C603BC1B15D5C51'
Plan hash value: 3115192837
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| TB_TABLE_LIST |
| 2 | INDEX UNIQUE SCAN | IDX_TB_TABLE_LIST_SID |
-------------------------------------------------------------
已选择 15 行。
Index Range Scans
通过该索引字段查询结果可能返回多个值的时候,例如>, <, and以及对非唯一索引的=,就会以该方式访问数据,表现在执行计划上就是INDEX RANGE SCAN
关键字。
SQL> select owner,table_name from test_env.tb_table_list where TABLE_NAME='ACCESS$';
OWNER TABLE_NAME
-------------------- ------------------------------
SYS ACCESS$
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select owner,table_name from test_env.tb_table_list where
TABLE_NAME='ACCESS$'
Plan hash value: 3473397811
------------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 2 | INDEX RANGE SCAN | IDX_TB_TABLE_LIST_TBNAME |
------------------------------------------------------------------------
Index Full Scans
索引全扫描会有序的读取整个索引,由于索引是有序的,所以它会消除额外的排序操作。 以下情况会优化器会考虑执行索引全扫描:
- 谓词使用了索引中的列。该列不必是索引的前导列
- 没有谓词条件,但是查询的列为该索引的列,并且至少有一个列不为空
- 查询包含ORDER BY 语句并且排序字段为非空且有索引
SQL> select table_name from test_env.tb_table_list order by table_name;
......
TABLE_NAME
------------------------------
XSTREAM$_SERVER_CONNECTION
XSTREAM$_SUBSET_RULES
XSTREAM$_SYSGEN_OBJS
_default_auditing_options_
已选择 2138 行。
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select table_name from test_env.tb_table_list order by table_name
Plan hash value: 2901892796
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX FULL SCAN | IDX_TB_TABLE_LIST_TBNAME |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Index Fast Full Scans
当查询的列仅仅包含索引列,且不需要排序的时候,优化器会考虑该访问方式,它会以磁盘存储位置来读取,不会保证数据的有序性。
SQL> select table_name from test_env.tb_table_list;
TABLE_NAME
------------------------------
XSTREAM$_SERVER_CONNECTION
XSTREAM$_SUBSET_RULES
XSTREAM$_SYSGEN_OBJS
_default_auditing_options_
已选择 2138 行。
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select table_name from test_env.tb_table_list
Plan hash value: 3670592075
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX FAST FULL SCAN| IDX_TB_TABLE_LIST_TBNAME