I know COunt(1) is count(*) in disguise but isnt count(1) faster. we've been evaluating all our queries and we've always found that count(1) had a better access time than count(*)

I searched in vain on the net for some info on this. This answer clarified the issue. Thanks..

Hey Arshad,
U r right count(1) is faster than count(*)... I have heard people saying thet count(1) will run through Ur tables' index whereas count(*) doesn't...

Please let me know if I am right or wrong...

select count(1) from a view could potentially be quicker than count(*) provided that the view contains columns from more than 1 table etc. because of the use of temp tsp when doing count(*). So, yes count(1) is a better habbit to form than count(*) and potentially quicker. Its wrong to say that count(*) is always the same as count(1)

The proof is in the pudding
===========================

select count(*) from ABC

COUNT(*)
----------
22766017

Elapsed: 00:00:06.25

Statistics
----------------------------------------------------------
19 recursive calls
401 db block gets
48481 consistent gets
0 physical reads
776 redo size
478 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

select count(1) from ABC

Statistics
----------------------------------------------------------
0 recursive calls
393 db block gets
48479 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Tom --

Just wanted you to know that there are those of us who believe you. Thanks for the clarification.

Great answer Tom!

Less Parse / Exec and CPU time. The point I am trying to make here is its not the same.

>> select count(*) from ABC

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT


Event Elapsed in Statement(%) in Overall(%)
------ ---------- ---------------- --------------
CPU 0.07 100% 37%
Wait 0.00 0% 0%
------ ---------- ---------------- --------------
total 0.07 100% 37%



STATISTICS TOTALS FOR STATEMENT


call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
Parse 2 0.03 0.08 0 53 8 1 0
Exec 2 0.03 0.34 0 0 6 0 0
Fetch 4 0.01 10.26 0 0 0 0 2
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
total 8 0.07 10.68 0 53 14 1 2



EXECUTION PLANs FOR STATEMENT


Parse number : 1
Parsing User ID : 25
Relative starting time : Seconds 41.39
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208


Parse number : 2
Parsing User ID : 25
Relative starting time : Seconds 80.61
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208

=======================================

>> select count(1) from ABC

CPU AND NON-IDLE WAIT TIMES TOTALS FOR STATEMENT


Event Elapsed in Statement(%) in Overall(%)
------ ---------- ---------------- --------------
CPU 0.02 100% 11%
Wait 0.00 0% 0%
------ ---------- ---------------- --------------
total 0.02 100% 11%



STATISTICS TOTALS FOR STATEMENT


call count cpu elapsed disk query current miss rows
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
Parse 2 0.00 0.01 0 2 8 1 0
Exec 2 0.01 0.00 0 0 6 0 0
Fetch 4 0.01 10.62 0 0 0 0 2
Unmap 0 0.00 0.00 0 0 0 0 0
Sort Unmap 0 0.00 0.00 0 0 0 0 0
---------- ------ -------- ---------- ---------- ---------- ---------- ----- ----------
total 8 0.02 10.63 0 2 14 1 2

EXECUTION PLANs FOR STATEMENT

Parse number : 1
Parsing User ID : 25
Relative starting time : Seconds 64.14
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208


Parse number : 2
Parsing User ID : 25
Relative starting time : Seconds 110.57
Recursive depth : 0
Optimizer goal : Choose

Rows Operation Object ID
----- --------------------------------------------------------------- ----------
1 SORT AGGREGATE 0
0 SORT AGGREGATE 0
0 TABLE ACCESS FULL ABC 43,208

I was reading a bug and found this:
problem is in count(col) to count(*) conversion
*** 03/23/00 05:46 pm ***
one workaround is to set event 10122 to turn off count(col) ->count(*)
optimization.
Another work around is to change the count(col) to count(*),
it means the same, when the col has a NOT NULL constraint.
The bug number is 1215372.

To expand on Tom's explanation of why sometimes count(1) is faster than count(*). To truly prove it you need an example and you need to run that example repeatedly. The limits of precision on your experiment overwhelm what you are measuring. You would need to run it many many times in order for the rounding error or randomness to settle down. For example, to test whether a coin is fair or not (fair being 50% of the time it lands heads and 50% of the time it lands tails) you have to flip (test) the coin a lot. (about 1,500 times to be sure with in 3% that it is fair; you can never be 100 % sure)

The only reason I believed COUNT(*) is faster than COUNT(1) is because I read it in 'Oracle Performance Tuning', second edition, O'Reilly & Associates, Inc, page 175. It says:

