oracle count计数的优化

在9i，我们知道count(*)的时候，如果表上有pk，那count(*) 一定会走pk的。count(column) ，如果column指定not null，那count(column)  可以走上索引（通过试验证明，必须还要加上index提示才能走上索引）。但如果列上有空值，不管如何加提示，都走不上列上的索引（组合索引的非引导列除外）

       count是否应该走索引，主要取决于count是否应该把空值算进来。所以， count(column) ，不管字段是否有null，都可以走索引。进而我们可以推论，如果表上存在一not null的字段，而且这个字段上有索引，表上即使没有pk，count（*)也可以通过扫描整个索引完成计数。在11g里面，oracle改进了策略。测试如下：

在9i里面，无论如何写提示，都没办法走上COL_IND索引的。因为col1字段允许为null（虽然实际数据没有null值，分析统计信息也是没用的）
此时count（*),全表扫描：

如我们推论的那样，即使没有PK，存在一个not null的字段，且字段上有索引。count(*)会使用表完成计数。
但如果一个无pk的表上出现多个not null且有索引的字段，那count(*)走哪个索引呢？我们很自然的想到了成本。哪个成本小，走哪个。测试一下：

1 consistent gets

>create table test (col1 varchar2(32),col2 varchar2(32));
 

Table created
>insert into test
2 select id,member_id
3 from b where rownum<=10;
>select * from test;

COL1 COL2
——————————– ——————————–
477234 shenzhenxiechang
291004 shenzhoutouzi
345045 hgyingzi
212170 ntdongyi
493284 ntfashion
200282 rebecca123
1199257 szjinshuipos
629740 nttg
1512060 rich228
772466 nxyk

>create index col_ind on test (col1);
>create index co2_ind on test (col2);
Index created.
Index created.
>select count(col1) from test;

COUNT(COL1)
———–
10

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 4154769577

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | INDEX FULL SCAN| COL_IND | 10 | 70 | 1 (0)| 00:00:01 |
—————————————————————————-

>select count(*) from test;
 

COUNT(*)
———-
10

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 1950795681

——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 18 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 10 | 18 (0)| 00:00:01 |
——————————————————————-

把其中一个字段改为not null：
>alter table test modify(col2 not null);

Table altered.
>desc test
Name Null? Type
———————————————————————————– ——– ——————————————————–
COL1 VARCHAR2(32)
COL2 NOT NULL VARCHAR2(32)

>select count(*) from test;

COUNT(*)
———-
10

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 73288374

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CO2_IND | 10 | 1 (0)| 00:00:01 |
——————————————————————–

>update test set col2=col2||lpad(col2,3500,’a');
 

10 rows updated.
>analyze table test compute statistics for all indexes;

Table analyzed.
update字段前的索引统计信息：
name NUM_ROWS distinct LEAF_BLOCKS cf level alfbpkey
—————————— ———- ———- ———– ———- ———- ———-
CO2_IND 10 10 1 1 0 1
COL_IND 10 10 1 1 0 1
update之后的：
name NUM_ROWS distinct LEAF_BLOCKS cf level alfbpkey
—————————— ———- ———- ———– ———- ———- ———-
CO2_IND 10 10 5 1 1 1
COL_IND 10 10 1 1 0 1

>select count(*) from test;

COUNT(*)
———-
10

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 4154769577

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| COL_IND | 10 | 1 (0)| 00:00:01 |
——————————————————————–

CBO还是根据访问索引的成本选择了成本小的索引进行访问。如果通过index提示，走CO2_IND.
6 consistent gets这个时候，pk对于count(*)来说完全无意义了。完全通过成本决定走哪个索引

注：由于手里没10g的测试环境，不知道这个改进是否在10g就已经有了。

>alter table test add constraint test_pk primary key (col2);
 

Table altered.

>>select count(*) from test;

COUNT(*)
———-
10

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 4154769577

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| COL_IND | 10 | 1 (0)| 00:00:01 |