本文内容
- 测试数据
- 演示执行计划
- 修改记录
本文简单介绍如何读一个 SQL 语句的执行计划。
我们觉得 SQL 语句执行得有点慢总是不够的，关键是知道为什么慢，在哪里慢。那么，执行计划会给出很多直观的数据。
测试数据
SCOTT 用户的表
查看 SCOTT 用户下所有的表。
SQL> select object_name from all_objects
2 where owner='SCOTT'
3 and object_type='TABLE'
4 /
OBJECT_NAME
------------------------------
SALGRADE
EMP
DEPT
BONUS
SQL>
SCOTT 用户的索引
查看 SCOTT 用户下所有的索引。
SQL> select object_name from all_objects
2 where owner='SCOTT'
3 and object_type='INDEX'
4 /
OBJECT_NAME
------------------------------
PK_EMP
PK_DEPT
SQL>
该用户下有四个表，本文主要使用 emp 和 dept 表，其中，emp 表的 empno 字段为主键；dept 表的 deptno 字段为主键。这是优化器制定执行计划所能使用的部分信息。
演示前执行 "set autot traceonly" 只显示 SQL 语句的执行计划，不显示语句的执行结果。
演示 SQL 语句的执行计划
演示 1 简单选择 emp 表，选择列表包含两个字段 empno 和 ename
SQL> select empno,ename from emp
2 /
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
838 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
执行计划中，ID 列为执行计划的步骤。执行计划是从底网上看的，也即是从 ID 编号最大的开始；Operation 和 Name 列为操作及其操作的对象；Rows 和 Bytes 列为操作的数据行及其字节大小；Cost 列为代价，它没有任何度量单位，仅仅是一个值，是根据 CPU 代价和 IO 代价算出来的，用来比较操作；Time 列为流逝时间。如下所示。
另外，看执行计划时，要注意每个步骤的缩进。
- db block gets - 请求当前块的数量。
当前块就是操作中刚好提取数据块数量，而不是在一致性读的情况下而产生的数据块数量。正常情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块。当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数量。
- consistent gets - 一致性读取数据块的数量。
这是在处理你操作时需要在一致性读的状态上处理数据块的数量。这些块产生的主要原因是因为在查询过程中，其他会话对数据块进行操作，可能对你要查询的数据块进行了修改，但我们查询的是修改之前的块，所以需要对回滚段中数据块的前映像进行查询，以保证数据的一致性。这就产生了一致性读。
- physical reads - 从磁盘读取数据块的数量。
其产生的主要原因是：1）高速缓存区不存在这些块；2）全表扫描；3）磁盘排序。Physical Reads 是我们最关心的。如果该值高，说明要从磁盘请求大量数据块到高速缓冲区，存在大量全表扫描，从而影响数据库的性能。因此，通过优化 SQL 语句，如创建索引，来避免语句进行全表扫描。
通过 physical reads、db block gets 和 consistent gets 这三个值，可以得到数据缓冲区命中率，即：
缓冲区命中率 = 1 - (physical reads / (db block gets + consistent gets))。
这是计算整个数据库的缓冲区命中率，也可以计算某个 SQL 语句的缓冲区命中率。
演示 2 与演示 1 相比字段列表是三个：empno、ename 和 sal
SQL> select empno,ename,sal from emp
2 /
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
951 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
从执行计划看，与演示 1 相比，只是选择列表多了一个字段，所以 Bytes 列比演示 1 大。
演示 3 选择并按 empno 字段倒序排序。选择列表两个字段：empno 和 ename
SQL> select empno,ename from emp order by empno desc
2 /
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3088625055
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
838 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
因为该 SQL 语句存在排序，而且排序的字段是表的主键，所以 ID 为 2 的步骤，Operation 列为降序的索引全表扫描，而且 Name 列为 PK_EMP。
演示 4 选择并按 ename 字段升序排序，但是没有为 ename 字段建立任何索引
SQL> select empno,ename from emp order by ename
2 /
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 140 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
838 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
执行计划中，因为 ename 列没有任何索引，所以 ID 为 2 的步骤，Operation 列为全表访问。
演示 5 选择工资大于 3000 的员工，选择列表包含两个字段：empno 和 ename
SQL> select empno,ename from emp
2 where sal > 3000
3 /
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 98 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 7 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">3000) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
该 SQL 语句与前面的语句相比，多了 WHERE 子句。所以执行计划最后包含了谓词信息。
从执行计划看，ID 为 1 的步骤，利用全表扫描，过滤 SAL > 3000 的数据行。之所以用了全表扫描，是因为 SAL 字段没有任何相关的索引可以利用。
演示 6 选择工资最高的前三个人
SQL> select empno,ename from emp
2 where rownum <= 3
3 order by sal desc
4 /
执行计划
----------------------------------------------------------
Plan hash value: 691404987
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 42 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 42 | 4 (25)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=3)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
执行计划中值得注意的是，ID 为 2 的步骤，这是 Oracle 为 TOP n 专门做的优化。
演示 7 左连接 emp 和 dept 表
SQL> select a.empno,a.ename,b.dname from emp a
2 left join dept b on(a.deptno=b.deptno)
3 /
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO"(+)) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
942 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
我们知道，该语句 JOIN 子句可以转换成 WHERE 子句。这体现在了执行计划后面的谓词条件。ID 为 2 和 3 的步骤，对 emp 和 dept 表都进行了全表扫描。之后进行了哈希连接。
演示 8 左连接 emp 和 dept 表，选择工资最高的前三个人，选择列表包含三个字段：empno、ename 和 dname
SQL> select a.empno,a.ename,b.dname from emp a
2 left join dept b on(a.deptno=b.deptno)
3 where rownum <= 3
4 order by a.sal desc
5 /
执行计划
----------------------------------------------------------
Plan hash value: 3599776420
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 90 | 8 (25)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN OUTER | | 14 | 420 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=3)
3 - access("A"."DEPTNO"="B"."DEPTNO"(+)) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
763 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
执行计划中，除了能看到演示 7 所体现的，还能 TOP n 操作的计划。
-
- Access - 访问谓词会影响访问路径，是全表扫描，还是索引扫描。
- Filter - 过滤谓词不会影响访问路劲，只起过滤的作用。
演示 9 演示 8 等值连接
SQL> select a.empno,a.ename,b.dname from emp a, dept b
2 where a.deptno=b.deptno
3 and rownum <= 3
4 order by a.sal desc
5 /
执行计划
----------------------------------------------------------
Plan hash value: 4235606028
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 7 (29)|00:00:01 |
| 1 | SORT ORDER BY | | 3 | 90 | 7 (29)|00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | MERGE JOIN | | 14 | 420 | 6 (17)|00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|00:00:01 |
|* 6 | SORT JOIN | | 14 | 238 | 4 (25)|00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=3)
6 - access("A"."DEPTNO"="B"."DEPTNO") filter("A"."DEPTNO"="B"."DEPTNO") 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
763 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
以上演示都是 SCOTT 用户下默认的对象，包括表和索引。下面自己创建一些索引，看看执行计划如何。
演示 10 为 sal 字段创建索引，并再次执行演示 5
SQL> create index emp_sal_idx on emp(sal)
2 /
索引已创建。
SQL> select empno,ename from emp
2 where sal > 3000
3 /
执行计划
----------------------------------------------------------
Plan hash value: 3428524353
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 7 | 98 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 7 | 98 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL_IDX | 7 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL">3000) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
执行计划显示，使用了刚才创建的 emp_sal_idx 索引。不再是全表扫描。
演示 11 部门为 30，并且工资在 1500 到 2900 所有员工
SQL> select t1.empno,t1.ename,t1.sal,t2.dname from emp t1,dept t2
2 where t1.deptno=t2.deptno
3 and t2.deptno=30 and t1.sal between 1500 and 2900
4 /
执行计划
----------------------------------------------------------
Plan hash value: 568005898
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 60 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 2 | 34 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=30) 4 - filter("T1"."DEPTNO"=30 AND "T1"."SAL"<=2900 AND "T1"."SAL">=1500) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
执行计划中，emp 表使用了全表扫描，因为目前只对 SAL 字段创建了索引，而过滤谓词中，不仅包含 emp 表的 SAL 字段，还包含 dept 表的 DEPTNO 字段。
演示 12 与演示 11 类似，唯一不同的是 WHERE 子句中条件 deptno，使用的是 emp 表，而不是 dept 表
SQL> select t1.empno,t1.ename,t1.sal,t2.dname from emp t1,dept t2
2 where t1.deptno=t2.deptno
3 and t1.deptno=30 and t1.sal between 1500 and 2900
4 /
执行计划
----------------------------------------------------------
Plan hash value: 568005898
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 60 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 2 | 34 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=30) 4 - filter("T1"."DEPTNO"=30 AND "T1"."SAL"<=2900 AND "T1"."SAL">=1500) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
执行计划与演示 11 相同。对 EMP 表仍然使用了全表扫描。
演示 13 为 EMP 表的 deptno 和 sal 字段创建组合索引，执行与演示 12 相同的操作
SQL> create index emp_deptno_sal_idx on emp(deptno,sal)
2 /
索引已创建。
SQL> select t1.empno,t1.ename,t1.sal,t2.dname from emp t1,dept t2
2 where t1.deptno=t2.deptno
3 and t1.deptno=30 and t1.sal between 1500 and 2900
4 /
执行计划
----------------------------------------------------------
Plan hash value: 668448259
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 3(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 60 | 3(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0(0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 34 | 2(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_SAL | 2 | | 1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=30) 5 - access("T1"."DEPTNO"=30 AND "T1"."SAL">=1500 AND "T1"."SAL"<=2900) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
从执行计划看，创建组合索引后，对 EMP 表使用了 EMP_DEPTNO_SAL 索引。
演示 14 利用 EMP_DEPTNO_SAL 索引，执行与演示 11 相同的操作
SQL> select t1.empno,t1.ename,t1.sal,t2.dname from emp t1,dept t2
2 where t1.deptno=t2.deptno
3 and t2.deptno=30 and t1.sal between 1500 and 2900
4 /
执行计划
----------------------------------------------------------
Plan hash value: 1121164890
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 60 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 34 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_SAL_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=30) 5 - access("T1"."DEPTNO"=30 AND "T1"."SAL">=1500 AND "T1"."SAL"<=2900) 统计信息
----------------------------------------------------------
219 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
执行计划中，除了 recursive calls 和 consistent gets 高的出奇外，其他全部一样。归根结底，组合索引 EMP_DEPTNO_SAL 是在 EMP 表上创建，而 SQL 语句的 WHERE 子句中 deptno 字段使用的却是 dept 表。
演示 15 统计表，并执行与演示 11 相同的操作
SQL> analyze table emp compute statistics
2 for table
3 for all indexes
4 for all indexed columns
5 /
表已分析。
SQL> select t1.empno,t1.ename,t1.sal,t2.dname from emp t1,dept t2
2 where t1.deptno=t2.deptno
3 and t2.deptno=30 and t1.sal between 1500 and 2900
4 /
执行计划
----------------------------------------------------------
Plan hash value: 1121164890
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 27 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."DEPTNO"=30) 5 - access("T1"."DEPTNO"=30 AND "T1"."SAL">=1500 AND "T1"."SAL"<=2900) 统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
此时，执行计划就变得正常了。
修改记录
- 2012-09-17 [UPDATE]
