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

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



>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 |
—————————————————————————-

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



>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 |
——————————————————————–

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



>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 |
——————————————————————–

1 consistent gets

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



>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 |

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

• 

  看得我莫明其妙：一会说“不管如何加提示，都走不上列上的索引”一会又说“不管字段是否有null，都可以走索引。”

  ttplay @ May 13, 2009 |

• 

  “所以， count(column) ，不管字段是否有null，都可以走索引。”

  差点理解错了.
  但是要说明的, count(column) = count(*) where column is not null;

  如果字段中有null值, 那么count(column)的结果跟count(*)的结果就是不一样的.

  我爱小白 @ May 15, 2009 |

• 

  看得我莫明其妙：一会说“不管如何加提示，都走不上列上的索引”一会又说“不管字段是否有null，都可以走索引。”

  ———————–
  “不管如何加提示，都走不上列上的索引”说的是count(*).
  “不管字段是否有null，都可以走索引。”说的是count(column).

  我爱小白 @ May 15, 2009 |

• 

  都是count（column）
  “不管如何加提示，都走不上列上的索引”说的是9i,column允许为null
  “不管字段是否有null，都可以走索引。”说的是11g 不管column是否允许有null

  kevin zheng @ May 16, 2009 |

• 
  

  http://www.hot-music.net @ December 8, 2009 |

• 

  ｂ（￣▽￣）ｄ（─.─||）

  杭州搬家公司 @ December 8, 2009 |

• 

  (⊙ˍ⊙)＞﹏＜

  杭州搬家 @ December 8, 2009 |

• 

  (⊙ˍ⊙)“不管字段是否有null，都可以走索引。”说的是count(column).

  杭州搬家 @ December 8, 2009 |

• 

  ＞﹏＜(⊙ˍ⊙)

  杭州搬家公司 @ December 8, 2009 |

• 

  ｂ（￣▽￣）ｄ＞﹏＜

  http://www.hzzxdz.cn @ December 8, 2009 |

• 

  (⊙ˍ⊙)＞﹏＜ｂ（￣▽￣）ｄ

  网站建设 @ December 8, 2009 |