Re: Checking if more than 0 rows exist.

From: Walt <walt_askier_at_YerBootsyahoo.com>
Date: Tue, 10 May 2005 11:36:24 -0400
Message-ID: <Zx4ge.1357$II.489@news.itd.umich.edu>

phil_herring_at_yahoo.com.au wrote:
> 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.

Small observation: adding 'rownum = 1' actually *increases* the execution time. Not by enough to make much practical difference, but a degredation in performance nonetheless. I'm seeing the same phenomonon now that I'm actually testing it (as opposed to just shooting my mouth off).

Your broader point - that 'exists' is much faster - is certainly the case.

-- 
//-Walt

Received on Tue May 10 2005 - 10:36:24 CDT