ORACLE中的常见执行计划

 本文梳理、归纳了在之前工作中常见的一些执行计划。了解ORACLE中有哪些可能的执行计划,以及什么情况下适合哪种执行计划是进行SQL优化的基础。

表访问相关

Full Table Scans

 全表扫描首先会读取表中的所有行,然后过滤掉不满足条件的数据。全表扫描时,数据库会以此读取HWM下的所有格式化了的数据块,此时数据库通常会做multiblock read来提高性能,单次读取的数据块由DB_FILE_MULTIBLOCK_READ_COUNT参数指定。
 以下情况会做全表扫描:

  1. 查询列上不存在索引
  2. 在索引列上使用了函数
  3. 执行SELECT COUNT(*)语句,存在索引,但是索引包含空值
  4. 未使用B-TREE索引的前导列。如存在employees(first_name,last_name)的索引,但是查询条件为WHERE last_name=’KING’。但是优化器有可能选择index skip scan
  5. 查询选择性很低的时候
  6. 统计信息陈旧
  7. 当表很小的时候,包含的数据块数n小于DB_FILE_MULTIBLOCK_READ_COUNT参数指定的值
  8. 当表具有很高的并行度的时候
  9. 使用了FULL的hints

 下面是一个做全表扫描的列子,其执行计划的关键字为’TABLE ACCESS FULL’:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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查询的时候会使用这种访问方式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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关键字。它有以下两种形式:

  1. SAMPLE (sample_percent)
    数据库读取表中指定百分比的行数据。
  2. 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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关键字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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 语句并且排序字段为非空且有索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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

 当查询的列仅仅包含索引列,且不需要排序的时候,优化器会考虑该访问方式,它会以磁盘存储位置来读取,不会保证数据的有序性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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的成本是非常的,绝大多数情况下是不会出现这种情况,通过索引去探测表通常成本更低。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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之间的转换。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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关键字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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循环,通过外部表的每一条数据去匹配内部表的数据:

1
2
3
4
5
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) 指定内部表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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:

  • 连接条件不是等值连接,如>,<>
  • 由于其他操作需要排序,优化器发现使用排序合并成本更低。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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的方式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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函数

 下面是一个执行了视图合并的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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取消视图合并,对比下执行计划。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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这个视图进行单独的处理。

2.复杂视图合并
 复杂视图合并会合并包含GROUP BY和DISTINCT操作的视图,优化器会把GROUP BY和DISTINCT操作延迟到连接之后。是否进行复杂视图合并,取决于优化器对合并后性能的评估。抛开成本问题,以下情况不会进行复杂视图合并:

  • 外部查询表没有rowid或者unique column
  • 视图出现在CONNECT BY查询中
  • 视图包含 GROUPING SETS, ROLLUP, 或PIVOT
  • 视图或者外部查询包含MODEL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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表明此时做的是谓词推入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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)时,优化器才会执行此转换。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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执行子查询展开。此时的执行计划如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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官网的一个例子:

  1. 表sales根据time_id进行范围分区

    1
    2
    3
    4
    SELECT *
    FROM sales
    WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
    AND prod_id = 38;
  2. 执行该语句得到的执行计划如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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)
  3. 然后将sales_q4_2003分区的索引禁用

    1
    2
    ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
    ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
  4. 此时数据库在有索引的分区采用索引检索数据,对没有索引的分区进行全表扫描。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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语句。