For many years, on Oracle’s forums, there has been an ideological fight: COUNT(*) or COUNT(1)? Each option has its true believers and opponents. Let’s dive into this topic!
Update: The article was updated on 30th October 2015. I added a part regarding the proof that Oracle internally rewrites Count(1) into Count(*).
Is Count(1) faster than Count(*)?
The most common argument used by the group which supports the use of COUNT(1) is the assertion that COUNT(1) is faster than COUNT(*). According to this theory, COUNT(*) takes all columns to count rows and COUNT(1) counts using the first column: Primary Key. Thanks to that, COUNT(1) is able to use index to count rows and it’s much faster. It sounds reasonable, doesn’t it?
Unfortunately, it is simply not true. And it’s time to dispel this widely spread myth.
The general definition of the COUNT function looks like the following:
COUNT (<expression>)
COUNT function counts all of not empty (non-null) occurrences of expression <expression>.
COUNT(1) is nothing other than COUNT(<expression>) where the Expression = 1 => and „1” is obviously not null.
COUNT(*)
There is not too much to explain – it just counts all records in table.
|
1
2
3
4
5
6
7
8
9
|
select count(*),
count(manager_id) ,
count(1),
count(‘banana’)
from employees;
|
| COUNT(*) | COUNT(MANAGER_ID) | COUNT(1) | COUNT(‘BANANA’) |
| 107 | 106 | 107 | 107 |
As you can see count(1), count(‘banana’) and count(*) do the same thing. So maybe we should use COUNT(‘banana’)?
So, what to choose? Count(*) or Count(1)?
If you want to have clear and comprehensible code, COUNT(*) is more appropriate. COUNT(*) at first glance indicates what will be done. Asterix is a typical sign of a meaning „everything” so you can assume that the function counts ALL rows.
As shown by stormy discussions on forums, COUNT(1) is not so obvious. If function COUNTS only the first column or the non-null values or uses index? Opinions are divided and each version has its die-hard followers.
To sum up – if you want to count all the rows in a table, you should use COUNT(*). If you want to count not null occurrences in a table – the best solution is COUNT(<expression>).
Finally, for all the sceptics, the last argument – all of you, no matter what you do, you end up doing COUNT(*) – Oracle internally rewrites COUNT(1) to COUNT(*).
Does Oracle really rewrite Count(1) to Count(*)?
After the initial publication of this article, I was asked about the proof that Oracle internally rewrites Count(1) into Count(*). Follow the steps below and check for yourself. 🙂
1. Create CNT_TEST table
|
1
2
3
|
create table cnt_test as select rownum id, o.* from all_objects o;
alter table cnt_test add constraint cnt_test_pk primary key (id);
|
2. Start TRACE 10053
|
1
|
alter session set events ‘10053 trace name context forever, level 1’;
|
3. Run query
|
1
|
select count(*) cnt_str from all_objects;
|
4. Stop TRACE
|
1
|
alter session set events ‘10053 trace name context off’;
|
5. Search sql id
|
1
|
select sql_id, sql_text from v$sql where sql_text like ‘%cnt_str%’;
|
6. Save TRACE file
|
1
2
3
4
5
6
7
8
|
begin
SYS.DBMS_SQLDIAG.DUMP_TRACE(p_sql_id => <sql_id>
p_child_number => 0,
p_component => ‘Compiler’,
p_file_id => <nazwa pliku>);
end;
|
7. In the TRACE file, search for the string ‘Final query after transformations:’
The result:
|
1
2
|
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) “CNT_STR” FROM “SYS”.“CNT_TEST” “CNT_TEST”
|
Test results:
| Count(*) | select count(*) cnt_str from all_objects; | Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_STR” FROM “SYS”.”CNT_TEST” “CNT_TEST” |
| Count(1) | select count(1) cnt_1 from all_objects; | Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_1” FROM “SYS”.”CNT_TEST” “CNT_TEST” |
| Count(id) – count on Primary Key | select count(id) cnt_id from all_objects; | Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_ID” FROM “SYS”.”CNT_TEST” “CNT_TEST” |
| count(object_id) –count on not NULL column | select count(object_id) cnt_not_null_coll from all_objects; | Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_NOT_NULL_COLL” FROM SYS.”OBJ$” “SYS_ALIAS_16″,SYS.”USER$” |
| Count(OBJECT_TYPE) –count on nullable column | select count(OBJECT_TYPE) cnt_nullable from all_objects; | Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(DECODE(“SYS_ALIAS_17″.”TYPE#”,0,’NEXT OBJECT’,1,’INDEX’,2, |
Last but not least, should you want to read more, here are links to some of the heated Count(*) vs Count(1) debates:
- https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40208915257337
- https://community.oracle.com/thread/613439
- http://www.databaseskill.com/4564328/
- http://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-col
- http://www.dbforums.com/showthread.php?1605397-difference-in-count(*)-and-count(1)
Are you looking for developers experienced in Oracle technologies?
Pretius has a lot of experience with using Oracle technologies in enterprise-grade systems. We also know our way around many different industries. So, if you need custom software tailored to your business needs, just drop us a line at hello@pretius.com (or use the contact form below). We’ll get back to you in 48 hours and tell you what we can do for you.
This post was published originally at my blog, which you can find here how2ora.blogspot.com.
