以前记得在itpub上与别人发生激烈争论,很久以前的事情了.当时争论的焦点是认为count(*),不一定选择主键索引,选择非空的字段,而且索引占用空间最小的索引.
现在写一些例子来说明:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1.建立测试表:
SQL> create table t3 (id1 number,id2 number,name varchar2(20));
Table created.
SQL> create unique index pk_t3 on t3(id1);
Index created.
SQL> alter table t3 add constraint pk_t3 primary key (id1) enable validate;
Table altered.
SQL> insert into t3 select rownum id1,ceil(rownum/1000) id2 ,'test' name from dual connect by level <=10000;
SQL> commit ;
SQL> exec dbms_stats.gather_table_stats(NULL,'T3',Method_Opt=>'FOR ALL COLUMNS SIZE 1');

2.测试1:
SQL>  select /*+ gather_plan_statistics */ count(*) from t3;
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fuynj2z4vdjks, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t3
Plan hash value: 1276951659
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |      25 |
|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |      25 |
|   2 |   INDEX FAST FULL SCAN| PK_T3 |      1 |  10000 |  10000 |00:00:00.01 |      25 |
-----------------------------------------------------------------------------------------

--可以发现使用索引PK_T3,使用INDEX FAST FULL SCAN.如果执行如下:

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;
COUNT(ID2)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0jazcgr65tmq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(id2) from t3
Plan hash value: 463314188
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      31 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      31 |
|   2 |   TABLE ACCESS FULL| T3   |      1 |  10000 |  10000 |00:00:00.01 |      31 |
-------------------------------------------------------------------------------------
--可以发现因为id2不为NULL,只能选择TABLE ACCESS FULL.
--修改id2为非空:

SQL> alter table t3 modify(id2  not null);

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;
COUNT(ID2)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0jazcgr65tmq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(id2) from t3
Plan hash value: 1276951659
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |      25 |
|   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |      25 |
|   2 |   INDEX FAST FULL SCAN| PK_T3 |      1 |  10000 |  10000 |00:00:00.01 |      25 |
-----------------------------------------------------------------------------------------
--可以发现即使count是id2,由于id2是not null,oracle可以选择PK_T3(id1的索引)的INDEX FAST FULL SCAN.

3.测试2:
--修改name为非NULL,建立name索引,选择索引压缩方式.
SQL> alter table t3 modify(name  not null);
SQL> CREATE INDEX i_t3_name ON T3(NAME) COMPRESS 1;
--重新分析表.
SQL> exec dbms_stats.gather_table_stats(NULL,'T3',Method_Opt=>'FOR ALL COLUMNS SIZE 1');
SQL> select index_name,compression,blevel,leaf_blocks from user_indexes where table_name='T3';
INDEX_NAME                     COMPRESS     BLEVEL LEAF_BLOCKS
------------------------------ -------- ---------- -----------
I_T3_NAME                      ENABLED           1          16
PK_T3                          DISABLED          1          18

--可以发现I_T3_name由于重复值很多,索引占用空间相对PK_T3小一点.

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;
COUNT(ID2)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  f0jazcgr65tmq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(id2) from t3

Plan hash value: 1645538918

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |      1 |00:00:00.01 |      22 |
|   1 |  SORT AGGREGATE       |           |      1 |      1 |      1 |00:00:00.01 |      22 |
|   2 |   INDEX FAST FULL SCAN| I_T3_NAME |      1 |  10000 |  10000 |00:00:00.01 |      22 |
---------------------------------------------------------------------------------------------
--可以发现执行选择了i_t3_name索引,因为它占用的空间要比PK_T3.

4.测试3:
--修改id2为NULL,建立位图索引.看看情况如何呢?

SQL> alter table t3 modify(id2 null);
SQL> create bitmap index i_t3_id2 on t3(id2);
SQL> exec dbms_stats.gather_table_stats(NULL,'T3',Method_Opt=>'FOR ALL COLUMNS SIZE 1');

SQL> select index_name,compression,blevel,leaf_blocks from user_indexes where table_name='T3';
INDEX_NAME                     COMPRESS     BLEVEL LEAF_BLOCKS
------------------------------ -------- ---------- -----------
I_T3_ID2                       DISABLED          0           1
I_T3_NAME                      ENABLED           1          16
PK_T3                          DISABLED          1          18

--这时可以发现位图索引i_t3_id2占用空间最小.

SQL> select /*+ gather_plan_statistics */ count(id2) from t3;
COUNT(ID2)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0jazcgr65tmq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(id2) from t3
Plan hash value: 1323983206
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |          |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS |          |      1 |  10000 |  10000 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| I_T3_ID2 |      1 |        |     10 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------
--可以发现位图索引的特殊方式,可以记录NULL值,执行计划可以使用位图索引I_T3_ID2.
--对比如下看看如何?

SQL> select /*+ gather_plan_statistics */ count(*) from t3;
  COUNT(*)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fuynj2z4vdjks, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t3
Plan hash value: 2432646254
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |          |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |          |      1 |  10000 |     10 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| I_T3_ID2 |      1 |        |     10 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------

--咋一看,好像一样的,对比发现Plan hash value不同,仔细看,就能看出不同:
前者第2步执行选择的是BITMAP CONVERSION TO ROWIDS,而后者执行选择的是BITMAP CONVERSION COUNT.
因为前面count(id),是计数id2为非空的数量,也就是扫描位图索引时不包括NULL.
而后面count(*),是计数全部包括空值.说明细节很重要!

5.测试4:
--如果修改id为非NULL
SQL> alter table t3 modify(id2 not null);
SQL> select /*+ gather_plan_statistics */ count(id2) from t3;
COUNT(ID2)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0jazcgr65tmq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(id2) from t3
Plan hash value: 2432646254
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |          |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |          |      1 |  10000 |     10 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| I_T3_ID2 |      1 |        |     10 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------

--可以发现这样就与count(*)的执行计划相同!