dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 18, 2020

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

By David Fitzjarrell

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



Oracle Archives




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM