Using rownum with count (*)

David Clement

September 2004

Oracle versions 7-8

An interesting gimmick is to use the Oracle pseudocolumn rownum to limit I/O and the number of rows counted in those cases where you are only interested in the existence of a record in a table. This is analogous to using exists in a condition, such as a subquery or a "where" clause.

Suppose a table T with one column C. To find out if there are rows of a certain value, you can write the following query. 

          select count (*)
          from t
          where c = :b1;
in which :b1 is a bind variable to which a host language variable will be bound. This query will count all the rows in t where c equals the host language variable.

But if you only cared about the existence of a matching row in t, not about how many matching rows there are, you could write this query. 

          select count (*)
          from t
          where c = :b1
          and rownum = 1;
If the first query returned 5, this would return 1. The condition on rownum applies before the count (*) is processed.

This is cute, but useless, you may be thinking. However, if you are checking for the existence of a matching row in a four million row table, you can save a lot of I/O by means of this trick.