"Contrary to popular belief COUNT(*) is faster than COUNT(1). If the rows are returned via an index, counting the indexed column - for example, COUNT(EMP_NO) is faster still. The optimizer realizes from the existence of the index that the column must also exist (non-null). We tested the following statements on several different computers and found that COUNT(*) consistently runs between 15% and 20% faster than COUNT(1) and that COUNT(INDEXED_COLUMN) is 5% faster again."

Doesn't seem so true to me anymore.

Tom provides the answer as usual, backed up in spectacular form with concrete examples. How people can question it with "hearsay" evidence is beyond me. These are the same people that still believe explicit cursors are faster because of the single fetch - and how many times have we seen Tom prove us wrong on that ! The point is this - whatever you believed to be true in Oracle 7 with respect to the finer points of SQL tuning has somewhat changed in 8i. The database evolves and access methods improve, and if you don't keep up with it, your standard practices quickly become outdated and in cases, obsolete.

Hi Tom,

It is not about count(1), count(*)...
1. Is
select count(1) from (select....)
always quicker than just the
(select ....)
?

2. During performance tuning, is it a good idea to compare
the 2
select count(1) from (select....)
or would it skew the data and would not give you the correct comparison between the 2
(select ...)?

Thanks!

John

Thanks Tom! This question has bothered me for quite a while. I could not seem to pin down the answer. Good to know that I can always come here for consultation!

John

I'll only believe in count(1) if it will give me atleast 10-15% less I/O. If not, then what's the point ? it might just confused other developer's/dba's not familiar with it.

Readability is important if you have tons of packages/scripts to maintain.

For starters, optimizer is free to tranform any query into a semantically equivalent one. The issue is not about performance, then: we are arguing about what form is more "intuitive" only.

Suppose we have

table A
x y
- -
1 1
1 2
1 2

then how is that count card[(1,1),(1,2),(1,2)]=3, or card[1,1,2]=3 more "intuitively evident" than card[1,1,1]=3 ?
For math challenged, the sql translation of the above formulas is

select count(*) from A
select count(y) from A
select count(1) from A

correspondingly. I like the last form because I could similarly write

select sum(1) from A

which is much less ambiguous.

I thought that '*' could be always substituted with list of columns in any expression. But you are right, it can't:

select count(*) from (
select null a from dual
)

Now, that's intuitive!

Tom is always right.

create table temp1(slno number, name varchar2(10), code varchar2(3));

insert into temp1(slno, name, code) values (1, '','1');
insert into temp1(slno, name, code) values (2, 'XXXX','1');
insert into temp1(slno, name, code) values (3, 'RRRR','1');
insert into temp1(slno, name, code) values (2, '','9');

scott@ORCL> select count(1) from temp1;

COUNT(1)
----------
4

scott@ORCL> select count(*) from temp1;

COUNT(*)
----------
4

scott@ORCL> select count(name) from temp1;

COUNT(NAME)
-----------
2

>> Now, that's intuitive!

It doesn't have to be intuitive if it's documented :)

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions26a.htm#SQLRF00624 <code>

"If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr."

I've found it! Absolute proof that count(*) is faster than count(1).

sql->alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.10
sql->select count(1) from dual;

COUNT(1)
----------
1

1 row selected.

Elapsed: 00:00:00.40
sql->select count(*) from dual;

COUNT(*)
----------
1

1 row selected.

Elapsed: 00:00:00.40

-- TKPROF:
select count(1)
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=87 us)
1 TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=71 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.11 3.11
********************************************************************************

select count(*)
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=94 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=79 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 2.30 2.30


--------------------------------------------------------------------------------
count(1) shows "SQL*Net message from client" = 3.11
count(*) shows "SQL*Net message from client" = 2.30

Research into the time problem led first to a comparison of the statements.
Careful examination revealed a subtle difference: a one byte discrepancy at
offset 14 of the SQL statement ( "*" vs. "1" ). Realizing this difference,
I was able to form two separate cases and document execution steps for each.

case 1: count(*)
----------------
1) left pinky holds down left shift key
2) right middle finger hits "("
3) right middle finger hits "*"
4) right ring finger hits ")"
Note that steps 1 and 2 are very nearly simultaneous, as are steps 3 and 4,
due to parallelism between the two hands. The majority of the time spent
in this case is the movement of right middle finger between steps 2 and 3

case 2: count(1)
----------------
1) left pinky holds down left shift key
2) right middle finger hits "("
3) left pinky releases left shift
4) left ring finger hits "1"
5) left pinky holds down left shift key
6) right ring finger hits ")"
Note that steps 1 and 2 are very nearly simultaneous, as are steps 5 and 6.
The majority of the time spent in this case is steps 3, 4, and 5.

