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

18 comments:

  1. select 1 from dual where exists(select count1
    from users)

    and use this select statement for more time reducing

    ReplyDelete
    Replies
    1. your query will always return 1 , so why you use it?

      Delete
  2. select 1 from dual where exists(select 1
    from users)

    and use this select statement for more time reducing

    ReplyDelete
    Replies
    1. your query will always return 1 , so why you use it?

      Delete
    2. look here
      http://www.oracle-base.com/articles/misc/EfficientSQLStatements.php

      Delete
    3. 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.

      In 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

      Delete
  3. I always use count(*) but after this I will use count(1)

    ReplyDelete
  4. This 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.
    Thanks
    When to use truncate vs delete

    ReplyDelete
  5. So you are saying that it is faster to count one row than all row.

    I could note haver guessed that.

    ReplyDelete
  6. The major problem with count(1) is that it's not cross DBMS.
    Some DBMS will treat 1 as an indexed column.
    You may use instead a primary key.

    ReplyDelete
    Replies
    1. In this blog I post for Oracle DBMS which doesn't treat 1 as indexed column

      Delete
  7. As 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.
    Count() uses a full table scan anyway, so that's were the time consumption goes: it has to go through all table records.

    Edward.

    ReplyDelete
    Replies
    1. 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

      Delete
  8. I've just tested both queries and both run in the same time, after first execution.

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

    ReplyDelete
  9. may your benchmark results be cached values?

    ReplyDelete
    Replies
    1. I also think you should run your queries using a loop because the query cache could influence the results...

      Delete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...