Re: Checking if more than 0 rows exist.

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Tue, 10 May 2005 08:27:21 -0700
Message-ID: <1115738590.156189@yasure>

Frank van Bortel wrote:

> Jeremy wrote:
>

>>In article <d5og57$je0$1_at_news3.zwoll1.ov.home.nl>, Frank van Bortel
>>says...
>>
>>>>Select count(*) from combo_table where all fields match would count them all
>>>>but all I really need to know is if any exist (>0 rows).
>>>
>>>Right question: define a cursor with all 5 columns concerned.
>>>Open the cursor - (attempt to) fetch any record.
>>>If found, you're OK - close cursor, 1 read done.
>>>If not found, you're OK - close cursor, 1 read done.
>>
>>Do you think this is a better approach than using EXISTS as suggested
>>(and proved to be faster) by Phil?
>>

> I have not seen any comparism - you'll have to do that.
> Basically, an exists will do something similar; stop reading
> after the first match.

Here's the reality check on this discussion:

SELECT COUNT(*)
FROM bar
WHERE office = 45
AND statement = 199901
AND rownum = 1;

Execution Plan

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=26) 1 0 SORT (AGGREGATE)

    2    1     COUNT (STOPKEY)
    3    2       TABLE ACCESS (BY INDEX ROWID) OF 'BAR' (TABLE) (Cost=1 
Card=4 Bytes=104)
    4    3         INDEX (RANGE SCAN) OF 'IX_BAR_OFFICE' (INDEX) (Cost=1

Card=4)

Statistics

           5  recursive calls
           0  db block gets
          27  consistent gets
           0  physical reads
           0  redo size
         420  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed
=======================================================================

SELECT 1
FROM dual
WHERE EXISTS (
   SELECT *
   FROM bar
   WHERE office = 45
   AND statement = 199901);

Execution Plan

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1) 1 0 FILTER

    2    1     FAST DUAL (Cost=2 Card=1)
    3    1     TABLE ACCESS (BY INDEX ROWID) OF 'BAR' (TABLE) (Cost=1 
Card=4 Bytes=104)
    4    3       INDEX (RANGE SCAN) OF 'IX_BAR_OFFICE' (INDEX) (Cost=1

Card=4)

Statistics

           4  recursive calls
           0  db block gets
          27  consistent gets
           0  physical reads
           0  redo size
         413  bytes sent via SQL*Net to client
         508  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Tue May 10 2005 - 10:27:21 CDT