Oracle执行计划 - 怎么通过查看执行计划建立索引 - 《SQL优化入门》

我们利用如下 SQL 讲解（基于 Oracle11gR2 scott）。

explain plan for select e.ename,e.job,d.dname from emp e,dept d  
where e.deptno=d.deptno and e.sal<2000;

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |   488 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     8 |   488 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     8 |   312 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("E"."SAL"<2000)
Note
-----
   - dynamic sampling used for this statement (level=2)

执行计划分为两部分，Plan hash value 和 Predicate Information 之间这部分主要是表的访问路径以及表的连接方式。关于访问路径以及表连接方式会在之后章节详细解释。另外一部分是谓词过滤信息，这部分信息位于 Predicate Information 下面，谓词过滤信息非常重要。一些老 DBA 因为之前接触的是 Oracle8i 或者 Oracle9i，那个时候执行计划还没有谓词信息，所以就遗留了一个传统，看执行计划只看访问路径和表连接方式了，而不关心谓词过滤信息。还有些人做 SQL 优化喜欢用 10 046 trace 或者 10 053 trace，如果仅仅是优化一个 SQL，根本就不需要使用上面两个工具，直接分析 SQL 语句以及执行计划即可。当然，如果是为了深入研究为什么不走索引，为什么走了嵌套循环而没走 HASH 连接等，这个时候我们可以用 10 053 trace；如果想研究访问路径是单块读或者是多块读，可以使用 10 046 trace。

我们这里先不讲怎么阅读执行计划，后面会讲利用光标移动大法阅读执行计划。

注意观察 Id 这列，有些 Id 前面有”“号，这表示发生了谓词过滤，或者发生了 HASH 连接，或者是走了索引。Id=1前面有”“号，它是 HASH 连接的”“号，我们观察对应的谓词过滤信息就能知道是哪两个表进行的 HASH 连接，而且能知道是对哪些列进行的 HASH 连接，这里是 e 表（emp 表的别名）的 deptno 列与 d 表（dept 的别名）deptno 列进行 HASH 连接的。Id=3前面有”“号，这里表示表 emp 有谓词过滤，它的过滤条件就是Id=3对应的谓词过滤信息，也就是e.sal<2000。Id=2前面没有「*」号，那么说明 dept 表没有谓词过滤条件。

提问：TABLE ACCESS FULL 前面没有「*」号怎么办？

回答：如果表很小，那么不需理会，小表不会产生性能问题。如果表很大，那么我们要询问开发人员是不是忘了写过滤条件，当然了一般也不会遇到这种情况。如果真的是没过滤条件呢？比如一个表有 10GB，但是没有过滤条件，那么它就会成为整个 SQL 的性能瓶颈。这个时候我们需要查看 SQL 语句中该表访问了多少列，如果访问的列不多，就可以把这些列组合起来，建立一个组合索引，索引的大小可能就只有 1GB 左右。我们利用 INDEX FAST FULL SCAN 代替 TABLE ACCESS FULL。在访问列不多的情况，索引的大小（Segment Size）肯定比表的大小（Segment Size）小，那么就不需要扫描 10GB 了，只需要扫描 1GB，从而达到优化目的。如果 SQL 语句里面要访问表中大部分列，这时就不应该建立组合索引了，因为此时索引大小比表更大，可以通过其他方法优化，比如开启并行查询，或者更改表连接方式，让大表作为嵌套循环的被驱动表，同时在大表的连接列上建立索引。关于表连接方式，我们会在后面章节详细介绍。

提问：TABLE ACCESS FULL 前面有「*」号怎么办？

回答：如果表很小，那么我们不需理会；如果表很大，可以使用select count(*)from 表，查看有多少行数据，然后通过select count(*) from 表 where *对应的谓词过滤条件，查看返回多少行数据。如果返回的行数在表总行数的 5% 以内，我们可以在过滤列上建立索引。如果已经存在索引，但是没走索引，这时我们要检查统计信息，特别是直方图信息。如果统计信息已经收集过了，我们可以用 HINT 强制走索引。如果有多个谓词过滤条件，我们需要建立组合索引并且要将选择性高的列放在前面，选择性低的列在后面。如果返回的行数超过表总行数的 5%，这个时候我们要查看 SQL 语句中该表访问了多少列，如果访问的列少，同样可以把这些列组合起来，建立组合索引，建立组合索引的时候，谓词过滤列在前面，连接列在中间，select 部分的列在最后。如果访问的列多，这个时候就只能走全表扫描了。

提问：TABLE ACCESS BY INDEX ROWID 前面有「*」号怎么办？

回答：我们利用如下 SQL 讲解（基于 Oracle11gR2 scott）。

grant dba to scott;

授权成功。

create table test as select * from dba_objects;

表已创建。

create index idx_name on test(object_name);

索引已创建。

set autot trace
select /*+ index（test） */ * from test where object_name like 'V_$%'
and owner='SCOTT';

未选定行 
执行计划 
----------------------------------------------------------
Plan hash value: 461797767
-------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows  | Bytes | Cost（%CPU）| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |    38 |  7866 |  334   (0)| 00:00:05 |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    38 |  7866 |  334   (0)| 00:00:05 |
|* 2 |   INDEX RANGE SCAN          | IDX_NAME |   672 |       |    6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
   1 - filter（「OWNER」='SCOTT』）
   2 - access（「OBJECT_NAME」 LIKE 『V_$%』）
       filter（「OBJECT_NAME」 LIKE 『V_$%』）
Note
-----
   - dynamic sampling used for this statement （level=2）

统计信息

----------------------------------------------------------
          0  recursive calls
          0  db block gets
<strong>        332  consistent gets</strong>
          0  physical reads
          0  redo size
       1191  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

TABLE ACCESS BY INDEX ROWID 前面有”*”号，表示回表再过滤。回表再过滤说明数据没有在索引中过滤干净。当 TABLE ACCESS BY INDEX ROWID 前面有”“号时，**可以将「」号下面的过滤条件包含在索引中**，这样可以减少回表次数，提升查询性能。

create index idx_ownername on test（owner,object_name）;

 索引已创建

select /*+ index（test） */ * from test where  object_name like 'V_$%'
and owner='SCOTT';

 未选定行 
 执行计划 
----------------------------------------------------------
Plan hash value: 3756723214
-------------------------------------------------------------------------------------
| Id |Operation                   |Name         |Rows | Bytes | Cost（%CPU）| Time     |
-------------------------------------------------------------------------------------
|  0 |SELECT STATEMENT            |             |   38|  7866 |    5   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID|TEST         |   38|  7866 |    5   (0)| 00:00:01 |
|* 2 |  INDEX RANGE SCAN          |IDX_OWNERNAME|    3|       |    3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
   2 - access（「OWNER」='SCOTT』 AND 「OBJECT_NAME」 LIKE 『V_$%』）
       filter（「OBJECT_NAME」 LIKE 『V_$%』）
Note
-----
   - dynamic sampling used for this statement （level=2）
 统计信息 
----------------------------------------------------------
          0  recursive calls
          0  db block gets
<strong>          3  consistent gets</strong>
          0  physical reads
          0  redo size
       1191  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts （memory）
          0  sorts （disk）
          0  rows processed

如果索引返回的数据本身很少，即使 TABLE ACCESS BY INDEX ROWID 前面有「*」号，也可以不用理会，因为索引本身返回的数据少，回表也没有多少次，因此可以不用再创建组合索引。

通过上面的讲解，相信大家也明白了为什么我们不推荐使用工具查看执行计划，因为有些工具看不到「*」号，看不到谓词过滤信息。