Oracle Scratchpad

January 6, 2015

Count (*)

Filed under: Oracle,Performance — Jonathan Lewis @ 6:04 pm GMT Jan 6,2015

The old chestnut about comparing speeds of count(*), count(1), count(non_null_column) and count(pk_column) has come up in the OTN database forum (at least) twice in the last couple of months. The standard answer is to point out that they will all execute the same code, and that the corroborating evidence for that claim is that, for a long time, the 10053 trace files have had a rubric reporting: CNT – count(col) to count(*) transformation or, for an even longer time, that the error message file (oraus.msg for the English Language version) has had an error code 10122 which produced (from at least Oracle 8i, if not 7.3):


SQL> execute dbms_output.put_line(sqlerrm(-10122))
ORA-10122: Disable transformation of count(col) to count(*)

But the latest repetition of the question prompted me to check whether a more recent version of Oracle had an even more compelling demonstration, and it does. I extracted the following lines from a 10053 trace file generated by 11.2.0.4 (and I know 10gR2 is similar) in response to selecting count(*), count(1) and count({non-null column}) respectively:


Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TEST_USER"."SAVED_ASH" "SAVED_ASH"

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(1)" FROM "TEST_USER"."SAVED_ASH" "SAVED_ASH"

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(SAMPLE_ID)" FROM "TEST_USER"."SAVED_ASH" "SAVED_ASH"

As you can see, Oracle has transformed all three select lists into count(*), hiding the transformation behind the original column alias. As an outsider’s proof of what’s going on, I don’t think you could get a more positive indicator than that.

 

16 Comments »

  1. This AskTom article points out the source of this legend: Oracle 7
    Difference between count(*) and count(1)

    Comment by jkstill — January 6, 2015 @ 6:36 pm GMT Jan 6,2015 | Reply

  2. Hi Jonathan,
    just in addition to. Interestingly the optimizer calculates different CPU costs for count() and count(*). This becomes (especially) relevant in case of query transformations based on the existence of NOT NULL constraints – e.g. like the example in my blog post about query transformation introduction: http://tinyurl.com/mn2nnnz

    Joze also stated that “20 CPU cycles per row (20 is default CPU cost to retrieve a column)” is the arithmetic, but this seems to change between the Oracle versions as i have seen it differently various times for different (patch set) versions: http://tinyurl.com/kthrh9f

    Regards
    Stefan

    Comment by Stefan Koehler — January 6, 2015 @ 7:39 pm GMT Jan 6,2015 | Reply

    • Stefan,

      I think Joze’s point in that posting was that the cost drops by 20 per row – but that’s the cost for each column you have to walk over to get to the column you want.

      The CPU cost of data access has a component per block visit, plus a component per row(-piece) visit, plus a component per column skipped, plus a component for doing whatever has to be done with the column.

      Interestingly, though I’d have to mess about for some time to be certain, it looks as if count(*) assumes the CPU cost of row access to do the counting, and doesn’t allow for the fact that it could just count the rowid entries when the block is clean.

      Comment by Jonathan Lewis — January 9, 2015 @ 11:57 am GMT Jan 9,2015 | Reply

      • Hi Jonathan,
        thanks for your reply. I was aware of what Joze meant (20 CPU cycles per skipped row), but i was not aware that this transformation was also available in 10gR2 – interestingly it is hidden in “Complex View Merging” in 10gR2.

        CBO snippet for 10.2.0.5
        ———-
        CVM: Considering view merge in query block SEL$1 (#0)
        CVM: Converting COUNT(COL1) to COUNT(*).
        ———-

        It makes kind of sense that this “COUNT(COL) to COUNT(*)” transformation is isolated (and converted) from a cost-based to a heuristic-based query transformation in 11g, but maybe my observations were based on some odd (boundary) cases with CVM then – unfortunately i don’t have these cases anymore.

        >> it looks as if count(*) assumes the CPU cost of row access to do the counting, and doesn’t allow for the fact that it could just count the rowid entries when the block is clean.

        Yes, it also looks like that to me by running a quick test case on a table with 500 rows stored in one clean data block. However i assume it would be pretty hard to determine an accurate picture of clean blocks at optimization time.

        Regards
        Stefan

        Comment by Stefan Koehler — January 9, 2015 @ 2:50 pm GMT Jan 9,2015 | Reply

        • Stefan,

          To clarify my costing point – it’s 20 per column skipped per row, not just 20 per row. This could explain why you get different results with different tests; the more columns you skip to get to the column you are counting the higher the CPU cost figure.

          Comment by Jonathan Lewis — January 9, 2015 @ 3:19 pm GMT Jan 9,2015

        • Hi Jonathan,
          thanks for follow up – i know what you meant :-)

          In case of Joze’s example it is 20 per row as only one column is skipped, but in my example on 11.2.0.3.6 (COL1 to COL4) there are 3 skipped columns per row (23563718 – 17563718 = 6000000 (CPU diff) / 100000 (cardinality) = 60). So the results are consistent between Joze’s and my test case here. However i have seen some differences in the past, but i am not quite sure if this was on 10.2.x and maybe related to CVM (and not heuristic) based “COUNT(COL) to COUNT(*)” transformation. Just another reminder for me to archive all oddities i see.

          Thanks.

          Regards
          Stefan

          Comment by Stefan Koehler — January 9, 2015 @ 3:49 pm GMT Jan 9,2015

  3. I am continually amazed at how much time has been wasted on this since rel 7 of Oracle…

    Comment by Noons — January 6, 2015 @ 9:46 pm GMT Jan 6,2015 | Reply

  4. I knew it! Thanks for doing the research and proving what I just got tired of debating and just nodded my head at the well meaning pl/sql developer.

    Comment by jasonbrown264 — January 7, 2015 @ 5:00 pm GMT Jan 7,2015 | Reply

  5. For those who can’t get trace output easily, the trivial way to demonstrate it is to look at the predicates section of the execution plan for something like:

    select * from (select count(1) c from dual) where c = 1

    Comment by William Robertson — January 15, 2015 @ 9:59 am GMT Jan 15,2015 | Reply

  6. It amazes me how many people claim that “count(1)” takes less effort to type, despite the fact that it does not.

    Comment by William Robertson — January 15, 2015 @ 10:03 am GMT Jan 15,2015 | Reply

  7. Jonathan,

    I have checked the 10053 trace output for all three count variation for the count(1) case it has done the transformation but for count(col) it has not done the transformation. My test case is:

    drop table t1 purge;

