Re: Checking if more than 0 rows exist.

Mark D Powell wrote:

> Interesting that no one mentioned the exists clause. This is exactly
> what an exists subquery is for: test to see if some condition is true
> and by design the statements stops processing as soon as one hit is
> found.

That was my first thought. In 9i, EXISTS is much faster than counting rows.

To simply count rows:

SQL> select count(*) from foo.bar
  2 where office = 45
  3 and statement = 199901;

  COUNT(*)

      9275

Elapsed: 00:00:06.02

ROWNUM = 1 won't actually change the execution time:

SQL> select count(*) from foo.bar
  2 where office = 45
  3 and statement = 199901
  4 and rownum = 1;

  COUNT(*)

         1

Elapsed: 00:00:06.06

Using EXISTS is much faster:

SQL> select count(*) from dual
  2 where exists
  3 (
  4 select 1 from foo.bar
  5 where office = 45
  6 and statement = 199901
  7 );

  COUNT(*)

         1

Elapsed: 00:00:00.00

These results are from 9.2.0.4, EE. The test table has 2.8M rows, and the lookup is indexed.

• Phil