Analysis: case 1 has 33% FEWER steps than case 2. Even with pipelining involved
(using both hands at once), case 2 must still perform more work to accomplish
what Oracle will ultimately consider the same thing. This may be defeated,
however, by clever use of the copy/paste subsystem of the operating system.
Using high-speed overhead keyboard cameras may reveal further important
information.

To ensure this was a reproducible case, I performed it on multiple machines
with similar findings. I furthermore discovered that both cases suffered
reduced performance after drinking a few beers.

The point is, my argument is equally as valid as some of the arguments I've
seen posted in this thread: utterly ridiculous and inconclusive. Unless one of
you is an Oracle engineer responsible for the code that actually interprets and
optimizes count(*), then you really can't argue that count(1) is faster than
count(*). You must provide irrefutable conclusive evidence to prove your case
that one is faster than the other, and that case must resist the test of this
peer community.

At least with cursor_sharing=force,
count(*) is faster then count(1) ...

dellera@ORACLE9I> declare
2 l_stmt varchar2(32767) default 'select ';
3 l_dummy number;
4 begin
5 for i in 1..3000 loop
6 l_stmt := l_stmt || 'count(*)+';
7 end loop;
8 l_stmt := l_stmt || '0 from dual';
9 execute immediate l_stmt into l_dummy;
10 end;
11 /

Same as before, with count(1) instead of count(*):

1 declare
2 l_stmt varchar2(32767) default 'select ';
3 l_dummy number;
4 begin
5 for i in 1..3000 loop
6 l_stmt := l_stmt || 'count(1)+';
7 end loop;
8 l_stmt := l_stmt || '0 from dual';
9 execute immediate l_stmt into l_dummy;
10* end;

********************************************************************************
select count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)

(snip)

+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)+count(*)
+:"SYS_B_0"
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.10 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.05 0.10 0 3 0 1

********************************************************************************
select count(:"SYS_B_0000")+count(:"SYS_B_0001")+count(:"SYS_B_0002")

(snip)

+count(:"SYS_B_2997")+count(:"SYS_B_2998")+count(:"SYS_B_2999")
+:"SYS_B_3000"
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.08 2.16 0 0 0 0
Execute 1 0.29 0.57 0 0 0 0
Fetch 1 0.01 0.02 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.38 2.75 0 3 0 1

The increase in parse cpu has to be obviously expected,
the increase in execute cpu too (more binds),
the increase in fetch cpu is probably due to the need to check for :SYS_* nullity.

Don't know which is the lesser evil between count(1) and cursor_sharing=force, anyway ;)

Hi Tom,
I am one of your biggest fan. I have tested this on Oracle 10g where I pumped 154154 rows into test_emp table. And I got difference, count(1) is still faster than count(*).
I always think you are right. Please correct me if I am wrong / Explain why its behaving in such a way.

drop table t2;
create table t2 as select * from test_emp;
create or replace view v_test
as
select t2.*
from t1, t2
where t2.empno = t1.col_number;

alter system flush shared_pool;
select count(*) from v_test;
alter system flush shared_pool;
select count(1) from v_test;


‘ora10g’alter system flush shared_pool;

System altered.

‘ora10g’select count(*) from v_test;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=699 Card=1 Bytes=2
6)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=699 Card=98775 Bytes=2568150)
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=62 Card=9877
5 Bytes=1284075)

4 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=215 Card=139
650 Bytes=1815450)





Statistics
----------------------------------------------------------
1603 recursive calls
0 db block gets
1795 consistent gets
1773 physical reads
0 redo size
379 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
35 sorts (memory)
0 sorts (disk)
1 rows processed

‘ora10g’alter system flush shared_pool;

System altered.

‘ora10g’select count(1) from v_test;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=699 Card=1 Bytes=2
6)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=699 Card=98775 Bytes=2568150)
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=62 Card=9877
5 Bytes=1284075)

4 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=215 Card=139
650 Bytes=1815450)





Statistics
----------------------------------------------------------
1068 recursive calls
0 db block gets
1688 consistent gets
306 physical reads
0 redo size
379 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed

Regards,

Pritesh.

Tom,
Which is faster
1)select count(*) from abc ;
2)select count(rowid) from abc ;

Count(*) or Count(Rowid) to get the total num of rows from a table ,or which could be used in any sub select.

Just reframmed the question in view of the reader comment above.

count(2.5)
count(2.71828)
count(3.1415)

The argument for count is treated differently from the argument for sum(), max(), avg(), etc. If this doesn't raise a red flag for you, then something is wrong with your critical thinking.

