ORACLE中的常见执行计划

最新推荐文章于 2024-04-30 14:40:53 发布

我不是VIP

最新推荐文章于 2024-04-30 14:40:53 发布

阅读量832

点赞数

分类专栏：数据库文章标签： ORACLE SQL SQL优化数据库

本文链接：https://blog.csdn.net/u010343795/article/details/89471656

版权

原文发表在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

最低0.47元/天解锁文章

我不是VIP

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE中的常见执行计划

原文发表在http://l4j.cc/2019/04/21/oralce-access-path-introduce/ 本文梳理、归纳了在之前工作中常见的一些执行计划。了解ORACLE中有哪些可能的执行计划，以及什么情况下适合哪种执行计划是进行SQL优化的基础。表访问相关Full Table Scans 全表扫描首先会读取表中的所有行，然后过滤掉不满足条件的数据。全表扫描时，数据库会...
复制链接

扫一扫