create table t1
as
select
	rownum	n1
	, mod(rownum, 100) mod1
	, rpad('x', 100, 'x') padding
from
	all_objects
where
	rownum <= 1e4
;

exec dbms_stats.gather_table_stats(user, 't1');

alter session set events '10053 trace name context forever';

select count(*) from t1;
select count(1) from t1;
select count(n1) from t1;

alter session set events '10053 trace name context off';

    Output from trace for count(n1) case

    Stmt: ******* UNPARSED QUERY IS *******
SELECT COUNT("T1"."N1") "COUNT(N1)" FROM "C##JAGDEEP"."T1" "T1"
Objects referenced in the statement
  T1[T1] 99634, type = 1
Objects in the hash table
  Hash table Object 99634, type = 1, ownerid = 2640453171238549381:
    No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("T1"."N1") "COUNT(N1)" FROM "C##JAGDEEP"."T1" "T1"

    I tested this on 12c version
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

    Regards,
    Jagdeep Sangwan

    Comment by jagdeepsangwan — April 20, 2015 @ 1:01 pm BST Apr 20,2015 | Reply

    • Column n1 is not declared as not null, so the transformation does not apply. Test again after issuing:

      alter table t1 modify n1 not null;

      As a side note, the transformation doesn’t apply if you just add a check constraint, the column HAS to be declared non-null. The following won’t do the trick:

      alter table t1 add constraint t1_ck_n1_nn check(n1 is not null);

      Comment by Jonathan Lewis — April 20, 2015 @ 3:37 pm BST Apr 20,2015 | Reply

      • Yes, for not null constraint the transformation is done and as you have mentioned for the check it does not apply.

        Thanks for Clarification

        Comment by jagdeepsangwan — April 21, 2015 @ 6:08 am BST Apr 21,2015 | Reply

  8. […] Proof by 10053 trace […]

    Pingback by Why Bother | Oracle Scratchpad — September 20, 2016 @ 11:17 am BST Sep 20,2016 | Reply

  9. […] I’ve pointed out in the past, in fact more than once, that count(*), count(1), count(declared-non-null-column) will all do the same thing … […]

    Pingback by count(*) – again | Oracle Scratchpad — February 25, 2020 @ 1:24 pm GMT Feb 25,2020 | Reply

  10. […] Count transformation redux (Jan 2015): Looking at the “unparsed” query for confirmation. […]

    Pingback by Optimizer catalogue | Oracle Scratchpad — October 27, 2022 @ 8:46 am BST Oct 27,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.