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 equivalent
sys@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.