Difference between count(1) and count(*) in oracle

  • A+
Category:Languages

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.

 


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.

Here you go:

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

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: