Oracle 全表扫描及其执行计划（full table scan）

全表扫描是Oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到SQL语句执行计划中的全表扫描，就要考虑对其进行修理一番。全表扫描的存在，的确存在可能优化的余地。但事实上很多时候全表扫描也并非是最低效的，完全要看不同的情形与场合，任一方式都是有利有弊的，也就是具体情况要具体分析。本文描述了什么是全表扫描以及何时发生全表扫描，何时全表扫描才低效。本文涉及到的相关链接：高水位线和全表扫描 http://www.linuxidc.com/Linux/2013-05/84875.htm
启用 AUTOTRACE 功能 http://www.linuxidc.com/Linux/2011-01/31634.htm
Oracle 测试常用表BIG_TABLE http://www.linuxidc.com/Linux/2013-03/80274.htm
Oracle db_file_mulitblock_read_count参数 http://www.linuxidc.com/Linux/2013-05/84876.htm1、什么是全表扫描？
全表扫描就是扫表表中所有的行，实际上是扫描表中所有的数据块，因为Oracle中最小的存储单位是Oracle block。
扫描所有的数据块就包括高水位线以内的数据块，即使是空数据块在没有被释放的情形下也会被扫描而导致I/O增加。
在全表扫描期间，通常情况下，表上这些相邻的数据块被按顺序（sequentially）的方式访问以使得一次I/O可以读取多个数据块。
一次读取更多的数据块有助于全表扫描使用更少的I/O，对于可读取的数据块被限制于参数DB_FILE_MULTIBLOCK_READ_COUNT。2、何时发生全表扫描？
a、表上的索引失效或无法被使用的情形（如对谓词使用函数、计算、NULL值、不等运算符、类型转换）
b、查询条件返回了整个表的大部分数据
c、使用了并行方式访问表
d、使用full 提示
e、统计信息缺失时使得Oracle认为全表扫描比索引扫描更高效
f、表上的数据块小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能产生全表扫描
3、演示全表扫描的情形a、准备演示环境
scott@ORA11G> select * from v$version where rownum<2;BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production--创建表t
scott@ORA11G> CREATE TABLE t
2 AS
3 SELECT rownum AS n, rpad（"*",100,"*"） AS pad
4 FROM dual
5 CONNECT BY level <= 1000;Table created.--添加索引
scott@ORA11G> create unique index t_pk on t（n）;Index created.scott@ORA11G> alter table t add constraint t_pk primary key（n） using index t_pk;Table altered.--收集统计信息
scott@ORA11G> execute dbms_stats.gather_table_stats（"SCOTT","T",cascade=>true）;PL/SQL procedure successfully completed.scott@ORA11G> set autot trace exp;
scott@ORA11G> select count（*） from t; --->count（*）的时候使用了索引快速扫描Execution Plan
----------------------------------------------------------
Plan hash value: 454320086
----------------------------------------------------------------------
| 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| T_PK | 1000 | 2 （0）| 00:00:01 |
----------------------------------------------------------------------scott@ORA11G> set autot off;
scott@ORA11G> alter table t move; --->进行move tableTable altered.-->move 之后索引失效，如下所示
scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: tTable Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
------------- -------------- -------------------- ------ -------- --------------- ----
T T_PK N 1 UNUSABLE NORMAL ASC
b、索引失效导致全表扫描
scott@ORA11G> set autot trace exp;
scott@ORA11G> select count（*） from t; Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost （%CPU）| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1000 | 7 （0）| 00:00:01 |
-------------------------------------------------------------------scott@ORA11G> set autot off;
scott@ORA11G> alter index t_pk rebuild; -->重建索引Index altered.scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: tTable Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
-------------- ---------------- -------------------- ------ -------- --------------- ----
T T_PK N 1 VALID NORMAL ASC
c、返回了整个表的大部分数据使用了全表扫描
scott@ORA11G> select count（pad） from t where n<=990;Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 7 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
|* 2 | TABLE ACCESS FULL| T | 991 | 101K| 7 （0）| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
2 - filter（"N"<=990）--返回小部分数据时，使用的是索引扫描
scott@ORA11G> select count（pad） from t where n<=10;Execution Plan
----------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 3 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 1050 | 3 （0）| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 10 | | 2 （0）| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
3 - access（"N"<=10）
d、使用并行方式访问表时使用了全表扫描
scott@ORA11G> select /*+ parallel（3） */ count（pad） from t where n<=10;Execution Plan
----------------------------------------------------------
Plan hash value: 3126468333
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 3 （0）| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 105 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC （RANDOM） | :TQ10000 | 1 | 105 | | | Q1,00 | P->S | QC （RAND） |
| 4 | SORT AGGREGATE | | 1 | 105 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 10 | 1050 | 3 （0）| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T | 10 | 1050 | 3 （0）| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
6 - filter（"N"<=10）
Note
-----
- Degree of Parallelism is 3 because of hint
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612
e、使用full提示时使用了全表扫描
scott@ORA11G> select /*+ full（t） */ count（pad） from t where n<=10;Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 7 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
|* 2 | TABLE ACCESS FULL| T | 10 | 1050 | 7 （0）| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
2 - filter（"N"<=10）
f、统计信息缺失导致全表扫描的情形
scott@ORA11G> exec dbms_stats.delete_table_stats（"SCOTT","T"）;PL/SQL procedure successfully completed.scott@ORA11G> select count（pad） from t where n<=10;Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | TABLE ACCESS FULL| T | 10 | 650 | 7 （0）| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
2 - filter（"N"<=10）
Note
-----
- dynamic sampling used for this statement （level=2）--上面的执行计划使用了全表扫描，而且提示使用了动态采样，也就是缺乏统计信息
--表上的数据块小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能产生全表扫描的情形不演示

redis单机单实例一键安装脚本Oracle 高水位线和全表扫描相关资讯 Oracle执行计划 Oracle全表扫描

执行计划出现COLLECTION ITERATOR （07/23/2015 16:25:04）
Oracle关于执行计划的简要分析（09/23/2014 18:58:58）
Oracle使用STORED OUTLINE固化执行（01/19/2014 13:10:10）

Oracle 执行计划中的buffer sort （12/26/2014 19:32:05）
使用hint优化Oracle的执行计划（06/21/2014 09:53:44）
Oracle获取执行计划的几种方法（06/17/2013 06:42:24）

本文评论查看全部评论（0）