DA Morgan wrote:
> 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
Where's the cursor/open/fetch?
--
Regards,
Frank van Bortel
Received on Tue May 10 2005 - 10:57:33 CDT
