Re: Testing for Existence of Rows

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 26 May 2002 08:32:02 -0700
Message-ID: <acqv5i02bh@drn.newsguy.com>

In article <uf1m2e77vopud1_at_corp.supernews.com>, "Sybrand says...
>
>
>

[snip]

>
>
>SELECT count(colA)
> INTO varCount
> FROM tableA -- has ~300,000 rows
> WHERE...
> AND
> rownum <2;
>
>rownum is the number of rows that have matched so far in the query.
>This should end the query when it finds the first occurance, rather than
>look at every row in the table (i.e. partial full tablescan vs full
>tablescan)..
>
>Have you ever tried this? It is simply not true.

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(*)

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 Sun May 26 2002 - 10:32:02 CDT

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