dcsimg
 

Is COUNT(rowid) Faster Than COUNT(*)?

Monday May 18th 2020 by David Fitzjarrell
Is COUNT(rowid) Faster Than COUNT(*)?

It is believed that count(*) expands to all columns and that count(rowid) is faster.  Read on to see why the opposite might be true.

A long-standing discussion in Oracle forumns and newsgroups has been the efficiency of using coun(*) to returnn a rowcount from a given table. A new wrinkle in that discussion now introduces count(rowid) as a more efficient alternative; the argument states that count(*) expands the entire column list, much like "select * ...", and, as such, could be a resource sink when CLOB columns are present in the desired table. Let's look at that argument and see if it holds water. Let's start by creating and populating a table containing a CLOB column:


SQL>
SQL> create table count_test(
  2  id      number,
  3  val     varchar2(40),
  4  clb     clob);

Table created.

SQL>
SQL> begin
  2          for z in 1..1000000 loop
  3             insert into count_test
  4             values(z, 'Record '||z, 'Clob value '||z);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

Next let's set event 10053 to dump the optimizer trace so we can see how Oracle plans to execute the count() queries:


SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

The stage is set, let's run some variants of count() to see how Oracle behaves. First, we'll execute a straight count(*) and display the plan:


SQL> select count(*) from count_test;

  COUNT(*)
----------
   1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(*) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 371675025

----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |  3582 |           |
| 1   |  SORT AGGREGATE     |           |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | COUNT_TEST|  848K |       |  3582 |  00:00:43 |
----------------------------------------+-----------------------------------+      
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>

Looking at the trace file generated Oracle simply uses count(*) as-is to return the results:


Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "BING"."COUNT_TEST" "COUNT_TEST"          
...
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |  3582 |           |
| 1   |  SORT AGGREGATE     |           |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | COUNT_TEST|  848K |       |  3582 |  00:00:43 |
----------------------------------------+-----------------------------------+                 
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
2 - SEL$1                / "COUNT_TEST"@"SEL$1"
------------------------------------------------------------
Predicate Information:
------------------------

SQL>

No surprises there; notice that Oracle does not expand the "*" to all columns in the table -- the "*" in this case indicates that all rows are to be counted. Had an actual column name been provided then Oracle would have counted values in the specified column. Let's now look at what Oracle does with a count(rowid) query:


SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

SQL> select count(rowid) from count_test;

COUNT(ROWID)
------------
     1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(rowid) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 371675025

----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |  3582 |           |
| 1   |  SORT AGGREGATE     |           |     1 |    12 |       |           |
| 2   |   TABLE ACCESS FULL | COUNT_TEST|  848K | 9941K |  3582 |  00:00:43 |
----------------------------------------+-----------------------------------+           

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(ROWID)[22]
   2 - (rowset=256) ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>

Oracle generates a rowid value for each row in the table, an operation which will consume some CPU resources. Since the query returned in roughly the same time as the count(*) version the performance 'hit' appears to be negligible. Adding a primary key changes the plans slightly but not the query text:



SQL> alter table count_test add constraint count_pk primary key(id);

Table altered.                                                                                                                            
SQL>
SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

SQL> select count(*) from count_test;

  COUNT(*)
----------
   1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(*) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 371675025

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |   589   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| COUNT_PK |   848K|   589   (2)| 00:00:01 |
--------------------------------------------------------------------------        

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>
SQL>
SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

SQL> select count(rowid) from count_test;

COUNT(ROWID)
------------
     1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(rowid) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 371675025

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    12 |   589   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |    12 |            |          |
|   2 |   INDEX FAST FULL SCAN| COUNT_PK |   848K|  9941K|   589   (2)| 00:00:01 |
----------------------------------------------------------------------------------                 
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(ROWID)[22]
   2 - (rowset=256) ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>
SQL> spool off
commit;

The 10053 trace details did not change after the primary key was added.

It would appear that two pieces of information have been gleaned from this experiment -- count(rowid) is no better than count(*) when tables contain CLOB columns and that count(*) does not expand the column list as "select *" does (and there is no reason to believe that it should).

The proof is in the pudding, as the old adage goes.

# # #

See articles by David Fitzjarrell

Home
Mobile Site | Full Site