自己在学习oracle优化课程的时候,想当然的以为count(*) 操作肯定是会走索引的。可是那天碰巧试试发现居然是全表扫描。
这里把关于这个问题的解决的过程记录下来,以便以后查阅,也希望能给同时遇见这个问题的朋友一点帮助。
--打开执行计划
SQL> set autotrace traceonly
--刚开使的查询计划
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 使用 hints 仍然是全表扫描
SQL> select /*+ index(PK_EMP) */ count(*) from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--分析表
SQL> analyze table emp compute statistics;
表已分析。
-- 再次执行 则开始走索引
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
问题解决,但仍有许多需要解释一下的地方。
1. rbo与cbo
rbo是基于规则的,在rbo下需要程序员自己分析然后指定应该使用那一个索引效率更高,也就是要使用hints。 而在cbo是基于cost的,这样sql引擎会自己计算用那个索引的cost最小,然后选择该索引执行sql语句。但是cbo需要有表的统计信息。
rbo是oracle早期版本7i的特性,8i,9i是即支持rbo也支持cbo的 但oracle 10g就只有cbo了,所以在10g中oracle会自己对表进行分析,并保存表的统计信息。
所以在没有对表进行分析的时候 走 rbo, 分析过的表走 cbo
2. 索引中不能含有null值,如果建索引的列不是 not null的,那么索引将不被使用。
其它索引不被使用的情况:
1)当对同一个表的两个列(其中一个列为主键,一个列为索引) 进行比较时,索引有时不会被使用
2)在where中出现 is null或者is not null时 索引不能被使用
3)在where语句中存在有not function时 例:not in,not exist,<>,>,<等情形下索引不能被使用
4)当使用了single-row function时 例nvl,to_char,lower等索引不能被使用
5)当使用通配符号%或者_作为查询字符串的第一个字符时索引不会被使用
这里把关于这个问题的解决的过程记录下来,以便以后查阅,也希望能给同时遇见这个问题的朋友一点帮助。
--打开执行计划
SQL> set autotrace traceonly
--刚开使的查询计划
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 使用 hints 仍然是全表扫描
SQL> select /*+ index(PK_EMP) */ count(*) from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--分析表
SQL> analyze table emp compute statistics;
表已分析。
-- 再次执行 则开始走索引
SQL> select count(*) from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
问题解决,但仍有许多需要解释一下的地方。
1. rbo与cbo
rbo是基于规则的,在rbo下需要程序员自己分析然后指定应该使用那一个索引效率更高,也就是要使用hints。 而在cbo是基于cost的,这样sql引擎会自己计算用那个索引的cost最小,然后选择该索引执行sql语句。但是cbo需要有表的统计信息。
rbo是oracle早期版本7i的特性,8i,9i是即支持rbo也支持cbo的 但oracle 10g就只有cbo了,所以在10g中oracle会自己对表进行分析,并保存表的统计信息。
所以在没有对表进行分析的时候 走 rbo, 分析过的表走 cbo
2. 索引中不能含有null值,如果建索引的列不是 not null的,那么索引将不被使用。
其它索引不被使用的情况:
1)当对同一个表的两个列(其中一个列为主键,一个列为索引) 进行比较时,索引有时不会被使用
2)在where中出现 is null或者is not null时 索引不能被使用
3)在where语句中存在有not function时 例:not in,not exist,<>,>,<等情形下索引不能被使用
4)当使用了single-row function时 例nvl,to_char,lower等索引不能被使用
5)当使用通配符号%或者_作为查询字符串的第一个字符时索引不会被使用