Frank van Bortel wrote:
> 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?
Ask Oracle. ;-)
What you see is the result of AUTOTRACE TRACEONLY in 10.1.0.4.
I performed no editing.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Tue May 10 2005 - 14:54:09 CDT