An "intutive" count() argument usages that I've seen in database world (which is BTW wider than oracle) are:

1. Predicate as an argument

count(sal>1000) which is a shorthand for

select count from (
select * from emp where sal > 1000
)

Note how nicely it encompases count(sal not null)

2. Group by clause

count(distinct sal) which is the same as count(group by sal)

which is abbreviation for

select count from (
select distinct sal from emp
)

and

select count from (
select sal from emp group by sal
)

respectively.


This kind of argument can be added as a second parameter to sum, max and the other aggregate functions, of course. Then,

select sum(sal, sal>500) from emp

becomes abbreviation for

select sum(sal) from (
select sal from emp where sal > 500
)

Again we are talking elementary consistency here.

</code> http://www.dbasupport.com/forums/showthread.php?s=&threadid=40518 <code>
reveals that:

- count(9) is fastest for one fingered typists

- count(1) gets conveted to count(*) anyway

The projection column has been added to the plan_table in 10g only and the post in the exchange above is dated Dec 2003.

select count(1) from sys.obj$;
-------------^

OPERATION OBJECT NAME PROJECTION
--------- ----------- ----------
SORT (#keys=0) COUNT(*)[22]
------------------------------------------^
INDEX I_OBJ1

Apparently

count(2+3) is converted to count(5), not to count(*)

and

count(-1)

is not converted at all...

hi Tom,
I also believed that count(1) is faster, and always used it..
But after reading this topic i came to know that there are no differences.
Where this rumours comes that count(1) is faster
than count(*)?

"Where this rumours comes that count(1) is faster than count(*)?"

I've wondered if it is descendant the myth that says:
"where exists (select 1 from . . . . " OBVIOUSLY MUST be faster than
"where exists (select * from . . . . "
Well it MUST BE because you have to retrieve the WHOLE ROW in the second case. (Removes tongue from cheek.)

The real difference comes down to typing again! NULL is 3 chars longer and * moves all over the keyboard depending on the langauge :)

Answer: as tom puts it
COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)COUNT(*)

Wouldn't count(*)count(*)count(*)... take more time because it has to read the row several times? Just kidding!

A DBA, mad as a bat,
After donning his best tin foil hat,
Cleared out the shared pool
And his profiling tool
Showed count one outperforming count splat.

(count(*) pronounced "count splat" by analogy with "rm -rf *", pronounced "rum ruff splat".)

Hello Tom,
Not wishing to be a pedant or such, but you state:

"I prefer where exists (select null from .... ) for semantic reasons..."

But... NULL can be defined as 'the absence of a value', and as such I don't believe "where exists (select the absence of a value from...)" cuts it semantically ;o)

I think we should always be dealing with values here, whether existentially or not.

Yes Tom, I
appreciate that exists is a set operation, but that's not particularly relevant here, and I still hold to the thrust of my point -- that it is not wholly sensible to be checking for the absence of values/sets, i.e., in addition to being counterintuitive, it is somewhat illogical semantically.

OK, we'll agree (to disagree), because I very much dislike:

'...where [not] exists (select null from...)'

:o)

As of 11g, there's a great reason to always use COUNT(*).

Try this:

asmith@DEV> create table t1 (c1 number);

Table created.

asmith@DEV> create view v1 as
  2  select count(1) count from t1;

View created.

asmith@DEV> create view v2 as 
  2  select count(*) count from t1;

View created.

asmith@DEV> select object_name, object_type, status
  2  from user_objects
  3  where (object_name in ('V1','V2'))
  4  and object_type = 'VIEW';

OBJECT_NAME
----------------------------
OBJECT_TYPE         STATUS
------------------- -------
V1
VIEW                VALID

V2
VIEW                VALID


asmith@DEV> alter table t1 add (c2 number);

Table altered.

asmith@DEV> select object_name, object_type, status
  2  from user_objects
  3  where (object_name in ('V1','V2'))
  4  and object_type = 'VIEW';

OBJECT_NAME
----------------------------
OBJECT_TYPE         STATUS
------------------- -------
V1
VIEW                INVALID

V2
VIEW                VALID

A view using count(*) on a table will remain valid when columns are added to the table.

See:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/dependencies.htm#insertedID4

I was working on a query, trying to understand what I was told about SUM was supposed to be faster than count.  (still not sure about that - for some strange reason, using DECODE and SUM was exponentially faster than a union and a condition in a WHERE clause.)...but back to the experiment.  

So I did a SELECT TABLE_NAME, NUM_ROWS from DBA_TABLES, and took the largest table  - approx. 15.5 Million rows.  
(I was pulling from my Mainframe days and using large volumes of data to measure speed.)

