Oracle 全表扫描及其执行计划(full table scan)

全表扫描是Oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到SQL语句执行计划中的全表扫描，就要考虑对其进行修理一番

全表扫描是oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到sql语句执行计划中的全表扫描，就要考虑对其进行修理一番。全表扫描的存在，，的确存在可能优化的余地。但事实上很多时候全表扫描也并非是最低效的，完全要看不同的情形与场合，任一方式都是有利有弊的，也就是具体情况要具体分析。本文描述了什么是全表扫描以及何时发生全表扫描，何时全表扫描才低效。

本文涉及到的相关链接：

高水位线和全表扫描
启用 AUTOTRACE 功能
Oracle 测试常用表BIG_TABLE
Oracle db_file_mulitblock_read_count参数

1、什么是全表扫描?
全表扫描就是扫表表中所有的行，实际上是扫描表中所有的数据块，因为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

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

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 table

Table altered.

-->move 之后索引失效，如下所示
scott@ORA11G> @idx_info         
Enter value for owner: scott
Enter value for table_name: t

Table 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: t