I noticed at a lot of application that developers used count(*) in their code repeatably.
I don't encourage any developer to use count(*) as I called it "Ugly count(*)" as If you want to retrieve count of all result set regardless null values then use count(1)
Then run below query against HR schema also
I will try again to query count by primary key (EMPLOYEE_ID)
I will try again to query count by non primary key which have null value
Note : Time of execution may differ in your machine.
Someone may ask question : Why count(*) take time more than count(1)?
The Answer : When I use * in select statement, Oracle internal treat it as Record Type. So it count against composite type and takes more time . While count(1) he counts against scalar type.
Take care that count against column that has null values, He doesn't count null values.
Thanks
Mahmoud A. El-Sayed
I don't encourage any developer to use count(*) as I called it "Ugly count(*)" as If you want to retrieve count of all result set regardless null values then use count(1)
I will explain why not using count(*)
Let's run below query against HR schema
select count(*) from employees;
It returns 108 and takes 73 msec to execute. Then run below query against HR schema also
select count(1) from employees;
It returns 108 and takes 24 msec to execute.
I will try again to query count by primary key (EMPLOYEE_ID)
select count(EMPLOYEE_ID) from employees;
It return 108 and takes 25 msec to execute.
I will try again to query count by non primary key which have null value
select count(COMMISSION_PCT) from employees;
It return 36 and takes 17 msec to execute.
Note : Time of execution may differ in your machine.
Someone may ask question : Why count(*) take time more than count(1)?
The Answer : When I use * in select statement, Oracle internal treat it as Record Type. So it count against composite type and takes more time . While count(1) he counts against scalar type.
Conclusion
Always use count(1) if you need to count in whole result set.Take care that count against column that has null values, He doesn't count null values.
Thanks
Mahmoud A. El-Sayed
select 1 from dual where exists(select count1
ReplyDeletefrom users)
and use this select statement for more time reducing
your query will always return 1 , so why you use it?
Deleteselect 1 from dual where exists(select 1
ReplyDeletefrom users)
and use this select statement for more time reducing
your query will always return 1 , so why you use it?
Deletelook here
Deletehttp://www.oracle-base.com/articles/misc/EfficientSQLStatements.php
Please notice that there is difference between getting counter of rows in table regarding your search criteria and check that data is existed in table.
DeleteIn my post, I get count of whole rows in table regarding my search criteria and in the link you posted he checks existence of data in table
I always use count(*) but after this I will use count(1)
ReplyDeleteThis kind of smaller but fundamental tips helps a lot.We all use count(*) and many times this simple difference can make lot of difference at least on Oracle.
ReplyDeleteThanks
When to use truncate vs delete
So you are saying that it is faster to count one row than all row.
ReplyDeleteI could note haver guessed that.
no.......
DeleteThe major problem with count(1) is that it's not cross DBMS.
ReplyDeleteSome DBMS will treat 1 as an indexed column.
You may use instead a primary key.
In this blog I post for Oracle DBMS which doesn't treat 1 as indexed column
DeleteAs far as I know, count(1) and count(*) are completely equivalent in Oracle. The reason count(1) has been executed faster, in your example, is probably that the result has been cached from the previous example.
ReplyDeleteCount() uses a full table scan anyway, so that's were the time consumption goes: it has to go through all table records.
Edward.
Someone may ask question : Why count(*) take time more than count(1)?
DeleteThe Answer : When I use * in select statement, Oracle internal treat it as Record Type. So it count against composite type and takes more time . While count(1) he counts against scalar type
:)
ReplyDeleteI've just tested both queries and both run in the same time, after first execution.
ReplyDeleteSame time AND same execution plan (that's the important part).
I don't know what is your Oracle version, but I believe this advantage of 'count(1)' is only a myth.
may your benchmark results be cached values?
ReplyDeleteI also think you should run your queries using a loop because the query cache could influence the results...
Delete