So I tried Selecting COUNT(*), COUNT(1) and SUM(1)...and here are my results:
 
20:21:12 USRT@PROD> select count(*) from ES_LOG;

  COUNT(*)
----------
  15544308

Elapsed: 00:00:12.01
20:22:36 USRT@PROD> select count(1) from ES_LOG;

  COUNT(1)
----------
  15544308

Elapsed: 00:00:01.07
20:22:51 USRT@PROD> select sum(1) from  ES_LOG;

    SUM(1)
----------
  15544308

Elapsed: 00:00:03.01
20:23:10 USRT@PROD>   

BUT when I went back and did the Select count(*) again and count(1), they came back with the same times.  

is this some kind of buffer issue? 

I vaguely remember in the early Version 6 (or maybe V5) days, that
select count(*) from t@dblink;
would pull all the data from a remote database to the local one to be counted. So one optimization was to do a count(1) instead to cut down on the sqlnet traffic. I think Version 7 was when the count() function would also be passed over the dblink.
.
But then again even all that may have been a myth.

for select count(1) statement, how can we do it with SQLDA?

e.g. to select total row count for a table with table name entered by user during runtime.

Tom,
Is there are difference between count(*) and count(1) in terms of the amount of memory used.
Does count(*) fetches the whole row into memory and count(1) does not?
Thanks

I did not think that it needs to read the whole row into memory.
I heard it from a co-worker who says that count(1) is better than count(*).
I can see in the statistics the sorts in memory but have no idea how to prove that memory consumption does not play a part in this.
Thanks,

Hi Tom

I delete a single row, using the 'RETURNING' clause to store the count of rows affected in the 'delcount' variable:

Count(*) tells me no rows were deleted
Count(1) tells me correctly that 1 row was deleted.

Environment : SQL*Plus: Release 10.2.0.2.0, Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

set serveroutput on
set verify 1

variable delcount number;

create table mytable (col1 varchar(1));
insert into mytable values ('a');
commit;

delete mytable returning count (*) into :delcount;
exec dbms_output.put_line ('COUNT (*) RETURNS ' ||:delcount);

rollback;

delete mytable returning count (1) into :delcount;
exec dbms_output.put_line ('COUNT (1) RETURNS ' ||:delcount);

rollback;

Is this an Oracle bug or can you explain what I'm missing?

Thanks!

Thanks Tom.

I'll raise a bug report. I'm a freelancer at a large bank.
My access to Metalink is coming, but its not going go be quick...

Paul

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> set serveroutput on
SQL> set verify 1
SP2-0265: verify must be set ON or OFF
SQL>
SQL> variable delcount number;
SQL>
SQL> create table mytable (col1 varchar(1));

Table created.

SQL> insert into mytable values ('a');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> delete mytable returning count (*) into :delcount;
delete mytable returning count (*) into :delcount
                         *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> exec dbms_output.put_line ('COUNT (*) RETURNS ' ||:delcount);
COUNT (*) RETURNS

PL/SQL procedure successfully completed.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> delete mytable returning count (1) into :delcount;
delete mytable returning count (1) into :delcount
                         *
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> exec dbms_output.put_line ('COUNT (1) RETURNS ' ||:delcount);
COUNT (1) RETURNS

PL/SQL procedure successfully completed.

SQL>
SQL> rollback;

Rollback complete.

I'ven't run the trace, but I'm thinking that with this bug, count(*) is still faster than count(1). Wrong, but faster. I feel better, don't you? :)

SR Submitted Date 18-Aug-2009 09:31:40 GMT
Product Version 10.2.0.2.0
Severity 3
Brief Problem Statement Count(*) incorrect; Count(1) correct -- rows deleted

I cannot believe, that there's a bug using select count(*) or select count(1) in the returning clause.....

Actually, count(1) is faster than count(*) on an oracle db.
In mysql, it is the same and probably mssql too.

drop table t;

create table t as select * from all_objects;
exec dbms_stats.gather_table_stats( user, 'T' );

@trace
declare
    l_n number;
begin
for i in 1 .. 100
loop
    select count(*) into l_n from t;
    select count(1) into l_n from t;
end loop;
end;
/
disconnect
connect /
@trace
declare
    l_n number;
begin
for i in 1 .. 100
loop
    select count(*) into l_n from t;
    select count(1) into l_n from t;
end loop;
end;
/
@tk "sys=no"

SELECT COUNT(*) FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100      0.72       0.74          0      69800          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.72       0.74          0      69800          0         100
********************************************************************************
SELECT COUNT(1) FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100      0.72       0.72          0      69800          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.72       0.73          0      69800          0         100

