oracle中count（1）和count（*）之间的区别

oracle中count（1）和count（*）之间的区别

我相信

count(1)

以前在旧版本的Oracle中会更快。但是到现在为止，我很确定优化器足够聪明，足以知道这一点，

count(*)

并且

count(1)

意味着您需要行数并创建适当的执行计划。

干得好：

create table t as select * from all_objects;Table T created.create index tindx on t( object_name );Index TINDX created.select count(*) from t;  COUNT(*)----------     21534select * from table(dbms_xplan.display_cursor( NULL, NULL, 'allstats last' ));Plan hash value: 2940353011--------------------------------------------------------------------------------------------------| Id  | Operation  | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |--------------------------------------------------------------------------------------------------|   0 | SELECt STATEMENT      |       |      1 |        |      1 |00:00:00.01 |     100 |     93 ||   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |     100 |     93 ||   2 |   INDEX FAST FULL SCAN| TINDX |      1 |  18459 |  21534 |00:00:00.01 |     100 |     93 |--------------------------------------------------------------------------------------------------select count(1) from t;  COUNT(1)----------     21534Plan hash value: 2940353011-----------------------------------------------------------------------------------------| Id  | Operation  | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |       |      1 |        |      1 |00:00:00.01 |     100 ||   1 |  SORT AGGREGATE       |       |      1 |      1 |      1 |00:00:00.01 |     100 ||   2 |   INDEX FAST FULL SCAN| TINDX |      1 |  18459 |  21534 |00:00:00.01 |     100 |-----------------------------------------------------------------------------------------

因此，不仅知道它可以使用索引来优化此查询非常聪明，而且针对不同版本使用了完全相同的执行计划（该计划的价值是相同的）。