count效率分析

count写法有四种
count(*)统计不为空的总和
count(1)同count(*)，1代表一个常量，等同于count(100)
count(column1)若column1列不为空，等同于count(*)
count(column2)column2列可以为空，统计column2不为空的总和

前三个都会优先选择cost更小的索引
对表进行分析后analyze table t compute statistics for table for all indexes for all indexed columns
可以通过查看执行计划来跟踪效率
set autot on,如果提示没有plan_table则执行
@?\sqlplus\admin\plustrace.sql
grant plustrace to user;

SQL> show parameter mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
optimizer_mode                       string      CHOOSE
SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 A                                         NOT NULL NUMBER
 B                                         NOT NULL VARCHAR2(10)
 C                                                  DATE

注意此处得到的结果是0，因为count(c)是统计c不为空的总和
SQL> select count(c) from t where c is null;

  COUNT(C)
----------
         0

SQL> select count(c) from t where c is not null;

  COUNT(C)
----------
       500

SQL> select index_name from user_indexes ;

INDEX_NAME
------------------------------
IDX_A
IDX_B
IDX_NULL_C
此处A,B，C分别有单列索引
分析表和索引
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.
set autot on
（1）SQL> select count(a) from t;

  COUNT(A)
----------
      1000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'IDX_A' (NON-UNIQUE) (Cost=2 C
          ard=1000)

SQL> select count(b) from t;

  COUNT(B)
----------
      1000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'IDX_A' (NON-UNIQUE) (Cost=2 C
          ard=1000)

SQL> select dump(a),dump(b) from t where rownum<2;

DUMP(A)
-----------------------------------------------------------
DUMP(B)
-----------------------------------------------------------
Typ=2 Len=2: 193,2
Typ=1 Len=10: 116,101,115,116,32,99,111,117,110,116

可以看出A的长度更短，cost最少。优先选择cost最小的索引IDX_A

（2）SQL> select count(c) from t;

  COUNT(C)
----------
       500


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=4000)

可以看到查询count(c)时不走c的单列索引IDX_NULL_C,而A,B列的单列索引显然会得到错误的值1000，所以也不能走，最后选择了全表扫描。
有两种办法可以走c的索引
第一种，建立c和其他非空列的复合索引
SQL> create index idx_c_a on t(a,c);

Index created.
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select count(c) from t ;

  COUNT(C)
----------
       500


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'IDX_C_A' (NON-UNIQUE) (Cost=2
           Card=1000 Bytes=4000)

第2种：建立c列和常量的复合索引，这个方法很巧妙。
SQL> drop index idx_c_a;

Index dropped.

SQL> create index idx_c_i on t(c,0);

Index created.


SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select count(c) from t ;

  COUNT(C)
----------
       500


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'IDX_C_I' (NON-UNIQUE) (Cost=2
           Card=1000 Bytes=4000)

=================================================================
补充
SQL> show parameter mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
optimizer_mode                       string      RULE
SQL> alter system flush shared_pool;

System altered.

SQL> select count(a) from t ;

  COUNT(A)
----------
      1000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T'

此处改为rule的模式后，执行count统计反而没有走索引
SQL> select * from t where a=1;

         A B
---------- ----------------------------------------------------------
C
---------
########## test count
26-OCT-10

########## x
27-OCT-10



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'IDX_A' (NON-UNIQUE)

不计算count可以走索引