Re: Testing for Existence of Rows - What's Fastest?

Ta Thomas, guess I should have presented the full evidence when I did my first post.
I recall when we hit something similar in a 3rd party supplied trigger pl/sql code.
I was fairly astounded over the difference at the time, but its just another clever bit of Oracle (they seem to have thought of everything).

rownum is also useful for pseudo-sampling a large tables.

I was thinking of checking a materialized view too, but I guess it depends on the cardinality of the column being checked.

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:acqv5i02bh_at_drn.newsguy.com...
tablescan)..
> yes it is -- it is accurate. Rownum is assigned dynamically and we have a
> special "stop" to abort the query when we know no more rows can satisfy
the
> results. Rownum is a very effective way to "stop" a query before its
done.
>
> >Rownum's aren't determined dynamically, they are determined when the
result
> >set is complete.
> >So you are actually fetching *ALL* rows that satisfy the where clause,
and
> >cutting them off after fetching.
> >Try it, with set autotrace on explain stat, and you will see I am
correct.
> >
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select count(*) from all_objects where
owner=
> 'SYS' and rownum < 2;
>
> COUNT(*)
> ----------
> 1
>
>
> Statistics
> ----------------------------------------------------------
> 7 recursive calls
> 0 db block gets
> 10 consistent gets
> 0 physical reads
> 0 redo size
> 367 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select count(*) from all_objects where
owner=
> 'SYS';
>
> COUNT(*)
> ----------
> 11802
>
>
> Statistics
> ----------------------------------------------------------
> 7 recursive calls
> 0 db block gets
> 58616 consistent gets
> 13 physical reads
> 0 redo size
> 369 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
>
> shows that with rownum < 2, we do 10 consistent gets. Loose the rownum <
2, we
> do 58,616. Huge difference. All due to rownum letting us abort the
query:
>
>
> sys_at_ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
> sys_at_ORA817DEV.US.ORACLE.COM> select count(*) from all_objects where owner=
'SYS'
> and rownum < 2;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (AGGREGATE)
> 2 1 COUNT (STOPKEY) <<<<<======= this is the guy here
> 3 2 FILTER
> 4 3 NESTED LOOPS
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
> 6 5 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
> 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
> 8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
> 9 3 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
> 10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
> 11 3 NESTED LOOPS
> 12 11 FIXED TABLE (FULL) OF 'X$KZSRO'
> 13 11 TABLE ACCESS (BY INDEX ROWID) OF 'OBJAUTH$'
> 14 13 INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
> 15 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 16 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 17 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 18 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 19 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 20 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 21 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 22 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 23 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 24 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 25 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 26 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 27 3 FIXED TABLE (FULL) OF 'X$KZSPR'
> 28 3 FIXED TABLE (FULL) OF 'X$KZSPR'
>
>
>
>
>
> >Regards
> >
> >
> >--
> >Sybrand Bakker
> >Senior Oracle DBA
> >
> >to reply remove '-verwijderdit' from my e-mail address
> >
> >
> >
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Mon May 27 2002 - 14:53:28 CDT