【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-