在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 _disibledevent=>
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 |
最新评论