oracle中count(1)和count(*)的区别 [英] Difference between count(1) and count(*) in oracle

问题描述

通过多个来源、书籍和通过 asktom 我发现 count(1) 和 count(*) 之间没有区别.我在某处发现 count(1) 比 count(*) 快.但我真的不知道怎么办?据我所知 count(*) 计算 rowid 和 count(1) 将 1 计算为行数.由于 rowid 是 18 位字符，所以需要更多时间吗?据我所知，大小是 2 还是 38 与速度无关.

Through multiple sources, books and through asktom I found there is no difference between count(1) and count(*). somewhere I found count(1) is faster than count(*). But I really dont know how? As for as I know count(*) counts the rowid and count(1) counts the 1 as the number of rows. since rowid is 18 digit character does it take more time? As far as I know whether size is 2 or 38 it doesn't matter for speed.

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

Can anyone of you please clear my doubt.

推荐答案

我相信 count(1) 在旧版本的 Oracle 中速度更快.但是现在，我很确定优化器足够聪明，知道 count(*) 和 count(1) 意味着您想要行数并创建一个适当的执行计划.

I believe count(1) used to be faster in older versions of Oracle. But by now, I'm pretty sure the optimizer is smart enough to know that count(*) and count(1) mean you want the number of rows and creates an appropriate execution plan.

给你:

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 |
-----------------------------------------------------------------------------------------

所以它不仅足够聪明，知道它可以使用索引来优化这个查询，而且它对不同版本使用完全相同的执行计划(计划的值相同).

So not only is it smart enough to know it can use the index to optimize this query, but it uses the exact same execution plan for the different versions (the plan has value is the same).

这篇关于oracle中count(1)和count(*)的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！