总以为count(1) 比count(*)的速度要快，但是事实上经过测试并非我们想象的那样。做测试如下：
yang@rac1>create table yangstar as select * from all_objects;
Table created.
yang@rac1>desc yangstar
Name Null? Type
----------------------------- -------- --------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
yang@rac1>create index yangstar_index on yangstar(object_id);
Index created.
yang@rac1>declare
2 n_count number;
3 n_time0 number;
4 n_time1 number;
5 n_time2 number;
6 n_time3 number;
7 n_time4 number;
8
9 begin
10 execute immediate'analyze table yangstar delete statistics';
11
12 select count(*) into n_count from yangstar;
13
14 n_time0 := dbms_utility.get_time;
15 for i in 1..1000 loop
16 select /*+index(yangstar yangstar_index)*/ count(*) into n_count from yangstar;
17 end loop;
18
19 n_time1 := dbms_utility.get_time;
20 for i in 1..1000 loop
21 select count(*) into n_count from yangstar;
22 end loop;
23 n_time2 := dbms_utility.get_time;
24 for i in 1..1000 loop
25 select count(1) into n_count from yangstar;
26 end loop;
27 n_time3 := dbms_utility.get_time;
28 for i in 1..1000 loop
29 select count(rowid) into n_count from yangstar;
30 end loop;
31 n_time4 := dbms_utility.get_time;
32
33 dbms_output.put_line('the count of the table T : '||to_char(n_count));
34 dbms_output.put_line('before analyze ,loop 1000,/*+hints*/count(*) :'||to_char(n_time1 - n_time0 ));
35 dbms_output.put_line('before analyze ,loop 1000,count(*) :'||to_char(n_time2 - n_time1));
36 dbms_output.put_line('before analyze ,loop 1000,count(1) :'||to_char(n_time3 - n_time2));
37 dbms_output.put_line('before analyze ,loop 1000,count(rowid) :'||to_char(n_time4 - n_time3));
38
39 execute immediate'analyze table yangstar compute statistics';
40
41 select count(*) into n_count from yangstar;
42 n_time1 := dbms_utility.get_time;
43 for i in 1..1000 loop
44 select count(*) into n_count from yangstar;
45 end loop;
46
47 n_time2 := dbms_utility.get_time;
48
49 for i in 1..1000 loop
50 select count(1) into n_count from yangstar;
51 end loop;
52 n_time3 := dbms_utility.get_time;
53 for i in 1..1000 loop
54 select count(rowid) into n_count from yangstar;
55 end loop;
56 n_time4 := dbms_utility.get_time;
57 dbms_output.put_line('the count of the table T : '||to_char(n_count));
58 dbms_output.put_line('after analyze ,loop 1000,count(*) :'||to_char(n_time2 - n_time1));
59 dbms_output.put_line('after analyze ,loop 1000,count(1) :'||to_char(n_time3 - n_time2));
60 dbms_output.put_line('after analyze ,loop 1000,count(rowid) :'||to_char(n_time4 - n_time3));
61 end;
62 /
the count of the table T : 73381
before analyze ,loop 1000,/*+hints*/count(*) :1628
before analyze ,loop 1000,count(*) :1628
before analyze ,loop 1000,count(1) :1453
before analyze ,loop 1000,count(rowid) :1886
the count of the table T : 73381
after analyze ,loop 1000,count(*) :227
after analyze ,loop 1000,count(1) :222
after analyze ,loop 1000,count(rowid) :474
PL/SQL procedure successfully completed.
从结果上看，统计信息前后差别很大，但是纵向来比的话，count(1)和count(*)相差不大，但是与count(rowid)。
下面附上 执行计划：
yang@rac1>select count(*) from yangstar;
Execution Plan
----------------------------------------------------------
Plan hash value: 2603293318
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| YANGSTAR_INDEX | 73381 | 46 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
171 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
yang@rac1>select count(1) from yangstar;
Execution Plan
----------------------------------------------------------
Plan hash value: 2603293318
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| YANGSTAR_INDEX | 73381 | 46 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
171 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
yang@rac1>select count(rowid) from yangstar;
Execution Plan
----------------------------------------------------------
Plan hash value: 2603293318
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 46 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | INDEX FAST FULL SCAN| YANGSTAR_INDEX | 73381 | 859K| 46 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
171 consistent gets
0 physical reads
0 redo size
532 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从执行计划上面也都是一样的。
