创建一张测试表,不创建任何索引
SQL> create table t as select * from dba_objects;
Table created.
SQL> update t set object_id=rownum;
87012 rows updated.
SQL> commit;
Commit complete.
SQL> set timing on
SQL> set linesize 200
SQL> set autotrace on
1. 比较count(*),count(1)和count(object_id)三者的执行计划
count(*),全表扫描,cost:347,逻辑读:1301
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1301 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 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),全表扫描,cost:347,逻辑读:1301
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1301 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
count(object_id) 全表扫描,cost:347,逻辑读:1301
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 866K| 347 (1)| 00:00:05 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1301 consistent gets
0 physical reads
0 redo size
536 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结论1:不创建索引,且object_id没有空值的情况下,三者的执行效率和执行结果是一样的
2. 在object_id上创建索引,再次比较
SQL> create index idx_object_id on t(object_id);
Index created.
count(*),全表扫描,cost:347,逻辑读1302,与不创建索引时并无区别
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
1302 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 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),全表扫描,cost:347,逻辑读1302。和count(*)一样,与不创建索引时没有区别。
注意:这里面1只是一个常量,把1换成5,6,a这些都是等价的。
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1302 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
count(object_id),索引扫描,cost:58,逻辑读:255,说明count(column)使用了索引,效率上有明显提升。
Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 68217 | 866K| 58 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
255 consistent gets
193 physical reads
0 redo size
536 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结论2:在统计字段上创建索引时,count(column)为索引扫描,count(*)与count(1)一致,仍为全表扫描。
3. 将object_id改为非空,再次测试
SQL> alter table t modify object_id not null;
Table altered.
count(*),索引扫描,cost:58,逻辑读:285。说明索引字段改为非空的时候,count(*)也可以走索引,其效果与count(column)一样。
Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 68217 | 58 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
33 recursive calls
0 db block gets
285 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
count(1),索引扫描,cost:58,逻辑读:255,。与count(*)一致。
Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 68217 | 58 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
255 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
count(object_id),索引扫描,cost:58,逻辑读:255。
Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 68217 | 58 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
255 consistent gets
3 physical reads
0 redo size
536 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结论3:当创建索引的目标字段非空时,三者均为索引扫描,执行效率也是一致的。
4. 将object_id改为允许为空,并将部分列更新为空值,比较三者
SQL> update t set object_id=null where rownum<10;
9 rows updated.
SQL> commit;
Commit complete.
count(*),结果:87012,全表扫描
SQL> select count(*) from t;
COUNT(*)
----------
87012
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1302 consistent gets
13 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 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),结果:87012,全表扫描
SQL> select count(1) from t;
COUNT(1)
----------
87012
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68217 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1302 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
count(object_id),结果:87003,索引扫描。与前两者的执行结果就不同了。
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
87003
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 68217 | 866K| 58 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
255 consistent gets
0 physical reads
0 redo size
536 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5. 总结
count(col)统计col列不为空的记录,如果有索引,不管col是否为空,都能走索引,没有索引就无法走。
count(*)和count(1)或者count(常量)没有任何区别,而且都是统计所有行。如果有索引列是非空的,两者都能用到索引,否则依然是全表扫描。
count(col)和count(*)/count(1)根本就不等价,也就无从谈起性能的比较。
附上TOM大师关于count(*)和count(1)的回应:
What is the difference between count(1) and count(*) in a sql query eg. select count(1) from emp; and select count(*) from emp; -- nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.
6. count(*)和count(col)深入探究
创建具有25个字段的测试表
SQL> set serveroutput on
SQL> set echo on
SQL> drop table t;
Table dropped.
SQL> DROP TABLE t;
DROP TABLE t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> DECLARE
2 l_sql VARCHAR2(32767);
3 BEGIN
4 l_sql := 'CREATE TABLE t (';
5 FOR i IN 1..25
6 LOOP
7 l_sql := l_sql || 'n' || i || ' NUMBER,';
8 END LOOP;
9 l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';
10 EXECUTE IMMEDIATE l_sql;
11 END;
12 /
PL/SQL procedure successfully completed.
插入数据
SQL> DECLARE
2 l_sql VARCHAR2(32767);
3 BEGIN
4 l_sql := 'INSERT INTO t SELECT ';
5 FOR i IN 1..25
6 LOOP
7 l_sql := l_sql || '0,';
8 END LOOP;
9 l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';
10 EXECUTE IMMEDIATE l_sql;
11 COMMIT;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')
PL/SQL procedure successfully completed.
SQL> SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T';
NUM_ROWS BLOCKS
---------- ----------
10000 80
统计比较count(*)和count各个列的时间
SQL> DECLARE
2 l_dummy PLS_INTEGER;
3 l_start PLS_INTEGER;
4 l_stop PLS_INTEGER;
5 l_sql VARCHAR2(100);
6 BEGIN
7 l_start := dbms_utility.get_time;
8 FOR j IN 1..1000
9 LOOP
10 EXECUTE IMMEDIATE 'SELECT count(*) FROM t' INTO l_dummy;
11 END LOOP;
12 l_stop := dbms_utility.get_time;
13 dbms_output.put_line((l_stop-l_start)/100);
14
15 FOR i IN 1..25
16 LOOP
17 l_sql := 'SELECT count(n' || i || ') FROM t';
18 l_start := dbms_utility.get_time;
19 FOR j IN 1..1000
20 LOOP
21 EXECUTE IMMEDIATE l_sql INTO l_dummy;
22 END LOOP;
23 l_stop := dbms_utility.get_time;
24 dbms_output.put_line((l_stop-l_start)/100);
25 END LOOP;
26 END;
27 /
.15 # 1000次count(*)的时间
.29 # 1000次count(col1)的时间,可以看到越往后的列,执行的时间也就越长
.33
.31
.38
.49
.35
.41
.39
.43
.46
.47
.46
.51
.54
.56
.56
.64
.66
.69
.71
.72
.74
.79
.82
.83
结论:COUNT(*)最快,COUNT(最大列)最慢
列的偏移量决定了性能,列越靠后,访问的开销就越大。由于count(*)的算法与列的偏移量无关,所以此时count(*)是最快的。