对比Oracle中各种count语句的效率和性能 (www.jiedichina.com)

 南京捷帝 www.jiedichina.com

代码:



测试环境windows xp + Oracle 9.2.0.1



在日常工作中，大家经常使用count.通常有以下几种写法：

count(*)

count(1)

count(rowid)

count('X')

count('column_name')

count('PK') --使用索引



这时候大家难免会争论一下哪个效率更高一点，有的人认为count(rowid)的效率更高，也有人持有不同意见。

那么究竟哪个效率更高，下面通过一个简单的实验来示范一下。



备注：请注意测试时使用的Oracle版本，在8以前稍有不同



Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

Connected as study



SQL> create table toms as select * from tomsrep.subs@from_ores1 where region=534 and rownum<100001;



Table created



SQL> alter table toms add constraint pk_toms primary key (oid);



Table altered



分别执行以下几个count语句，注意期间要清除data buffer，以保证数据环境的一致性！



SQL> select count(*) from toms;



  count(*)

----------

    100000



SQL> select count(1) from toms;



  count(1)

----------

    100000



SQL> select count('X') from toms;



count('X')

----------

    100000



SQL> select count(rowid) from toms;



count(ROWID)

------------

      100000





SQL> select count(oid) from toms;



count(OID)

----------

    100000



SQL> select /*+ index(toms PK_TOMS) */ count(*) from toms;



  COUNT(*)

----------

    100000





对比每个count语句的统计信息如下：



select count(*) from toms



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.14       0.23          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.14       1.02       3948       3957          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.28       1.25       3949       3958          0           1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 59  (STUDY)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE

100000   TABLE ACCESS FULL TOMS





select count(1) from toms



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.02          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.12       1.08       3948       3957          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.14       1.10       3949       3958          0           1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 59  (STUDY)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE

100000   TABLE ACCESS FULL TOMS





select count(rowid) from toms



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.00          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.09       1.03       3948       3957          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.10       1.04       3949       3958          0           1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 59  (STUDY)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE

100000   TABLE ACCESS FULL TOMS







select count('X') from toms



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.02          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.17       1.03       3948       3957          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.18       1.05       3949       3958          0           1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 59  (STUDY)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE

100000   TABLE ACCESS FULL TOMS







select count(oid) from toms



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.02          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.20       1.03       3948       3957          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.20       1.05       3949       3958          0           1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 59  (STUDY)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE

100000   TABLE ACCESS FULL TOMS



select /*+ index(toms PK_TOMS) */ count(*) from toms



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.20       0.24          1          1          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.04       0.18        266        266          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.25       0.43        267        267          0           1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 59  (STUDY)



Rows     Row Source Operation

-------  ---------------------------------------------------

      1  SORT AGGREGATE

100000   INDEX FULL SCAN PK_TOMS (object id 34808)





整理如下：



   对比可以看出，前5个count SQL语句的disk + query 都为3949 + 3957，这就保证了这几个SQL的执行环境

是一致的，同时也说明在性能上并没有明显差异



   在一个有主键的表上count是可以只访问索引不访问表的，可以看出disk+query比使用全表扫描小些



   在CPU消耗和总的时间消耗上有点不同：

   count(*)：           cpu=0.28 elapsed=1.25

   count(1)：           cpu=0.14 elapsed=1.10

   count(‘X’)：         cpu=0.18 elapsed=1.05

   count(rowid)：       cpu=0.10 elapsed=1.04

   count(pk)：          cpu=0.20  elapsed=1.05



   count(pk:use index): cpu=0.25 elapsed0.43



   对比一下，可以看出在CPU和总的时间消耗上相差甚微，LIO都相同性能上并没有明显差异。所以,我们可以

大胆的说他们的性能几乎是完全一样的, 大胆的猜测 Oracle 在9I[8i上没测试]以后Oracle在内部把count(*)、

count(1)、count(rowid)、count(pk)都统一转化为count(*)来处理,如果有PK或在有非空约束的字段上存在索引,

count(*)一样可以只访问该索引段得到数据.



   所以大家今后对这种五花八门的count语句，也就不要见怪了，或许是每个人习惯不同而已。



最后：



   在Oracle9i 8i以前，上述测试的结果可能不太相同，所以大家要永远记住一老话：实践是检验真理的唯一标准

 

 南京捷帝 www.jiedichina.com

 

---