Oracle: COUNT(*) vs COUNT(1) – the final battle

30 October 2015, Monika Mitura

After the article “Count(1) or count(*) that is the question” I was asked about the proof that Oracle internally rewrites Count(1) into Count(*).

Follow below steps and check for yourself 🙂

1. Create CNT_TEST table

2. Start TRACE 10053

3. Run query

3. Stop TRACE

4. Search sql id

5. Save TRACE file

6. In TRACE file search for the string ‘Final query after transformations:’
The result:

 

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,

This post was published originally at my blog, which you can find here how2ora.blogspot.com.

Tagged with: , , , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!