In my opinion, making the claim -- without any demonstration or proof -- that "X is faster than Y" verges perilously on the unprofessional. Especially when previous demonstrations on this thread have shown the opposite. If the results were different when you tried it, show what you observed -- quantitatively.

As for the SR on RETURNING count(*) or count(1), Oracle support has created BUG 8827474.

Bug updated to say it's the same as Bug No. 5869609, Filed 07-FEB-2007

Tom,

How does the select count(*) work on a range partitioned daily?

Is the count taken from NUM_ROWS from user_tab_partitions?

As Tom said, NUM_ROWS just an estimate,not an actual. And caching affects your perceived performance (that's why there's a cache!)

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
 select sum(num_rows)
 from user_tab_partitions
 where table_name = 'POLICY_AND_QUOTE';

SUM(NUM_ROWS)
-------------
   39,826,281 -- just an estimate

SQL> set serveroutput off

Session altered.

SQL> set timing on
SQL> select /*+ gather_plan_statistics */ count(*) from policy_and_quote;

  COUNT(*)
----------
39,884,587  -- the actuality

Elapsed: 00:00:30.66 -- wow, 30 seconds for count(*) !!!!

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------
|Id|Operation                   |Name      |Starts |E-Rows|A-Rows|   A-Time   |Buffers|
---------------------------------------------------------------------------------------
| 1|SORT AGGREGATE              |          |     1 |    1 |    1 |00:00:29.77 |  1574 |
| 2|PARTITION RANGE ALL         |          |     1 |  39M |21520 |00:00:33.43 |  1574 |
| 3|BITMAP CONVERSION COUNT     |          |   301 |  39M |21520 |00:00:30.27 |  1574 |
| 4| BITMAP INDEX FAST FULL SCAN|PQ_SRC_BIT|   301 |      |21520 |00:00:29.73 |  1574 |

SQL> select /*+ gather_plan_statistics */ count(1) from policy_and_quote;

  COUNT(1)
----------
39,884,587

Elapsed: 00:00:00.15 -- so much faster: less than a second !!

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------
|Id|Operation                   |Name      |Starts |E-Rows|A-Rows|   A-Time   |Buffers|
---------------------------------------------------------------------------------------
| 1|SORT AGGREGATE              |          |     1 |    1 |    1 |00:00:00.09 |  1574 |
| 2|PARTITION RANGE ALL         |          |     1 |  39M |21520 |00:00:00.13 |  1574 |
| 3|BITMAP CONVERSION COUNT     |          |   301 |  39M |21520 |00:00:00.61 |  1574 |
| 4| BITMAP INDEX FAST FULL SCAN|PQ_SRC_BIT|   301 |      |21520 |00:00:00.08 |  1574 |

SQL> select /*+ gather_plan_statistics */ count(*) from policy_and_quote;

  COUNT(*)
----------
39,884,587

Elapsed: 00:00:00.20  -- but wait, now count(*) is faster too!

---------------------------------------------------------------------------------------
|Id|Operation                   |Name      |Starts |E-Rows|A-Rows|   A-Time   |Buffers|
---------------------------------------------------------------------------------------
| 1|SORT AGGREGATE              |          |     1 |    1 |    1 |00:00:00.08 |  1574 |
| 2|PARTITION RANGE ALL         |          |     1 |  39M |21520 |00:00:00.11 |  1574 |
| 3|BITMAP CONVERSION COUNT     |          |   301 |  39M |21520 |00:00:00.57 |  1574 |
| 4| BITMAP INDEX FAST FULL SCAN|PQ_SRC_BIT|   301 |      |21520 |00:00:00.07 |  1574 |

Amazing what the cache will do for you.

wow.

"May be it is an expected behaviour."
...
"use a column or pseudo-column insted of * "

which proves that count(*) is NOT the same as count(<column>)

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

> which proves that count(*) is NOT the same as count(<column>)

I don't think anyone claimed the two versions were identical. I hope not... they answer fundamentally different questions.

count(*) --> counts complete rows
count(<column>) --> counts non-null occurrences of <column>

Hi Tom,

Please evaluate these queries and enlighten us.


SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 23 16:09:53 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT COUNT(*)  FROM dual WHERE 1 = 2;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*)  FROM dual WHERE 1 = 2 GROUP BY dummy;

no rows selected

SQL> 

does not return any rows. We were expecting 0 (Zero)

Is this an intended behavior?

I wrote a PL/SQL script to be run under SQL*Plus that times SELECT COUNT (*) and SELECT COUNT (1)
queries. The script has the following features:

