关于select count(*) from table的讨论

摘自：http://www.itpub.net/381892,2.html

下面是我测试的结果：
scott@ORCL> set autotrace traceonly
scott@ORCL> CREATE INDEX I_DEPT_NAME ON DEPT (DNAME) ;
Index created.
scott@ORCL> analyze table dept compute statistics ;
Table analyzed.
scott@ORCL> select /*+ index(i_dept_name) */ count(*) from dept ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=5)


看见dname可以为null的时候,不会使用这个索引。

scott@ORCL> ALTER TABLE DEPT MODIFY (DNAME NOT NULL)
Table altered.
scott@ORCL> analyze table dept compute statistics ;
Table analyzed.

scott@ORCL> select /*+ index(i_dept_name) */ count(*) from dept ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'I_DEPT_NAME' (NON-UNIQUE) (Cost=1 Card=5)

看见没有这回使用了索引i_dept_name,dname 不为空的时候。

现在增加一个字段foo，char(1),default ='1'

scott@ORCL> ALTER TABLE DEPT ADD foo CHAR(1) DEFAULT 1 NOT NULL ;
Table altered.
scott@ORCL> CREATE INDEX I_DEPT_foo ON DEPT (foo);
Index created.
scott@ORCL> analyze table dept compute statistics ;
Table analyzed.
scott@ORCL> select count(*) from dept ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'I_DEPT_FOO' (NON-UNIQUE) (Cost=1 Card=5)

你再仔细看看，没有使用hints，索引选择了I_DEPT_FOO，而没有选择pk_dept.
删除统计，走rbo。
scott@ORCL> Analyze Table SCOTT.DEPT Delete Statistics
Table analyzed.
scott@ORCL> select count(*) from dept ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEPT'

没有使用索引。 现在加hints。

scott@ORCL> select /*+ index(i_dept_foo) */ count(*) from dept ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=82)

还是没有使用索引。