Oracle 全表扫描及其执行计划

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

发表于：2013-6-06 09:52

作者：RobinsonCheng 来源：51Testing软件测试网采编

软件开发

数据库

Oracle

　　全表扫描是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<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 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;

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

Table 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值的情形可能产生全表扫描的情形不演示

31/312 3 >

《2023软件测试行业现状调查报告》独家发布~

搜索风云榜

测试技术了解

2023测试行业调查报告

挣点稿费

AI与软件测试

文章资料精选