【Oracle】从雇员表的年龄查询看CBO、索引和Hint的作用

本例适用的Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

不保证在其它版本的Oracle上也是同样效果。

 

有一张雇员表含三个字段：

create table emp(
id int,
name nvarchar2(20),
age int,
primary key(id));

给它填充百万数据：

insert into emp
select 
    rownum,
    dbms_random.string('*',dbms_random.value(6,20)),
    dbms_random.value(18,65)
from
    dual
connect by level<1000001;

然后我们要看看select name from emp where age=42这句SQL的cost是多少，注意现在没加索引（id上的索引除外，此索引与下面的测试无关）

分别执行下面两句：

explain plan for select name from emp where age=42;
select * from table(dbms_xplan.display);

不加索引的解释计划
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 23462 |   801K|  1523   (2)| 00:00:19 |
|*  1 |  TABLE ACCESS FULL| EMP  | 23462 |   801K|  1523   (2)| 00:00:19 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("AGE"=42)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择17行。

解释计划告诉我们Cost是1523，走的是全表查询。

之后加age，name的组合索引
create index idx_age_name on emp(age,name);

再看看情况：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2015333714

--------------------------------------------------------------------------------
-

| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              | 23462 |   801K|   125   (0)| 00:00:02
|

|*  1 |  INDEX RANGE SCAN| IDX_AGE_NAME | 23462 |   801K|   125   (0)| 00:00:02
|

--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - access("AGE"=42)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择17行。

这回较好，索引起作用了，Cost降低到原来的十分之一，走的是索引范围扫描。

之所以这个索引见效显著，是因为name和age都编到了索引中，在索引里找到了age，不用回表就找到了name。

所以说，在查询条件、排序方式、显示列中出现的字段，如果确实需要，都可以编入到索引中。

如果我们只对age加索引，看cbo是会选择回表还是全表查询。

删除组合索引，只给age加单列索引
SQL> drop index idx_age_name;

索引已删除。

SQL> create index idx_age on emp(age);

索引已创建。

SQL> explain plan for select name from emp where age=42;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 23462 |   801K|  1523   (2)| 00:00:19 |
|*  1 |  TABLE ACCESS FULL| EMP  | 23462 |   801K|  1523   (2)| 00:00:19 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("AGE"=42)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择17行。

Cost又恢复了原值1523，查询方式还是走了全表查询，看来CBO认为全表查询比回表更省。

 

我们可以试试用hint强制引导oracle使用idx_age这个索引：

explain plan for select /*+ index(emp,idx_age)*/ name from emp where age=42;

执行效果：

SQL> explain plan for select /*+ index(emp,idx_age)*/ name from emp where age=42;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 977828616

--------------------------------------------------------------------------------
-------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 23462 |   801K|  5471   (1)| 00:
01:06 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     | 23462 |   801K|  5471   (1)| 00:
01:06 |

|*  2 |   INDEX RANGE SCAN          | IDX_AGE | 23462 |       |    44   (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("AGE"=42)

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择18行。

上面已经出现了使用单列索引的迹象，但Cost不减反增。

看来Cbo坚持全表查询的路子是对的。

 

这个例子告诉我们：

1.合理的索引能避免回表的发生；

2.如果索引不合理，Cbo自会选择它认为的最合理的方式；

3.hint只有合理才能提高效率。

-END-