count(*), count(1) ,count(唯一键索引),count(非唯一键索引),count(存在null索引)的效率
看见pub上面有人问count(*),count(1)，count(索引字段)有什么区别，效率是否相同，恩 是个好问题下面我来研究研究
SQL> create table test as select * from dba_objects;
表已创建。
SQL> alter table test modify object_id primary key;
表已更改。
SQL> set autot trace
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C005230 | 56783 | 25 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
326 recursive calls
0 db block gets
236 consistent gets
620 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C005230 | 56783 | 25 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
1112 recursive calls
0 db block gets
353 consistent gets
632 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
上面的测试清空了缓存，现在不清空缓存
SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C005230 | 56783 | 25 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
110 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 33512955
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C005230 | 56783 | 25 (4)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
110 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
现在将表的主键去掉
SQL> alter table test drop primary key ;
表已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 140 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 56783 | 140 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
274 recursive calls
0 db block gets
795 consistent gets
699 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 140 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 56783 | 140 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
282 recursive calls
0 db block gets
797 consistent gets
701 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
现在不刷新缓存，多运行几次
SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 140 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 56783 | 140 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(1) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 140 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 56783 | 140 (2)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
存在主键的时候，从统计信息上看,第一次硬解析的时候count(1)效率比count(*)高一点点，但是一旦硬解析之后，再次执行他们的效率是相同的，在没有主键的时候，从统计信息上看，第一次硬解析的时候count(*)的效率比count(1)高点点，再次执行的时候，他们的效率是相同的。由此可以说count(*),count(1)他们的效率是相同的。
现在来测试一下count(索引字段),这个时候分两种情况
1.该字段中有null
2.该字段无null
第一种情况(该字段有null):
SQL> update test set object_name=null where owner='SCOTT';
已更新13行。
SQL> commit;
提交完成。
SQL> create index ind_on on test(object_name);
索引已创建。
SQL> select count(*) from test;
COUNT(*)
----------
49916
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(object_name) from test;
COUNT(OBJECT_NAME)
------------------
49903
执行计划
----------------------------------------------------------
Plan hash value: 1496018862
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 53 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | INDEX FAST FULL SCAN| IND_ON | 56783 | 3659K| 53 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
990 recursive calls
0 db block gets
472 consistent gets
766 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(object_name) from test;
COUNT(OBJECT_NAME)
------------------
49903
执行计划
----------------------------------------------------------
Plan hash value: 1496018862
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 53 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | INDEX FAST FULL SCAN| IND_ON | 56783 | 3659K| 53 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
253 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第二种情况(该字段无null值)
SQL> update test set object_name='DEPT' where owner='SCOTT';
已更新13行。
SQL> commit;
提交完成。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(object_name) from test;
COUNT(OBJECT_NAME)
------------------
49916
执行计划
----------------------------------------------------------
Plan hash value: 1496018862
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 53 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | INDEX FAST FULL SCAN| IND_ON | 56783 | 3659K| 53 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
990 recursive calls
0 db block gets
472 consistent gets
759 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(object_name) from test;
COUNT(OBJECT_NAME)
------------------
49916
执行计划
----------------------------------------------------------
Plan hash value: 1496018862
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 53 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | INDEX FAST FULL SCAN| IND_ON | 56783 | 3659K| 53 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
253 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在硬解析的时候，从统计信息上看，有null值时count(索引字段)的花费要比没有null值时高，而软解析的时候他们的效率是一样的。另外需要注意的就是count(索引字段),存在null值的时候，count不会统计null值，因为ORACLE不会对索引列上包含null值的行进行索引，这点需要特别注意。
下面比较count(唯一键索引)与非唯一键索引的效率
SQL> create unique index ind_oid on test(object_id);
索引已创建。
SQL> set autot on
SQL> select count(object_id) from test;
COUNT(OBJECT_ID)
----------------
49916
执行计划
----------------------------------------------------------
Plan hash value: 886325522
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IND_OID | 56783 | 720K| 25 (4)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
187 consistent gets
109 physical reads
0 redo size
422 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index ind_oid;
索引已删除。
SQL> create index ind_oid on test(object_id);
索引已创建。
SQL> select count(object_id) from test;
COUNT(OBJECT_ID)
----------------
49916
执行计划
----------------------------------------------------------
Plan hash value: 886325522
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 26 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IND_OID | 56783 | 720K| 26 (4)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
36 recursive calls
0 db block gets
197 consistent gets
110 physical reads
0 redo size
422 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由此可见count(唯一键索引的效率)比count(非唯一键)效率高。
总结:
当表存在主键或者唯一索引的时候count(*),count(1)效率相同。count(唯一键索引字段)比count(非唯一键索引字段)效率高。
count(非唯一键索引，列存在null)与count(非唯一键索引，列不存在null)的效率差不多，不过count(唯一键索引，列存在null)的值比count(*)小，因为null的值不会被记录在索引中。
注意:我这里所指的效率主要是指的cost，经过多次执行后的cost。
