1.
准备
create table t as select * from dba_objects;
2.
无index
2.1
select (*) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 2197K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------
2.2
select count(object_id) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 2197K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------
总结：
无INDEX的时候，count(*)和count(列)
走全表扫描，是一样的。
但是在消耗的时间的问题上，如果执行次数如够多，就会发现，
count(*)最快，count(列)的执行速度会随着列的偏移位置而越来越慢！
这是因为优化品的cost算法是和列偏移量有关的。列越靠后，性能越低
count(*)和列偏移量无关，所以性能最佳（参见，收获不至sql优化,17章）
3.
有index，但列值没有指定非空，且没有空值的情况
create index idx_t_1 on t(object_id)
3.1
select count(*) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 2197K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------
3.2
select count(object_id) from t
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 2197K| 57 (0)| 00:00:02 |
---------------------------------------------------------------------------------
COUNT(OBJECT_ID)
----------------
196954
4
有index，列没有指定非空，且有空值的情况
update t set object_id = null where rownum < 1000;
4.1
select count(*) from t;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 324 (1)| 00:00:06 |
-------------------------------------------------------------------
COUNT(OBJECT_ID)
----------------
196954
4.2
select count(object_id) from t;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 2197K| 57 (0)| 00:00:02 |
---------------------------------------------------------------------------------
COUNT(OBJECT_ID)
----------------
195955
count(列)会快，但是，可以看到结果两个不一样，少999
count(*)和count(object_id)不等价，所以这种情况要慎重
4.3
在其他非索引，但有空值的列上使用count()
update t set status = null where rownum < 2000;
select count(status) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 845K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------
COUNT(STATUS)
-------------
194958
可以看出结果少了1999，可以看出count(列)，不统计空值
5.
有index，列非空的情况
update t set object_id = rownum;
alter table t modify object_id not null;
5.1
select count(*) from t;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 124 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 124 (0)| 00:00:03 |
-------------------------------------------------------------------------
5.2
select count(object_id) from t;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 124 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 124 (0)| 00:00:03 |
-------------------------------------------------------------------------
可以看出，两种情况一样，都会走索引
5.3
select count(status) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 845K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------
count(列)，列为非索引列时，走全表扫描
总结：
各种情况下，与count(列)与count(*)比较
+--------------------------------------+-----------------------+---------------------------+
| | count(索引列) | count(非索引列) |
+--------------------------------------+-----------------------+---------------------------+
|无索引、无空值 | -------- | 等价，count(*)快，见2.2 |
|无索引、有空值 | -------- | 不等价 |
|有索引、列没指定NOT NULL、无空值| 等价、比count(*)快 | 等价、一样快 |
|有索引、列没指定NOT NULL、有空值| 不等价 | 不等价 |
|有索引、列指定NOT NULL | 等价、一样快 | 等价、count(*)快，见5.3 |
+--------------------------------------+------------------------+---------------------------+
6. 扩展 组合索引，且count(列)为索引前列 drop index idx_t_1; create index idx_t_1 on t(object_id,object_type); 6.1 select count(*) from t; ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 83 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 83 (0)| 00:00:02 | ------------------------------------------------------------------------- 6.2 select count(object_id) from t; ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 83 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 83 (0)| 00:00:02 | ------------------------------------------------------------------------- 6.3 select count(object_type) from t; ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 83 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 83 (0)| 00:00:02 | ------------------------------------------------------------------------- 可以看出，对于索引快速全扫描，索引前列和索引后列，其他没有啥差别 至于相同的查询条件为什么前面的cost为124,后面的为83
因为gather_table_stat收集了下表的统计信息，所以后面cost更准确一些
