Oracle里count（1）、count（*）和count（主键）哪个更快

Oracle里count（*）、count（1）和count（主键）到底哪个快的问题。这个问题看起来很简单，每个人都会有自己的答案，去百度上搜会出来一大堆帖子来讲哪个更快。但是说了它们三个其实是一样的，我听到之后也觉得挺诧异的，因为我记得别人跟我说过count（主键）会快，然后自己简单想了一下，觉得好像是那么回事的就没有深入去追究。接着老猫说官方有这样的说法这三个其实是等价的。晚上回来之后到MOS上查了一下，居然被我找到了How the Oracle CBO Chooses a Path for the SELECT COUNT（*） Command （文档 ID 124717.1）。这篇文档讲的就是在CBO优化器模式下，Oracle怎样去评估没有where条件select count（*）和select count（colum）语句的最优路径。1、创建测试表并设计测试场景：--创建测试表
sys@ORCL>create table journal_entries
2 （id_je number（8） ,
3 date_je date not null,
4 balanced number ,
5 constraint indx_ecr_id_je primary key（id_je）
6 ）;

Table created.
--创建索引
sys@ORCL>create index indx_ecr_date_je_balanced on journal_entries（date_je,balanced）;

Index created.

sys@ORCL>create index indx_ecr_balanced_date_je on journal_entries（balanced,date_je）;

Index created.

sys@ORCL>create index indx_ecr_balanced on journal_entries（balanced）;

Index created.
--插入测试数据
sys@ORCL>insert into journal_entries values（1,sysdate,11）;

1 row created.

sys@ORCL>insert into journal_entries values（2,sysdate,21）;

1 row created.

sys@ORCL>insert into journal_entries values（3,sysdate,31）;

1 row created.

sys@ORCL>insert into journal_entries values（4,sysdate,41）;

1 row created.

sys@ORCL>insert into journal_entries values（5,sysdate,51）;

1 row created.

sys@ORCL>insert into journal_entries values（6,sysdate,61）;

1 row created.

sys@ORCL>insert into journal_entries values（7,sysdate,71）;

1 row created.

sys@ORCL>insert into journal_entries values（8,sysdate,81）;

1 row created.

sys@ORCL>insert into journal_entries values（9,sysdate,91）;

1 row created.

sys@ORCL>commit;

Commit complete.
--收集统计信息
sys@ORCL>exec dbms_stats.gather_table_stats（ownname=>USER,tabname=>"JOURNAL_ENTRIES",cascade=>true）;

PL/SQL procedure successfully completed.设计四个场景进行对比：Sel1 : Select count（*） from journal_entries;
Sel2 : Select count（1） from journal_entries;
Sel3 : Select count（id_je） from journal_entries;
Sel4 : Select count（balanced） from journal_entries;1、场景1和场景2等价For CBO, Sel1 and Sel2 are strictly equivalentsys@ORCL>alter session set statistics_level=all;

Session altered.

sys@ORCL>select count（*） from journal_entries;

COUNT（*）
----------
9

sys@ORCL>select * from table（dbms_xplan.display_cursor（null,null,"runstats_last"））;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5ja3ukp4wd73p, child number 0
-------------------------------------
select count（*） from journal_entries

Plan hash value: 42135099

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| INDX_ECR_ID_JE | 1 | 9 | 9 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------

14 rows selected.

sys@ORCL>select count（1） from journal_entries;

COUNT（1）
----------
9

sys@ORCL>select * from table（dbms_xplan.display_cursor（null,null,"runstats_last"））;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gbxjjuqj9j7ww, child number 0
-------------------------------------
select count（1） from journal_entries

Plan hash value: 42135099

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| INDX_ECR_ID_JE | 1 | 9 | 9 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------

14 rows selected.可以看到两个语句的执行计划是完全相同的。2、场景3也与前两个场景等价，因为id_je有NOT NULL约束For Sel3, CBO does the same as for Sel1 and Sel2 since "id_je" has a NOT NULL constraint.sys@ORCL>select count（id_je） from journal_entries;

COUNT（ID_JE）
------------
9

sys@ORCL>select * from table（dbms_xplan.display_cursor（null,null,"runstats_last"））;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b1p4v15dwx7hs, child number 0
-------------------------------------
select count（id_je） from journal_entries

Plan hash value: 42135099

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| INDX_ECR_ID_JE | 1 | 9 | 9 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------

14 rows selected.可以看到执行计划与前两个也是完全相同的。4、场景4跟前边3个不同，因为balanced列上没有NOT NULL约束，但是balanced列上有索引，那会走这个列上的索引么？我们来看一下执行计划：sys@ORCL>select count（balanced） from journal_entries;

COUNT（BALANCED）
---------------
9

sys@ORCL>select * from table（dbms_xplan.display_cursor（null,null,"runstats_last"））;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bc3bc8c0fg14z, child number 0
-------------------------------------
select count（balanced） from journal_entries

Plan hash value: 3638043346

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| INDX_ECR_DATE_JE_BALANCED | 1 | 9 | 9 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------

14 rows selected.我们看到这个执行计划没有走balanced列上的索引，而是走了和date_je的联合索引。这个可以查看另一篇文档：Note:67522.1 Why is my index not used？小结一下：我这里只是简单的从执行计划上看count（*）、count（1）和count（主键）其实是一致，MOS的文档中详细的讲解了Oracle是如何评估执行计划的，也可以使用10053 event查看CBO优化器是如???做出选择的。由于我的功力还不够，对于10053事件还不是很明白，暂时就先不做演示了，要不哪说错了就不好了，这也可以做为以后博客分享的内容。从这个事情上来看，我们对于一件事情应该做一个深入的研究，有充足的证据来证明，尤其是想要在某一方面有深入发展的时候。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址