1) It does a SELECT COUNT (*) and a SELECT COUNT (1) query against a DBA_OBJECTS view that
contains over 68,000 rows.

2) It flushes the BUFFER_CACHE and the SHARED_POOL before each query.

3) It perform 10 sets of the two queries, timing each one down to six decimal places.

4) It alternates the ordering of the two queries, with SELECT COUNT (*) submitted first in odd
sets and SELECT COUNT (1) submitted first in even sets.

Below are the results I received from two runs of the script:

Run #1:

COUNT (*) time: +00 00:00:08.100015
COUNT (1) time: +00 00:00:05.726067
COUNT (1) beat COUNT (*) by +00 00:00:02.373948
COUNT (1) is leading COUNT (*) by +00 00:00:02.373948
COUNT (1) time: +00 00:00:04.690972
COUNT (*) time: +00 00:00:07.283698
COUNT (1) beat COUNT (*) by +00 00:00:02.592726
COUNT (1) is leading COUNT (*) by +00 00:00:04.966674
COUNT (*) time: +00 00:00:09.697947
COUNT (1) time: +00 00:00:08.654737
COUNT (1) beat COUNT (*) by +00 00:00:01.043210
COUNT (1) is leading COUNT (*) by +00 00:00:06.009884
COUNT (1) time: +00 00:00:09.378757
COUNT (*) time: +00 00:00:07.982754
COUNT (*) beat COUNT (1) by +00 00:00:01.396003
COUNT (1) is leading COUNT (*) by +00 00:00:04.613881
COUNT (*) time: +00 00:00:07.393250
COUNT (1) time: +00 00:00:07.618329
COUNT (*) beat COUNT (1) by +00 00:00:00.225079
COUNT (1) is leading COUNT (*) by +00 00:00:04.388802
COUNT (1) time: +00 00:00:07.059480
COUNT (*) time: +00 00:00:05.122013
COUNT (*) beat COUNT (1) by +00 00:00:01.937467
COUNT (1) is leading COUNT (*) by +00 00:00:02.451335
COUNT (*) time: +00 00:00:06.239745
COUNT (1) time: +00 00:00:09.225688
COUNT (*) beat COUNT (1) by +00 00:00:02.985943
COUNT (*) is leading COUNT (1) by +00 00:00:00.534608
COUNT (1) time: +00 00:00:07.194247
COUNT (*) time: +00 00:00:06.923286
COUNT (*) beat COUNT (1) by +00 00:00:00.270961
COUNT (*) is leading COUNT (1) by +00 00:00:00.805569
COUNT (*) time: +00 00:00:06.084537
COUNT (1) time: +00 00:00:07.977888
COUNT (*) beat COUNT (1) by +00 00:00:01.893351
COUNT (*) is leading COUNT (1) by +00 00:00:02.698920
COUNT (1) time: +00 00:00:06.621696
COUNT (*) time: +00 00:00:06.628764
COUNT (1) beat COUNT (*) by +00 00:00:00.007068
COUNT (*) is leading COUNT (1) by +00 00:00:02.691852
Overall COUNT (*) beat COUNT (1) by +00 00:00:02.691852

Run #2:

COUNT (*) time: +00 00:00:08.203430
COUNT (1) time: +00 00:00:07.490769
COUNT (1) beat COUNT (*) by +00 00:00:00.712661
COUNT (1) is leading COUNT (*) by +00 00:00:00.712661
COUNT (1) time: +00 00:00:08.254991
COUNT (*) time: +00 00:00:08.045552
COUNT (*) beat COUNT (1) by +00 00:00:00.209439
COUNT (1) is leading COUNT (*) by +00 00:00:00.503222
COUNT (*) time: +00 00:00:07.211382
COUNT (1) time: +00 00:00:06.782487
COUNT (1) beat COUNT (*) by +00 00:00:00.428895
COUNT (1) is leading COUNT (*) by +00 00:00:00.932117
COUNT (1) time: +00 00:00:05.586959
COUNT (*) time: +00 00:00:07.043175
COUNT (1) beat COUNT (*) by +00 00:00:01.456216
COUNT (1) is leading COUNT (*) by +00 00:00:02.388333
COUNT (*) time: +00 00:00:08.388103
COUNT (1) time: +00 00:00:09.305080
COUNT (*) beat COUNT (1) by +00 00:00:00.916977
COUNT (1) is leading COUNT (*) by +00 00:00:01.471356
COUNT (1) time: +00 00:00:11.971294
COUNT (*) time: +00 00:00:09.546646
COUNT (*) beat COUNT (1) by +00 00:00:02.424648
COUNT (*) is leading COUNT (1) by +00 00:00:00.953292
COUNT (*) time: +00 00:00:07.936489
COUNT (1) time: +00 00:00:06.256901
COUNT (1) beat COUNT (*) by +00 00:00:01.679588
COUNT (1) is leading COUNT (*) by +00 00:00:00.726296
COUNT (1) time: +00 00:00:08.104579
COUNT (*) time: +00 00:00:05.415139
COUNT (*) beat COUNT (1) by +00 00:00:02.689440
COUNT (*) is leading COUNT (1) by +00 00:00:01.963144
COUNT (*) time: +00 00:00:07.566783
COUNT (1) time: +00 00:00:06.008076
COUNT (1) beat COUNT (*) by +00 00:00:01.558707
COUNT (*) is leading COUNT (1) by +00 00:00:00.404437
COUNT (1) time: +00 00:00:04.730734
COUNT (*) time: +00 00:00:07.868103
COUNT (1) beat COUNT (*) by +00 00:00:03.137369
COUNT (1) is leading COUNT (*) by +00 00:00:02.732932
Overall COUNT (1) beat COUNT (*) by +00 00:00:02.732932

