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

Z时代
2024-01-10
分类：问答

通过多种资源，书籍和Asktom，我发现count(1)和之间没有区别count(*)。我发现某个地方count(1)比那里快count(*)。但是我真的不知道怎么办？据我所知count(*)计数rowid和count(1)计数1作为行数。既然rowid是18位字符，是否需要更多时间？据我所知，大小是2还是38，速度无关紧要。

你们任何人都可以消除我的疑问。

回答：

我相信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(*)
----------
     21534
select * 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)
----------
     21534
Plan 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 |
-----------------------------------------------------------------------------------------