本文梳理、归纳了在之前工作中常见的一些执行计划。了解 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 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Index Skip Scans
在复合索引中未使用前导列的情况，并且前导列只存在很少的 distinct value。其在执行计划中表现为 INDEX SKIP SCAN。
SQL> create index test_env.idx_tb_table_list_mul on test_env.tb_table_list(owner,table_name);
索引已创建。
SQL> select /*+ index(t idx_tb_table_list_mul) */sid,owner,table_name,status from test_env.tb_table_list t where table_name='ACCESS$';
SID OWNER TABLE_NAME STATUS
------------------------------------ -------------------- ------------------------------ --------
58223719640640AB8C603BC1B15D5C51 SYS ACCESS$ VALID
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ index(t idx_tb_table_list_mul) */sid,owner,table_name,status
from test_env.tb_table_list t where table_name='ACCESS$'
Plan hash value: 124701251
---------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 2 | INDEX SKIP SCAN | IDX_TB_TABLE_LIST_MUL |
---------------------------------------------------------------------
Index Join Scans
对同一个表，多个索引的 hash join。当查询结果都是索引中的列，并且存在于不同索引中时会考虑使用该数据访问方式，但是如果访问表的效率更高则会根据 rowid 去访问表。 Index join 的成本是非常的，绝大多数情况下是不会出现这种情况，通过索引去探测表通常成本更低。
SQL> select owner,table_name from test_env.tb_table_list where ini_trans>=4;
OWNER TABLE_NAME
--------------- ------------------------------
MDSYS SDO_DIST_METADATA_TABLE
SYS AW$AWCREATE
SYS AW$AWCREATE10G
SYS AW$AWMD
SYS AW$AWREPORT
SYS AW$AWXML
SYS AW$EXPRESS
SYS AW_OBJ$
SYS AW_PROP$
SYS OBJECT_USAGE
SYS PS$
OWNER TABLE_NAME
--------------- ------------------------------
SYS STREAMS$_APPLY_PROGRESS
已选择 12 行。
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 ini_trans>=4
Plan hash value: 832528447
---------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | index$_join$_001 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
| 2 | HASH JOIN | |
| 3 | BITMAP CONVERSION TO ROWIDS| |
| 4 | BITMAP INDEX RANGE SCAN | BIDX_TB_TABLE_LIST_INITRANS |
| 5 | INDEX FAST FULL SCAN | IDX_TB_TABLE_LIST_MUL |
---------------------------------------------------------------------
已选择 17 行。
Bitmap 索引相关
传统的 B-tree 索引，一条索引对应一条数据。Bitmap 的一个索引的键值通常对应一批 rowid。 Bitmap 索引适用于拥有较低 distinct 基数并且不经常修改的数据。Bitmap 索引不适用于经常进行 DML 操作的列，因为一个 index key 指向很多数据行，当对一个索引的列进行修改时，会锁住整个索引条目以及对应的数据行。 还需要注意的一点就是 Bitmap 索引列是可以包含空值的。
Bitmap Conversion to Rowid
只要从位图索引中检索行，就会用到该访问方式，进行数据行与 bitmap 之间的转换。
SQL> select owner,table_name,status,ini_trans from test_env.tb_table_list where ini_trans>=4;
OWNER TABLE_NAME STATUS INI_TRANS
--------------- ------------------------------ ---------- ----------
SYS PS$ VALID 4
SYS AW_OBJ$ VALID 4
SYS AW_PROP$ VALID 4
SYS AW$EXPRESS VALID 4
SYS AW$AWMD VALID 4
SYS AW$AWCREATE VALID 4
SYS AW$AWCREATE10G VALID 4
SYS AW$AWXML VALID 4
SYS AW$AWREPORT VALID 4
SYS OBJECT_USAGE VALID 30
SYS STREAMS$_APPLY_PROGRESS VALID 120
OWNER TABLE_NAME STATUS INI_TRANS
--------------- ------------------------------ ---------- ----------
MDSYS SDO_DIST_METADATA_TABLE VALID 255
已选择 12 行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select owner,table_name,status,ini_trans from test_env.tb_table_list
where ini_trans>=4
Plan hash value: 3195249317
---------------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 | BITMAP INDEX RANGE SCAN | BIDX_TB_TABLE_LIST_INITRANS |
---------------------------------------------------------------------------
已选择 16 行。
Bitmap Index Single Value
使用 bitmap 索引的单个键值来查询数据的时候会走该访问方式，在执行计划中表现为 BITMAP INDEX SINGLE VALUE 关键字。
SQL> SELECT OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS FROM TEST_ENV.TB_TABLE_LIST WHERE TABLESPACE_NAME='SYSTEM';
....
OWNER TABLE_NAME TABLESPACE_NAME STATUS
--------------- ------------------------------ -------------------- ----------
LBACSYS OLS$USER_LEVELS SYSTEM VALID
LBACSYS OLS$USER_COMPARTMENTS SYSTEM VALID
LBACSYS OLS$USER_GROUPS SYSTEM VALID
LBACSYS OLS$PROFILES SYSTEM VALID
LBACSYS OLS$DIP_DEBUG SYSTEM VALID
LBACSYS OLS$DIP_EVENTS SYSTEM VALID
LBACSYS OLS$AUDIT SYSTEM VALID
LBACSYS OLS$AUDIT_ACTIONS SYSTEM VALID
SYS DBMS_SQLPATCH_STATE SYSTEM VALID
SYS DBMS_SQLPATCH_FILES SYSTEM VALID
SYS AQ_SRVNTFN_TABLE_1 SYSTEM VALID
OWNER TABLE_NAME TABLESPACE_NAME STATUS
--------------- ------------------------------ -------------------- ----------
SYS REGISTRY$SQLPATCH_RU_INFO SYSTEM VALID
已选择 912 行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS FROM
TEST_ENV.TB_TABLE_LIST WHERE TABLESPACE_NAME='SYSTEM'
Plan hash value: 4282437356
-----------------------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 | BITMAP INDEX SINGLE VALUE | BIDX_TB_TABLE_LIST_TABLESPACE |
-----------------------------------------------------------------------------
已选择 16 行。
Bitmap Index Range Scans
当谓词条件在 bitmap 索引列上是一个范围值的时候，如 Bitmap Conversion to Rowid 中的例子，它在执行计划中表现为 BITMAP INDEX RANGE SCAN 关键字。
Bitmap Merge
此访问路径合并多个 Bitmap，并返回单个 Bitmap 作为结果。在执行计划中表现为 BITMAP MERGE 关键字
SQL> select owner,table_name,status,ini_trans from test_env.tb_table_list where ini_trans>=4 and tablespace_name='SYSTEM';
OWNER TABLE_NAME STATUS INI_TRANS
--------------- ------------------------------ ---------- ----------
SYS OBJECT_USAGE VALID 30
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select owner,table_name,status,ini_trans from test_env.tb_table_list
where ini_trans>=4 and tablespace_name='SYSTEM'
Plan hash value: 1400915856
-----------------------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 | BITMAP AND | |
| 4 | BITMAP INDEX SINGLE VALUE | BIDX_TB_TABLE_LIST_TABLESPACE |
| 5 | BITMAP MERGE | |
| 6 | BITMAP INDEX RANGE SCAN | BIDX_TB_TABLE_LIST_INITRANS |
-----------------------------------------------------------------------------
已选择 19 行。
表连接相关
在进行多表关联查询的时候，优化器需要考虑以下内容：
- 数据访问方式 同单表查询一样，优化器必须选择是全表扫描还是通过索引来检索表中数据
- 关联方法 即如何对表进行关联。可能的关联方式有，hash join,nested loops,sorted merge。
- 关联类型 关联条件决定了关联的类型，如 inner join，outer join 等
- 关联的顺序 在进行表关联的时候，优化器需要决定先关联哪些表，哪个表作为驱动表。基本思想就是尽快的过滤掉尽可能多的数据。
Nested Loops Joins
嵌套循环连接，可以理解为一个嵌套的 for 循环，通过外部表的每一条数据去匹配内部表的数据：
FOR erow IN outer_table LOOP
FOR drow IN inner_table LOOP
return match value
END LOOP
END LOOP
嵌套循环连接适用于以下情况：
- 数据集很小的时候
- 在 FIRST_ROW 的优化器模式下关联大表
- 关联条件可以高效的访问内部表
一般情况下，只有在数据集比较小并且关联条件存在索引的时候会使用该方式。也可以通过加入以下 hints 的方式来强制走嵌套训话连接。：
- USE_NL_WITH_INDEX (table index) 由优化器决定那个表作为驱动表，index 是可选的，不指定则由优化器决定
- ORDERED USE_NL (d) 指定内部表
SQL> select /*+ORDERED USE_NL(B) */A.owner,A.table_name,A.column_name from TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST B WHERE A.TABLE_NAME=B.TABLE_NAME AND B.TABLE_NAME='ACCESS$';
OWNER TABLE_NAME COLUMN_NAME
--------------- ------------------------------ --------------------
SYS ACCESS$ D_OBJ#
SYS ACCESS$ ORDER#
SYS ACCESS$ COLUMNS
SYS ACCESS$ TYPES
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ORDERED USE_NL(B) */A.owner,A.table_name,A.column_name from
TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST B WHERE
A.TABLE_NAME=B.TABLE_NAME AND B.TABLE_NAME='ACCESS$'
Plan hash value: 1684280275
----------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_COLUMN_LIST |
| 3 | INDEX RANGE SCAN | TB_COLUMN_LIST_TBNAME |
| 4 | INDEX RANGE SCAN | TB_TABLE_LIST_TBNAME |
----------------------------------------------------------------------
已选择 18 行。
Hash Joins
这是大数据集关联的常见方式，也是我们日常见得最多的关联方式。优化器会选择两个数据集中较小的一个表，然后用关联字段来构建 hash 表，存放在 PGA 中 (如果 PGA 大小不够存放该 hash 表，则会将部分数据放入到临时表中)，然后，数据库扫描较大的数据集，探测哈希表以查找满足连接条件的数据行。 同意通过 USE_HASH 的 hints 来强制数据库走 HASH 连接。
SQL> select /*+USE_HASH(A B) */A.owner,A.table_name,A.column_name from TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST
B WHERE A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER AND B.TABLE_NAME='ACCESS$';
OWNER TABLE_NAME COLUMN_NAME
--------------- ------------------------------ --------------------
SYS ACCESS$ D_OBJ#
SYS ACCESS$ ORDER#
SYS ACCESS$ COLUMNS
SYS ACCESS$ TYPES
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+USE_HASH(A B) */A.owner,A.table_name,A.column_name from
TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST B WHERE
A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER AND B.TABLE_NAME='ACCESS$'
Plan hash value: 209128112
----------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 3 | INDEX RANGE SCAN | TB_TABLE_LIST_TBNAME |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_COLUMN_LIST |
| 5 | INDEX RANGE SCAN | TB_COLUMN_LIST_TBNAME |
----------------------------------------------------------------------
已选择 19 行。
Sorted Merge Joins
这是 Nested Loops 的一个变种。数据库会首先执行 SORT JOIN 操作对两个数据集进行排序，然后遍历外部数据集的每一行，去匹配内部数据集，第二次开始后面每次的匹配位置，取决于前一次迭代的匹配。 以下情况会选择进行 Sorted Merge：
- 连接条件不是等值连接，如 >,<>
- 由于其他操作需要排序，优化器发现使用排序合并成本更低。
SQL> select A.owner,A.table_name,A.column_name from TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST B WHERE A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER AND B.TABLE_NAME='ACCESS$' ORDER BY TABLE_NAME;
....
OWNER TABLE_NAME COLUMN_NAME
--------------- ------------------------------ --------------------
SYS XSTREAM$_SYSGEN_OBJS SPARE5
SYS XSTREAM$_SYSGEN_OBJS SPARE4
SYS XSTREAM$_SYSGEN_OBJS SPARE3
SYS XSTREAM$_SYSGEN_OBJS SPARE2
SYS XSTREAM$_SYSGEN_OBJS SPARE6
SYS XSTREAM$_SYSGEN_OBJS OBJECT_TYPE
SYS XSTREAM$_SYSGEN_OBJS OBJECT_NAME
SYS XSTREAM$_SYSGEN_OBJS OBJECT_OWNER
SYS XSTREAM$_SYSGEN_OBJS SERVER_NAME
SYS XSTREAM$_SYSGEN_OBJS SPARE1
SYS _default_auditing_options_ A
已选择 23104 行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select A.owner,A.table_name,A.column_name from TEST_ENV.TB_COLUMN_LIST
A,TEST_ENV.TB_TABLE_LIST B WHERE A.TABLE_NAME=B.TABLE_NAME AND
A.OWNER=B.OWNER ORDER BY TABLE_NAME
Plan hash value: 3270676555
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | INDEX FAST FULL SCAN| PK_TB_TABLE_LIST |
| 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL | TB_COLUMN_LIST |
---------------------------------------------------
已选择 19 行。
查询转换相关
我们发送给 ORACLE 的目标 SQL 与最终执行的 SQL 有可能是不同的，oracle 有可能执行一系列查询转换的操作 (如视图合并，子查询展开等) 将 SQL 改写成语义等价的其他形式，是否执行查询转换取决于 ORACLE 对转换后 SQL 执行效率的评估。
OR 扩展
OR 扩展会将 OR 语句改写为 UNION-ALL 的形式，这样各个分支各自走索引、分区修剪、表连接等互不干扰。在之前版本中使用的 CONCATENATION 来执行 OR 扩展，12.2 开始采用了 UNION-ALL 的方式。
SQL> select A.owner,A.table_name,B.STATUS,A.column_name from TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST B
2 WHERE A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER AND (B.TABLE_NAME='ACCESS$' OR B.OWNER='XDB');
......
OWNER TABLE_NAME STATUS COLUMN_NAME
--------------- ------------------------------ ---------- --------------------
XDB XDB_INDEX_DDL_CACHE VALID CONSTR_OWNER
SYS ACCESS$ VALID TYPES
SYS ACCESS$ VALID D_OBJ#
SYS ACCESS$ VALID ORDER#
SYS ACCESS$ VALID COLUMNS
已选择 170 行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select A.owner,A.table_name,B.STATUS,A.column_name from
TEST_ENV.TB_COLUMN_LIST A,TEST_ENV.TB_TABLE_LIST B WHERE
A.TABLE_NAME=B.TABLE_NAME AND A.OWNER=B.OWNER AND
(B.TABLE_NAME='ACCESS$' OR B.OWNER='XDB')
Plan hash value: 1155047104
-------------------------------------------------------------------------
| Id | Operation | Name |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | VIEW | VW_JF_SET$ADB40ABC |
| 4 | UNION-ALL | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 6 | INDEX RANGE SCAN | PK_TB_TABLE_LIST |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 8 | INDEX RANGE SCAN | TB_TABLE_LIST_TBNAME |
| 9 | INDEX RANGE SCAN | TB_COLUMN_LIST_TBNAME |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 10 | TABLE ACCESS BY INDEX ROWID | TB_COLUMN_LIST |
-------------------------------------------------------------------------
已选择 25 行。
视图合并
将视图合并到包含它的查询块中。进行试图合并后优化器可以考虑更多的连接顺序、访问方式和其他的查询转换，以此来获取更好的性能。视图合并又分为简单视图合并和复杂视图合并。 1. 简单视图合并 对于简单试图，视图合并总是会带来更好的执行计划，所以数据库总是会进行合并而不会考虑成本。 存在以下情况时不会进行简单视图合并：
- 视图中包含以下结构时：GROUP BY,DISTINCT,OUTER JOIN, MODEL, CONNECT BY, 集合操作，聚合
- 视图出现在半连接或者反连接右侧时
- 视图出现在 SELECT 中的子查询
- 外部查询块包含 PL/SQL 函数
下面是一个执行了视图合并的例子：
SQL> select A.owner,A.table_name,V_B.STATUS,A.column_name from TEST_ENV.TB_COLUMN_LIST A,
(SELECT OWNER,TABLE_NAME,STATUS FROM TEST_ENV.TB_TABLE_LIST) V_B
WHERE A.TABLE_NAME=V_B.TABLE_NAME AND A.OWNER=V_B.OWNER;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC'))
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select A.owner,A.table_name,V_B.STATUS,A.column_name from
TEST_ENV.TB_COLUMN_LIST A, (SELECT OWNER,TABLE_NAME,STATUS FROM
TEST_ENV.TB_TABLE_LIST) V_B WHERE A.TABLE_NAME=V_B.TABLE_NAME AND
A.OWNER=V_B.OWNER
Plan hash value: 4162551876
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL| TB_TABLE_LIST |
| 3 | TABLE ACCESS FULL| TB_COLUMN_LIST |
---------------------------------------------
已选择 18 行。
然后加个 no_merge 的 hint 取消视图合并，对比下执行计划。
SQL> select A.owner,A.table_name,V_B.STATUS,A.column_name from TEST_ENV.TB_COLUMN_LIST A,
(SELECT /*+no_merge*/OWNER,TABLE_NAME,STATUS FROM TEST_ENV.TB_TABLE_LIST) V_B
WHERE A.TABLE_NAME=V_B.TABLE_NAME AND A.OWNER=V_B.OWNER;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select A.owner,A.table_name,V_B.STATUS,A.column_name from
TEST_ENV.TB_COLUMN_LIST A, (SELECT /*+no_merge*/OWNER,TABLE_NAME,STATUS
FROM TEST_ENV.TB_TABLE_LIST) V_B WHERE A.TABLE_NAME=V_B.TABLE_NAME AND
A.OWNER=V_B.OWNER
Plan hash value: 3859876297
----------------------------------------------
| Id | Operation | Name |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | VIEW | |
| 3 | TABLE ACCESS FULL| TB_TABLE_LIST |
| 4 | TABLE ACCESS FULL | TB_COLUMN_LIST |
----------------------------------------------
已选择 19 行。
可以看到第二个执行计划出现了 view 关键字，表示会把 V_B 这个视图进行单独的处理。
- 复杂视图合并 复杂视图合并会合并包含 GROUP BY 和 DISTINCT 操作的视图，优化器会把 GROUP BY 和 DISTINCT 操作延迟到连接之后。是否进行复杂视图合并，取决于优化器对合并后性能的评估。抛开成本问题，以下情况不会进行复杂视图合并：
- 外部查询表没有 rowid 或者 unique column
- 视图出现在 CONNECT BY 查询中
- 视图包含 GROUPING SETS, ROLLUP, 或 PIVOT
- 视图或者外部查询包含 MODEL
SQL> (SELECT OWNER,TABLE_NAME,COUNT(*) AS CNT_COLS FROM TEST_ENV.TB_COLUMN_LIST GROUP BY OWNER,TABLE_NAME) V_A,
2 TEST_ENV.TB_TABLE_LIST B
3 WHERE V_A.TABLE_NAME=B.TABLE_NAME AND V_A.OWNER=B.OWNER AND B.OWNER='XDB';
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT B.OWNER,B.TABLE_NAME,B.TABLESPACE_NAME,B.STATUS,V_A.CNT_COLS
FROM (SELECT A.OWNER,A.TABLE_NAME,COUNT(*) AS CNT_COLS FROM
TEST_ENV.TB_COLUMN_LIST A GROUP BY A.OWNER,A.TABLE_NAME) V_A,
TEST_ENV.TB_TABLE_LIST B WHERE V_A.TABLE_NAME=B.TABLE_NAME AND
V_A.OWNER=B.OWNER AND B.OWNER='XDB'
Plan hash value: 3165065995
------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TABLE_LIST |
| 5 | INDEX RANGE SCAN | PK_TB_TABLE_LIST |
| 6 | INDEX RANGE SCAN | TB_COLUMN_LIST_TBNAME |
| 7 | TABLE ACCESS BY INDEX ROWID | TB_COLUMN_LIST |
------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
已选择 23 行。
可以看到执行计划中已经没有 VIEW 关键字了，并且 GROUP BY 操作延迟到了嵌套循环连接之后。
谓词推入
优化器处理带视图的目标 SQL 的另一种优化手段。数据库可以将推入的谓词来访问索引或者作为过滤条件，进而走基于索引的嵌套循环连接。 还是上面的列子，现在加一个 no_merger 的 hint，避免做视图合并。VIEW PUSHED PREDICATE 表明此时做的是谓词推入。
SQL> SELECT B.OWNER,B.TABLE_NAME,B.TABLESPACE_NAME,B.STATUS,V_A.CNT_COLS FROM
(SELECT /*+no_merge*/A.OWNER,A.TABLE_NAME,COUNT(*) AS CNT_COLS FROM TEST_ENV.TB_COLUMN_LIST A GROUP BY A.OWNER,A.TABLE_NAME) V_A,
TEST_ENV.TB_TABLE_LIST B
WHERE V_A.TABLE_NAME=B.TABLE_NAME AND V_A.OWNER=B.OWNER
AND B.OWNER='XDB';
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT B.OWNER,B.TABLE_NAME,B.TABLESPACE_NAME,B.STATUS,V_A.CNT_COLS
FROM (SELECT /*+no_merge*/A.OWNER,A.TABLE_NAME,COUNT(*) AS CNT_COLS
FROM TEST_ENV.TB_COLUMN_LIST A GROUP BY A.OWNER,A.TABLE_NAME) V_A,
TEST_ENV.TB_TABLE_LIST B WHERE V_A.TABLE_NAME=B.TABLE_NAME AND
V_A.OWNER=B.OWNER AND B.OWNER='XDB'
Plan hash value: 466935718
------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | TB_TABLE_LIST |
| 3 | INDEX RANGE SCAN | PK_TB_TABLE_LIST |
| 4 | VIEW PUSHED PREDICATE | |
| 5 | SORT GROUP BY | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_COLUMN_LIST |
| 7 | INDEX RANGE SCAN | TB_COLUMN_LIST_TBNAME |
------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
已选择 23 行。
子查询展开
在子查询展开中优化器会把嵌套的子查询转换成一个关联查询语句。只有当 JOIN 语句返回的数据行与原始 SQL 返回数据行相同，并且子查询中不包含聚合函数 (如 AVG) 时，优化器才会执行此转换。
SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME FROM TEST_ENV.TB_TABLE_LIST WHERE
2 (OWNER,TABLE_NAME) IN (SELECT OWNER, TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST);
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT OWNER,TABLE_NAME,COLUMN_NAME FROM TEST_ENV.TB_COLUMN_LIST WHERE
(OWNER,TABLE_NAME) IN (SELECT OWNER, TABLE_NAME FROM
TEST_ENV.TB_TABLE_LIST)
Plan hash value: 2699376560
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | INDEX FAST FULL SCAN| PK_TB_TABLE_LIST |
| 3 | TABLE ACCESS FULL | TB_COLUMN_LIST |
--------------------------------------------------
已选择 17 行。
从上面的执行计划可以看出，执行子查询展开后，表现为两个表进行 HASH JOIN。我们可以使用 no_unnest 的 hint 来让优化器不对该 SQL 执行子查询展开。此时的执行计划如下：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select owner,table_name,column_name from test_env.tb_column_list where
(owner,table_name) in(select /*+no_unnest*/ owner,table_name from
test_env.tb_table_list)
Plan hash value: 2714665686
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FILTER | |
| 2 | TABLE ACCESS FULL| TB_COLUMN_LIST |
| 3 | INDEX UNIQUE SCAN| PK_TB_TABLE_LIST |
-----------------------------------------------
已选择 17 行。
不进行子查询展开后的执行计划走的是 FILTER 的类型的过滤了。
表扩展
基于索引可以提高查询的性能，但是维护索引也会产生开销。在一个大的分区表中（特别是采用业务时间做 PARTITION KEY 的分区表），经常 DML 操作比较频繁的就几个分区，其他分区大多数时候都做的全表检索的时间比较多。在类似这种场景下我们就可以只在比较活跃的分区上面创建索引，而其他分区则不创建。优化器会自动的帮我们在有索引的分区上面通过索引检索数据，没有索引的分区进行全表扫描，然后再把结果进行 UNION-ALL 操作。 下面是 ORACLE 官网的一个例子：
- 表 sales 根据 time_id 进行范围分区
SELECT *
FROM sales
WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND prod_id = 38;
- 执行该语句得到的执行计划如下：
Plan hash value: 3087065703
--------------------------------------------------------------------------
|Id| Operation | Name |Pstart|Pstop|
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |
| 1| PARTITION RANGE ITERATOR | | 13 | 28 |
| 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13 | 28 |
| 3| BITMAP CONVERSION TO ROWIDS | | | |
|*4| BITMAP INDEX SINGLE VALUE |SALES_PROD_BIX| 13 | 28 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PROD_ID"=38)
- 然后将 sales_q4_2003 分区的索引禁用
ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
- 此时数据库在有索引的分区采用索引检索数据，对没有索引的分区进行全表扫描。
Plan hash value: 2120767686
---------------------------------------------------------------------------
|Id| Operation | Name |Pstart|Pstop|
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |
| 1| VIEW | VW_TE_2 | | |
| 2| UNION-ALL | | | |
| 3| PARTITION RANGE ITERATOR | | 13| 27|
| 4| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13| 27|
| 5| BITMAP CONVERSION TO ROWIDS | | | |
|*6| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX| 13| 27|
| 7| PARTITION RANGE SINGLE | | 28| 28|
|*8| TABLE ACCESS FULL | SALES | 28| 28|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("PROD_ID"=38)
8 - filter("PROD_ID"=38)
通过将非活跃分区的索引删除的方式可以减少维护索引的成本，优化器会自动的帮我们进行查询的改写，而不影响我们原本的 SQL 语句。
