这个是我一个朋友问我的
CREATE TABLE t (
  id NUMBER,
  n1 NUMBER,
  n2 NUMBER,
  pad VARCHAR2(4000),
  CONSTRAINT t_pk PRIMARY KEY (id)
);

execute dbms_random.seed(0)

INSERT INTO t
SELECT rownum AS id,
       1+mod(rownum,251) AS n1,
       1+mod(rownum,251) AS n2,
       dbms_random.string('p',255) AS pad
FROM dual
CONNECT BY level <= 10000
ORDER BY dbms_random.value;

CREATE INDEX t_n1_i ON t (n1);

CREATE BITMAP INDEX t_n2_i ON t (n2);

SQL> SELECT /*+ index(t t_n1_i) */ count(n1) from t;

Execution Plan
----------------------------------------------------------
SELECT STATEMENT ptimizer=ALL_ROWS (Cost=597 Card=1 Bytes=4)
  SORT (AGGREGATE)
    TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=597 Card=10000 Bytes=40000)
      BITMAP CONVERSION (TO ROWIDS)
        BITMAP INDEX (FULL SCAN) OF 'T_N2_I'

ORACLE 9208竟然不选择N1索引，10g是可以正常使用t_n1_i

偶看了后第一个反应是因为n1列缺乏NOT NULL约束，但是一想不对，我又不是COUNT(*)啊

尝试对N1列添加NOT NULL约束或者为SQL加上 where n1 is not null，都能正常使用索引了，定位在约束问题上

尝试10053，发现CBO根本没有考虑t_n1_i这个索引
尝试RULE+INDEX提示仍然不行

METALINK下，未找到相关的BUG，只找到了一篇讲CBO为COUNT选择路径的文章
How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command [ID 124717.1]

摘录一段：
Sel4 : Select count(balanced) from journal_entries;
balanced is a column without a NOT NULL constraint.
NDX_ECR_BALANCED on column balanced

- CBO will not evaluate index NDX_ECR_BALANCED since "balanced" has no
  NOT NULL constraint.  If you are sure that NDX_ECR_BALANCED is more
  optimal than performing a full table scan on journal_entries, you must
  rewrite your query from select count(balanced) from journal_entries to:
 
    select count(*) from journal_entries
    where balanced > " " or 0 (depending on column type)

  CBO will then use the index.
  Even if you write your select with a hint:
          /*+ INDEX (JOURNAL_ENTRIES NDX_ECR_BALANCED) */
  CBO will not use it as it is not evaluated.

该文档是2002年的，估计就只是针对9I的。10g已经不再去通过约束来判断
而且，在9i中，这不仅仅是CBO的行为，包括RBO。

约束的重要性！！！