Count(*)

Jump to update for 23.3

A recent posting on the comp.databases.oracle.server newsgroup pointed me to a rather elderly Ask Tom question (originally posed in July 2004, last updated June 2011) where Tom produced an extraordinary observation. The response times for the following two queries are completely different (on Oracle 9.2 on his data set):

rem
rem     Script:         count_union.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2012
rem

set autotrace traceonly explain

select count(*)
from   ( select null from big_table
         union all
         select null from big_table
       )
/

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=8 Card=8000000)
   3    2       UNION-ALL
   4    3         INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)
   5    3         INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)

select sum(cnt )
from   ( select count(*) cnt from big_table
         union all
         select count(*) cnt from big_table
       )
/

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=8 Card=2 Bytes=26)
   3    2       UNION-ALL
   4    3         SORT (AGGREGATE)
   5    4           INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)
   6    3         SORT (AGGREGATE)
   7    6           INDEX (FAST FULL SCAN) OF 'FOO' (NON-UNIQUE) (Cost=4 Card=4000000)

The second query took 14 seconds compared to 52 seconds for the first query.
At first my response was stark disbelief – but Tom had, of course, provided the evidence and after a couple of minutes thought I realised why the difference has appeared. Before explaining what’s happened, I’ll just reproduce the (slightly stripped) tkprof outputs that Tom showed us.

select count(*)
  from ( select null from big_table
         union all
         select null from big_table )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     46.32      52.72      44494      44630          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     46.32      52.72      44494      44630          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=44630 r=44494 w=0 time=52726468 us)
8000000   VIEW  (cr=44630 r=44494 w=0 time=46390678 us)
8000000    UNION-ALL  (cr=44630 r=44494 w=0 time=33556917 us)
4000000     INDEX FAST FULL SCAN FOO (cr=22315 r=22247 w=0 time=4464467 us)(object id 128577)
4000000     INDEX FAST FULL SCAN FOO (cr=22315 r=22247 w=0 time=3447898 us)(object id 128577)
********************************************************************************

select sum(cnt )
  from ( select count(*) cnt from big_table
         union all
         select count(*) cnt from big_table )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     12.31      14.17      44494      44630          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     12.31      14.17      44494      44630          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=44630 r=44494 w=0 time=14174206 us)
      2   VIEW  (cr=44630 r=44494 w=0 time=14174178 us)
      2    UNION-ALL  (cr=44630 r=44494 w=0 time=14174171 us)
      1     SORT AGGREGATE (cr=22315 r=22247 w=0 time=7594295 us)
4000000      INDEX FAST FULL SCAN OBJ#(128577) (cr=22315 r=22247 w=0 time=3969944 us)(object id 128577)
      1     SORT AGGREGATE (cr=22315 r=22247 w=0 time=6579839 us)
4000000      INDEX FAST FULL SCAN OBJ#(128577) (cr=22315 r=22247 w=0 time=3545406 us)(object id 128577)

Update – 6th March 2012

The comments from Tanel cover most of the interesting information in this example – but I’m going to write up an answer anyway since there are a couple of related points worth mentioning. First, the sort in the sort aggregate lines is not actually doing any sorting, Oracle is capable of keeping a simple running count in this particular case. In fact, even in cases where there is a genuine aggregation (multi-row result) the volume of data that goes into the sort area is related to the number of different values aggregated rather than the number of rows input – so the amount of memory used for a sort aggregate operation can be very small compared to the volume of data processed. (See this note for an example of how Oracle can handle one type of special case involving sorting)

As far as timing is concerned – the sort aggregate operator has to count the input from its child operation – and in the first case the input for the count is the output of the view, so the view has to be accumulating 8 million rows of “something” from its child operation, which is the union all, and the union all is getting 4 million rows of “something” from each of its two children. The code spends a lot of time sending millions for rows through the “pipelines” that connect operators Doing a quick count – 8 million rows go into the union all pipeline, then 8 million go into the view pipeline, then 8 million go into the sort aggregate pipeline for a total of 24 million rows of “something” being passed.

In the second case the sort aggregate operations at lines 4 and 6 have to accumulate “something” from their respective child operations at lines 5 an 7 – but that’s the limit of data being passed through pipelines – i.e. a total of 8 million rows going into a pipeline. So it’s not too surprising that the amount of CPU used is roughly one third of the CPU used by the first case – we’re passing one third of the rows. (Apart from this simple difference in volume there may even be some special case code that Oracle uses in the second case if it realises that the  operation is simply a variation on “count number of rows in table”.)

Update (Nov 2023)

While writing up a note on a new “push group by into union all” transformation that has appeared in 23c (possibly 21c) I searched for this blog note to check whether or not the transformation would be relevant to the queries in the test. It wasn’t, but I re-ran the test on 19.11.0.0 and 23.3. The difference in time and CPU was still present in 19.11 but the queries completed in virtually the same amount of time in 23.3, so it might be worth doing a few more tests with more realistic code at some time.