About using Count(*) -is explained clearly here
http://bix-erusadla.blogspot.com/

Does the recursive calls mean a slight difference?  10.2.0.4:

SYS> select count(*) from dba_synonyms;

  COUNT(*)
----------
     20197


Execution Plan
----------------------------------------------------------
Plan hash value: 3261027237

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     1 |    20 |   222   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE      |        |     1 |    20 |            |          |
|*  2 |   HASH JOIN          |        | 20204 |   394K|   222   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL | USER$  |    86 |   344 |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS      |        | 20212 |   315K|   219   (2)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| OBJ$   | 20528 |   220K|   216   (1)| 00:00:03 |
|*  6 |     INDEX UNIQUE SCAN| I_SYN1 |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."OWNER#"="U"."USER#")
   5 - filter("O"."TYPE#"=5)
   6 - access("O"."OBJ#"="S"."OBJ#")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      20993  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        488  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SYS>  select count(1) from dba_synonyms;

  COUNT(1)
----------
     20197


Execution Plan
----------------------------------------------------------
Plan hash value: 3261027237

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     1 |    20 |   222   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE      |        |     1 |    20 |            |          |
|*  2 |   HASH JOIN          |        | 20204 |   394K|   222   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL | USER$  |    86 |   344 |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS      |        | 20212 |   315K|   219   (2)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| OBJ$   | 20528 |   220K|   216   (1)| 00:00:03 |
|*  6 |     INDEX UNIQUE SCAN| I_SYN1 |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."OWNER#"="U"."USER#")
   5 - filter("O"."TYPE#"=5)
   6 - access("O"."OBJ#"="S"."OBJ#")


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      20995  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        488  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

It seems consistent on repetition.  I was curious after I saw bug 10294904 (slow all_synonyms on 11.2.0.2)

Tom:

In your very first reply to this thread, you said

It only takes ONE CASE and I'll be wrong (but you'll never find it).

In which case you will be wrong?

Hi Tom,

Here are some queries and there outputs are given ,please look it.
SQL> select count(*) from all_objects;

  COUNT(*)
----------
     49930

Elapsed: 00:00:04.08

SQL> select count(1) from all_objects;

  COUNT(1)
----------
     49930

Elapsed: 00:00:01.94
 
So, Tom please Tell me that can I say that Count(1) is faster/better then count(*), But if you say not then why there is difference in the timing of the above given queries?

select count(*) from all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6     25.52      26.18       3268     133983          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12     25.52      26.18       3268     133983          0           3
********************************************************************************
select count(1) from all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.16       0.17          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6     27.41      27.65       2805     133983          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12     27.57      27.82       2805     133983          0           3

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789



Hi tom,

I have been your fan since long time.
I could not explain following behavior to my colleague, while suggesting him to use Count(*).

SQL> SELECT count(*)
  2  FROM rgis_time_sheet_details rt;

  COUNT(*)
----------
   4196998

SQL> SELECT count(rt.*)
  2  FROM rgis_time_sheet_details rt;
SELECT count(rt.*)
                *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

What can be the difference between count(*) and count(rt.*) ? 

Will count() ever return no_data_found? I have encountered in code the following:

begin
select count(*)
into ...
from ...
exception
when no_data_found then
...
end;

It was my understanding that the exception was unnecessary with count, in that it will ALWAYS return a number. Thanks

ops$tkyte%ORA11GR2> select count(*) from dual where 1=0;

  COUNT(*)
----------
         0

ops$tkyte%ORA11GR2>