05 April, 2012

Ugly count(*)

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)

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
Recommended Post Slide Out For Blogger