The Oracle (tm) Users' Co-Operative FAQ

Is there a good way of counting the number of rows in a table ?


Author's name: Mark D Powell

Author's Email: Mark.Powell@eds.com

Date written: 09 August 2001

Oracle version(s): 8.1.7.0

Is there a good way of counting the number of rows in a table ? Note if what you really want to know is how to generate the select counts for all the tables in your system then see the FAQ: Is there a simple way to produce a report of all tables in the database with current number of rows ? where dynamic SQL is used to perform this task. This FAQ entry actually talks about tuning the select count SQL statement.

Back to index of questions


You can use the count(*) function to get row counts from Oracle and with Oracle rdbms version 8.1.7+ it would appear that the cost based optimizer, CBO, optimizes unqualified select count statements for you where a primary key constraint exists. The example table is allocated at 200M and the PK index is 20M: For reference the ANSI aggregate function of min, max, count, sum, and avg ignore null values in their input sets and have the form of:

	function( ALL | DISTINCT column_name | * ) 
Examples - Starting with a full table count on a table with a primary key, PK, constraint defined.
DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- -----------------------------------------------------
       123           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
       123      379426     3.1 INDEX FAST FULL SCAN ITEM_MASTER_PRIME UNIQUE


3 rows selected.

The CBO was able to utilize a fast full scan of the PK index to perform the count rather than read the full table sequentially which is what the RULE based optimizer did for the same query. Also notice the cardinality column where Oracle tells you how many rows it thinks it will process in this step; the actual rows returned will probably be different so running explain plans is not a good substitute for running select counts:

DDC2> select count(*) from item_master;

  COUNT(*)
----------
    387835

1 row selected.

If you are running rule based a full table scan is employed to solve the unqualified full count but with version 7.3 on, a hint provided in the SQL overrides the database and session optimizer settings, even if no statistics exist on the table, so you can use an index hint on the PK to force use of the index. Under version 7 the CBO would also full scan the table to solve this query, but again you can hint the SQL as long as statistics existed on the table for version 7.0 - 7.2 and with or without statistics in version 7.3.

Select /*+ INDEX(t t_pk) */ count(*)
From   table_name t

   t = label for the table in the from clause and t_pk is the primary key or unique index name

The index fast full scan option was not available until version 8 and the hint is INDEX_FFS. (addendum from Zach Friese zfriese@earthlink.net in fact the fast full scan was introduced in Oracle 7.3 - see page 5-30, Oracle7 Server Tuning).

In the case where a where clause needs to be provided to obtain the row count for a specific condition then Oracle's ability to optimize the query depends on the where clause columns being indexed just like with any normal query. The PK for this table consists of item_no and plant. The plant is the second column in the key. Remember that in version 7 that Oracle can only use a concatenated index when the leading column of the index is referenced in the where clause but observe:

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  where  plant = '12'
  5  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- -----------------------------------------------------
       123           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
       123       63238     3.1 INDEX FAST FULL SCAN ITEM_MASTER_PRIME UNIQUE


3 rows selected.

The CBO is still able to scan the PK to count the number of plant 12 entries. Under RULE a full table scan is once again used:

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select /*+ RULE */ count(*)
  3  from   item_master
  4  where  plant = '12'
  5  /

Explained.

Elapsed: 00:00:00.04
DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- --------------------------------------------------
                        SELECT STATEMENT
                         2.1 SORT AGGREGATE
                           3.1 TABLE ACCESS FULL ITEM_MASTER

The CBO and RULE base optimizers can both use indexes to solve count requests for leading indexed columns even those that are not part of the PK or a UK constraint. In the example product_code is the leading column of item_master_idx6 and may be null.

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  where  product_code = '12'
  5  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- ----------------------------------------------------
         3           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
         3         234     3.1 INDEX RANGE SCAN ITEM_MASTER_IDX6 NON-UNIQUE


3 rows selected.

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select /*+ RULE */ count(*)
  3  from   item_master
  4  where  product_code = :1
  5  /

Explained.

[Note the rule hint so the cost and cardinality column of the explain plan table are now null, but the plan is the same because under RULE if the index exists we use it. Also note that in this case using bind variables instead of constants does not prevent the optimizer from using the index under either optimizer approach.]

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- ----------------------------------------------------
                        SELECT STATEMENT
                         2.1 SORT AGGREGATE
                           3.1 INDEX RANGE SCAN ITEM_MASTER_IDX6 NON-UNIQUE


3 rows selected.

If you need a count based on the where condition referencing a non-indexed column even if is constrained to be not null then the optimizers will have no option but to full scan the table:

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  where  family_cd    is null
  5  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- --------------------------------------------------
      1296           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
      1296           1     3.1 TABLE ACCESS FULL ITEM_MASTER

3 rows selected.

The column family_cd is constrained to be not null but does not appear in any index. Logically if the column is constrained to be not null then it has a value for every row in the table so the result of a count of nulls is zero, but the optimizer is still going to count.

In the case where you want the not null count for a null allowed column then you can make the following change:

From:
DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  where  family_cd is not null
  5  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- --------------------------------------------------
      1296           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
      1296      379426     3.1 TABLE ACCESS FULL ITEM_MASTER


To:
DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(family_cd)
  3  from   item_master
  4  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- -----------------------------------------------------
       123           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
       123      379426     3.1 INDEX FAST FULL SCAN ITEM_MASTER_PRIME UNIQUE

By removing the where clause condition test and counting the specific column the CBO can use the PK index, but the RULE based optimized solved the above using a full table scan.

The above covers the case where the column is not-null but what about when it is or can be null. I am pretty sure that at one time if you ran a query of the form select count(*) from table where [nullable] column is null OR is not null then the optimizer would perform a full table scan even if the column was indexed. But with version 8.1.7+ if at least one column in the index that the where clause column is the leading column of is constrained to being not null then the CBO can use it to satisfy the query:

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  where  product_code is not null
  5  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- --------------------------------------------------------
       132           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
       132        2799     3.1 INDEX FAST FULL SCAN ITEM_MASTER_IDX6 NON-UNIQUE


3 rows selected.

DDC2> EXPLAIN PLAN  SET statement_id = 'mpowel01' FOR
  2  select count(*)
  3  from   item_master
  4  where  product_code is null
  5  /

Explained.

DDC2> set echo off

      COST CARDINALITY QUERY_PLAN
---------- ----------- --------------------------------------------------------
       132           1  SELECT STATEMENT
                     1   2.1 SORT AGGREGATE
       132      376627     3.1 INDEX FAST FULL SCAN ITEM_MASTER_IDX6 NON-UNIQUE


3 rows selected.

DDC2> select count(*) from item_master where product_code is null;

  COUNT(*)
----------
    385100

1 row selected.

DDC2> select count(*) from item_master where product_code is not null;

  COUNT(*)
----------
      2744

1 row selected.

DDC2> select count(*) from item_master;

  COUNT(*)
----------
    387844

1 row selected.  

[This count differs from the one at the top of the page because this is production and activity is taking place, but the null and non-null product_code counts add to the current total rows so Oracle was able to use the index.]

If all the columns of a normal index are capable of being null then expect the optimizer to full table scan. However bitmap indexes store nulls so the CBO can use bitmap indexes to solve null and non-null query requests including count(*). The RULE base optimizer does not recognize bit-map indexes.


Further reading: Is there a simple way to produce a report of all tables in the database with current number of rows ?


Back to top

Back to index of questions