Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Sujit .

Asked: May 02, 2000 - 1:20 pm UTC

Answered by: Tom Kyte - Last updated: September 23, 2013 - 6:14 pm UTC

Category: - Version:

Viewed 10K+ times! This question is

You Asked

How does rownum work? suppose I issue a query where I
put the condition as "where rownum < 100". Does Oracle first
select all the row into the buffer and then return 99 rows?


and we said...


It depends. If we can get 99 rows and then stop -- yes. For example, if T is a table:

select * from t where rownum < 99;

will get just the first 99 rows and stop -- it'll not expend the engery to get all of the rows in T.

On the other hand, if it is something like:


select * from V where rownum < 99;

and V is a complex view involving an aggregate (eg: v = "select deptno, count(*) from emp group by deptno") -- the entire result set may be materialized into a temp result and then the first 99 rows returned.

So the answer is -- usually we just get the first N and stop but sometimes we have to build the entire result set and then return the first N rows of it.


and you rated our response

  (74 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

How can we prove the above conclusively with statistics

November 19, 2001 - 6:21 pm UTC

Reviewer: Nag

SQL> select deptno, avg(sal)
  2  from emp
  3  where rownum<5
  4  group by deptno  ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'EMP'




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



SQL> select deptno, avg(sal)
  2  from emp
  3  group by deptno  ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     TABLE ACCESS (FULL) OF 'EMP'




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

Can we consider bytes sent via SQL*Net to client as conclusive proof that less rows are being read when where condition is given using rownum.
 

Tom Kyte

Followup  

November 19, 2001 - 6:38 pm UTC

You cannot prove conclusively from this output that less rows were actually read, only that less rows were sent back to the client.

There is insufficient data in the table to see the effect of rownum in this case.

Conlusive proof -

November 20, 2001 - 9:57 am UTC

Reviewer: Nag

Can the increasing consistent gets, bytes sent via SQL*Net to client , below when using rownum, be considered conlusive proof of lesser work being done when requesting lesser number of rows via  <rownum.
          


SQL> select object_name from all_objects;

14610 rows selected.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          7  recursive calls
          4  db block gets
     234213  consistent gets
         16  physical reads
          0  redo size
     436887  bytes sent via SQL*Net to client
      38200  bytes received via SQL*Net from client
        975  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14610  rows processed


SQL> select object_name from all_objects 
  2  where rownum <100;

99 rows selected.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          7  recursive calls
          4  db block gets
        397  consistent gets
          5  physical reads
          0  redo size
       2778  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> select object_name from all_objects 
  2  where rownum <300 ;

299 rows selected.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          7  recursive calls
          4  db block gets
       3596  consistent gets
          4  physical reads
          0  redo size
       7601  bytes sent via SQL*Net to client
        994  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        299  rows processed

SQL> select object_name from all_objects 
  2  where rownum <600 ;

599 rows selected.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          7  recursive calls
          4  db block gets
      10953  consistent gets
          1  physical reads
          0  redo size
      13394  bytes sent via SQL*Net to client
       1774  bytes received via SQL*Net from client
         41  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        599  rows processed
 

Tom Kyte

Followup  

November 20, 2001 - 10:58 am UTC

the consistent gets tell the story. The bytes transmitted over the network tell you nothing about the work done on the server.

Try:

select count(*) from all_objects where rownum < ....

they all have the same bytes sent (for the count) but the consistent gets will vary based on the rows processed.


ROWNUM in Top-N queries

November 20, 2001 - 4:03 pm UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

Please correct me if I am wrong... (or confirm it if I am right):

Query a) select ename, sal
from emp
where rownum<=10
order by sal desc;

Query b) select ename, sal
from ( select ename, sal
from emp
order by sal desc)
where rownum<=10;

Oracle8i introduced the possibility of using order bys in in-line views, and now it has the capability of doing a "smart" sort.

Important, not that the the processing will flow like indicated in the query. Oracle will identify the Top-N syntax and do this "smart" sort.

What I suppose the queries do:

Query a) will first sort all the rows in the emp table -- quicksort style, I imagine -- and then extract the first 10 rows, that is, the top 10 salaries.

Query b) will do a scan on the table to simply get the top 10 values, like storing 10 MAX()'s in some memory positions. The result: the same as query a) except for the processing complexity.

We go from an O(n log n) to an O(1) algorithm, which is just fantastic if you are considering huge, petabyte data farms and VLDB's. That might mean years as opposed to minutes on large amounts of data.

>> Is this so deterministic ? <<

Another consideration:

>> Is it possible to use parallel query when sorting ? <<

That does not make much sense in coarse-grained computing -- in our general case, SMP machines -- since sorting algorithms can only be really optimized in fine-grained architectures. In SMP machine you would still be getting O[(n log n)/m] -- m being the number of processors -- which ends up in O(n log n).

Tom Kyte

Followup  

November 20, 2001 - 5:12 pm UTC

Query a ) will find 10 records and then sort them. Rownum is assigned before the ORDER BY.

Query b ) will sort the entire emp table by SAL desc (unless an index exists, in which case we'll do something different) and then take the first 10 rows.

If we have an index on sal, we can read it backwards -- avoiding the sort, get the 10 rows and be done with it.

query a and query b return different answers -- almost certainly (a may accidently return the same answer but it probably won't).

This is easy to see:

scott@ORA817DEV.US.ORACLE.COM> select ename, sal
2 from emp
3 where rownum<=10
4 order by sal desc;

ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
A 800

10 rows selected.

scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> select ename, sal
2 from ( select ename, sal
3 from emp
4 order by sal desc)
5 where rownum<=10;

ENAME SAL
---------- ----------

KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300

10 rows selected.


You do not get your reduction at all.

If you have an index on SAL, the optimizer is smart enough to figure out that it can read it to get the 10 rows it needs.


Yes, you can use PQ when sorting. Scanners feed sorters, sorters feed a single merger.






PLAY OF THE DAY

November 20, 2001 - 9:23 pm UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

Well Tom...

You will have to find a good interjection for this one... so will I...

This is TOTALLY confusing. I researched the on-line books and they are reticent about this issue.

Excerpt from the SQL Reference:

----------------------------------------------------------

" For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM emp WHERE ROWNUM < 10;


If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM emp WHERE ROWNUM < 11 ORDER BY empno;


If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 11;


In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by EMPNO in the subquery. "

----------------------------------------------------------

OK, this is what you said.

BUT... Read this text from the Oracle University Book, which seems to be undocumented in the on-line books: "Oracle8i: New Features for Administrators", page 3-22

----------------------------------------------------------
"
(slide)

Top-N SQL

-Views and in-line views now allow ordering
-Sorts only the >>>required<<< number of rows

SELECT *
FROM ( SELECT class_id, crs_id, start_date
FROM classes
ORDER BY start_date DESC )
WHERE ROWNUM < 10

...

(text)

As shown in the slide, >>>when only the top 10 rows are requested, Oracle does not sort all the rows in the table to produce the result<<<. Oracle stores a maximum of 10 rows as data is retrieved from the table. When the 11th row is read, it discards the row with the lowest margin. This operation continues until all the data is read and the >>>sorting takes place only on the 10 rows at the end of the operation<<<. Here is the execution plan for this operation:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 SORT (ORDER BY STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'CLASSES'

"
----------------------------------------------------------

What now ?

---------> AskDavidAustin.oracle.com ...


Tom Kyte

Followup  

November 21, 2001 - 9:30 am UTC

Well, we are NOT magic ;)

If there is no index on start_date, we obviously MUST sort the data -- otherwise it would behave just like the original

  select * from t where rownum < 11 order by x;


So, it does in fact sort the entire result set and then returns the first 9.  Now, if there is an index, it can (and will) use it.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2   from ( select owner, object_name, object_id
  3                    from t
  4                   order by object_id desc )
  5   where rownum < 10;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
OPS$TKYTE                      T                                   23087
OPS$TKYTE                      MD5STRING                           23086
OPS$TKYTE                      MD5                                 23085
OPS$TKYTE                      THE_OLD_IN_OUT                      23084
OPS$TKYTE                      STRING_ARRAY                        23083
OPS$TKYTE                      ArrayDemo                           23082
OPS$TKYTE                      ArrayDemo                           23081
A                              T                                   23078
OPS$TKYTE                      PRINT_TABLE                         23048

9 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     VIEW
   3    2       SORT (ORDER BY STOPKEY)
   4    3         TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets<b>
        238  consistent gets</b>
        234  physical reads
          0  redo size
        936  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed


<b>note the blocks processed -- 238.  Thats the entire table, as we'll see below.  We read and sorted everything</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(object_id);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2   from ( select owner, object_name, object_id
  3                    from t
  4                   order by object_id desc )
  5   where rownum < 10;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
OPS$TKYTE                      T                                   23087
OPS$TKYTE                      MD5STRING                           23086
OPS$TKYTE                      MD5                                 23085
OPS$TKYTE                      THE_OLD_IN_OUT                      23084
OPS$TKYTE                      STRING_ARRAY                        23083
OPS$TKYTE                      ArrayDemo                           23082
OPS$TKYTE                      ArrayDemo                           23081
A                              T                                   23078
OPS$TKYTE                      PRINT_TABLE                         23048

9 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     VIEW
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T'
   4    3         INDEX (FULL SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets<b>
          9  consistent gets</b>
          1  physical reads
          0  redo size
        936  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed


ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

<b>Now, if we look at the size of the raw table:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space('T' )
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................21
Unused Bytes............................172032
Last Used Ext FileId....................7
Last Used Ext BlockId...................246537
Last Used Block.........................43

PL/SQL procedure successfully completed.

<b>We can see the 238 blocks = 256     - 21     - 1           + 4
                              Total     Not    Overhead      full scan
                              Blocks    Used   for Table     read seg. hdr                                                             four times

</b>
it read the entire table, sorted it then gave us the answer. 

Rownumis only Record Number Saved

November 21, 2001 - 7:44 am UTC

Reviewer: Mohammed Al-moayed from Yemen

Hi, As I noticed That rownum is the rownumber and the sequence of inserting the Record to the Table.
If you use
Select rownum,empno,ename
from emp
order by empno ;

The rownum for each Record retrived will not be in sequence
1,2,3,4,....

you can insert into emp table any Record ,and select max(rownum ) from that table .

and you will see ....



Tom Kyte

Followup  

November 21, 2001 - 9:56 am UTC

NO ITS NOT!!!!!! (if you get Oracle Magazine, I just published an article on this as many many people seemed to be totally confused by this).

Rownum is assigned to rows as they are retrieved from the table.  Data in a table is NOT STORED in order of INSERTION.


quick and dirty example that hopefully puts this to rest:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
  2  ( x int,
  3    a char(2000) default 'x',
  4    b char(2000) default 'x',
  5    c char(2000) default 'x');

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 2 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 3 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where x = 2;

1 row deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 4 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, rownum from t;

         X     ROWNUM
---------- ----------
         1          1
         4          2
         3          3


<b>FACTS:

o rows are not stored in order of insertion.  
o rownum is not the sequence of inserting the rows.

</b> 

Where can an 'order by in an inline view be useful'

November 21, 2001 - 8:29 am UTC

Reviewer: Nag

Tom

I read above

"Oracle8i introduced the possibility of using order bys in in-line views"

Can you demonstrate the utility of using an order by in an inline view.

What and in what cases this can make a difference.

Tom Kyte

Followup  

November 21, 2001 - 10:13 am UTC

see my comment that starts with:

Well, we are NOT magic ;)



Magic ???

November 21, 2001 - 11:03 am UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

Sorry Tom, with all due respect...

Questions:

1) What do you mean by 'we are not magic' ? The algorithm described in the course book storing the MAX's makes A LOT of sense if there is not an index in place.

2) So Oracle charges nearly US$ 1000,00 for a course and the all excited instructor explains a new internal feature and I in turn get across how wonderful it is, and it does not simply work in fact ?

3) Is the Oracle University material 'bugged' ? So, there nothing SO great about the Top-N query feature, only the possibility of putting an order by in the in-line view ? The OU instructor explained that Oracle would identify the syntax and arrange an special execution plan, blah, blah, blah... In sum, the "smart sort" is a legend ?

Buddy, I regret to inform you that at this time there is an army of instructors out there saying this... thing.

Tom Kyte

Followup  

November 21, 2001 - 12:40 pm UTC

Look at the paragraph -- I did not say he was wrong.  Lets digest this:

......
As shown in the slide, >>>when only the top 10 rows are requested, Oracle does 
not sort all the rows in the table to produce the result<<<. Oracle stores a 
maximum of 10 rows as data is retrieved from the table. When the 11th row is 
read, it discards the row with the lowest margin. This operation continues until 
all the data is read and the >>>sorting takes place only on the 10 rows at the 
end of the operation<<<. Here is the execution plan for this operation:
....


Perhaps we are mincing terms here but.... we read the entire table and in fact sorted it (keeping just the 10 you were interested in).  Lets be more clear about this feature then.  We'll look at it in a little more detail. (i myself do not see any conflicts between what you cut and pasted from OU and what we see here).


We'll measure what happens when we use the "new way" with rownum versus the DIY (do it yourself way) -- the results are huge.  My point was:

o we read the entire table, no getting around that.  we in fact sort it as well -- just smarter then we would if we did not know you wanted the top n.

o if there is an index, we can do this really well.


Consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view stats
  2  as select 'STAT...' || a.name name, b.value
  3        from v$statname a, v$mystat b
  4       where a.statistic# = b.statistic#
  5      union all
  6      select 'LATCH.' || name,  gets
  7        from v$latch;
View created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( object_name char(2000), object_id number, owner varchar2(30) );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
  2  select object_name, object_id, owner
  3    from all_objects
  4   where rownum < 101;
100 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 65536;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3  begin
  4      insert into run_stats select 'before', stats.* from stats;
  5  
  6      l_start := dbms_utility.get_time;
  7      for i in 1 .. 100
  8      loop<b>
  9          for x in ( select *
 10                        from ( select * from t order by object_name )
 11                       where rownum < 10 )</b>
 12          loop
 13              null;
 14          end loop;
 15      end loop;
 16      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 17  
 18      insert into run_stats select 'after 1', stats.* from stats;
 19  
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 100
 22      loop<b>
 23          for x in ( select a.*, rownum r
 24                        from ( select * from t order by object_name ) a )
 25          loop</b>
 26              exit when ( x.r >= 10 );
 27          end loop;
 28      end loop;
 29      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 30  
 31      insert into run_stats select 'after 2', stats.* from stats;
 32  end;
 33  /<b>
22 hsecs
2306 hsecs</b>

PL/SQL procedure successfully completed.


ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.done queue latch                            2          1         -1
LATCH.ksfv messages                               4          3         -1
STAT...calls to get snapshot scn: kcmgss        506        505         -1
STAT...consistent gets                         3812       3811         -1
LATCH.multiblock read objects                     2          0         -2
STAT...parse time cpu                             3          1         -2
STAT...parse time elapsed                         1          3          2
LATCH.library cache                             544        547          3
STAT...redo entries                              16         13         -3
STAT...db block changes                          27         22         -5
LATCH.active checkpoint queue latch               0          8          8
LATCH.session timer                               0          8          8
LATCH.shared pool                               159        147        -12
LATCH.redo allocation                            16         30         14
LATCH.redo writing                                3         29         26
STAT...prefetched blocks                         33          0        -33
LATCH.checkpoint queue latch                      2         36         34
LATCH.messages                                    3         44         41
STAT...redo size                              21484      21428        -56
STAT...free buffer requested                     38        103         65
STAT...enqueue requests                           5        103         98
STAT...enqueue releases                           4        103         99
LATCH.file number translation table               1        101        100
STAT...recursive calls                         1031       1131        100<b>
STAT...sorts (memory)                           103          3       -100
STAT...sorts (disk)                               1        101        100</b>
STAT...recursive cpu usage                       27        149        122
LATCH.cache buffers lru chain                    38        208        170
STAT...session logical reads                   5038       5230        192
STAT...db block gets                           1226       1419        193
LATCH.enqueue hash chains                         6        206        200
LATCH.loader state object freelist                2        202        200
LATCH.enqueues                                   13        413        400
LATCH.sort extent pool                            6        606        600
STAT...physical reads                            43        809        766
STAT...physical reads direct                      9        809        800
LATCH.row cache objects                          85        985        900
STAT...physical writes                            9       2509       2500
STAT...physical writes direct                     9       2509       2500
STAT...physical writes non checkpoint             9       2509       2500
LATCH.cache buffers chains                    10197      17102       6905

41 rows selected.


As you can see -- there is an ENORMOUS difference (and that guy was worth the $1,000 bucks).  It does work -- never meant to say it didn't -- only that its not magic, it still has to read the entire table, finding the top "N".  Perhaps they should use some framework like I do above to help quantify this, the above shows very clearly the effect.... However, compared to what you can do with a well placed index on a top-n:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( object_name char(2000), object_id number, owner varchar2(30) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
  2  select object_name, object_id, owner
  3    from all_objects
  4   where rownum < 101;

100 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t2;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 as select * from t;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t2_idx on t2(object_name);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t2 modify object_name NOT NULL;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 65536;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3  begin
  4      insert into run_stats select 'before', stats.* from stats;
  5  
  6      l_start := dbms_utility.get_time;
  7      for i in 1 .. 100
  8      loop
  9          for x in ( select *
 10                        from ( select * from t order by object_name )
 11                       where rownum < 10 )
 12          loop
 13              null;
 14          end loop;
 15      end loop;
 16      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 17  
 18      insert into run_stats select 'after 1', stats.* from stats;
 19  
 20      l_start := dbms_utility.get_time;
 21      for i in 1 .. 100
 22      loop
 23          for x in ( select *
 24                        from ( select * from t2 order by object_name )
 25                       where rownum < 10 )
 26          loop
 27              null;
 28          end loop;
 29      end loop;
 30      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 31  
 32      insert into run_stats select 'after 2', stats.* from stats;
 33  end;
 34  /
21 hsecs
14 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.active checkpoint queue latch               1          0         -1
LATCH.enqueues                                   13         14          1
LATCH.session idle bit                            1          2          1
STAT...free buffer requested                      5          6          1
STAT...total file opens                           1          0         -1
STAT...parse time elapsed                         1          2          1
STAT...enqueue releases                           4          3         -1
LATCH.direct msg latch                            2          0         -2
LATCH.library cache load lock                     0          2          2
STAT...parse time cpu                             1          3          2
STAT...enqueue requests                           5          3         -2
LATCH.session queue latch                         2          0         -2
LATCH.session allocation                          2          4          2
LATCH.redo writing                                4          2         -2
LATCH.done queue latch                            2          0         -2
LATCH.checkpoint queue latch                      5          8          3
STAT...redo entries                              16         13         -3
STAT...physical reads                             9         12          3
STAT...parse count (total)                        6          9          3
STAT...opened cursors cumulative                  6          9          3
STAT...execute count                            105        108          3
STAT...cluster key scans                          0          3          3
STAT...cluster key scan block gets                0          3          3
LATCH.messages                                    5          2         -3
LATCH.ksfv messages                               4          0         -4
LATCH.cache buffers lru chain                     5         11          6
STAT...db block changes                          28         22         -6
LATCH.row cache objects                          85         91          6
LATCH.redo allocation                            16         23          7
LATCH.shared pool                               165        155        -10
STAT...recursive cpu usage                       28         18        -10
STAT...recursive calls                         1031       1106         75
LATCH.library cache                             519        601         82
STAT...sorts (memory)                           103          7        -96
STAT...table scans (short tables)               100          0       -100
STAT...redo size                              21512      21404       -108
STAT...calls to get snapshot scn: kcmgss        506        108       -398
LATCH.undo global data                          401          0       -401
STAT...table fetch by rowid                       8        909        901
STAT...buffer is not pinned count              3416       2421       -995
STAT...db block gets                           1228         19      -1209
STAT...no work - consistent read gets          3408       1712      -1696
STAT...consistent gets                         3812       1922      -1890
STAT...session logical reads                   5040       1941      -3099
STAT...table scan blocks gotten                3400          0      -3400
STAT...session uga memory                         0       4128       4128
LATCH.cache buffers chains                    10168       3921      -6247
STAT...sorts (rows)                           12317       2321      -9996
STAT...table scan rows gotten                 10000          0     -10000
STAT...session uga memory max                 24808          0     -24808

50 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
 

that is good

November 21, 2001 - 3:39 pm UTC

Reviewer: mano from usa

tom thanks for that answer.
can you guide whether i can achieve the following in any better way or any oracle 8i features to help the same.

I HAVE WRITTEN A PROC THAT ACCEPTS TWO PARAMS I_PAGE_NUM AND I_ROWS_PER_PAGE.DEPENDING ON THIS I SHOULD RETURN ROWS USING A REF CURSOR.

CREATE OR REPlace procedure
myproc(i_page_num number,i_rows_per_page number,o_ref_cur t_ref_cur)
BEGIN

OPEN O_REF_CUR for select * from (select rownum rno,a.* from emp a) where rno between
((i_page_num - 1) * i_rows_per_page) + 1 and ((i_page_num) * i_rows_per_page);

end;

is there a better way using any oracle 8i new features or using existing features.

rgds


Tom Kyte

Followup  

November 21, 2001 - 3:51 pm UTC

I generally code this as:

select * 
  from ( select a.*, rownum r 
           from ( YOUR_QUERY_HERE ) a
          where rownum <= :MAX_ROW )
 where r >= :MIN_ROW
/


For the reason that sometimes (NOT EVERYTIME) the optimizer can stop the inner query after MAX_ROW rows and then just through away the first MIN_ROW rows.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( object_name char(2000), object_id number, owner varchar2(30) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
  2  select object_name, object_id, owner
  3    from all_objects
  4   where rownum < 101;

100 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3          l_min_row number default 50;
  4          l_max_row number default 60;
  5  begin
  6      insert into run_stats select 'before', stats.* from stats;
  7  
  8      l_start := dbms_utility.get_time;
  9      for i in 1 .. 1000
 10      loop
 11          for x in ( select *
 12                        from ( select rownum r, t.* from t )
 13                       where r between l_min_row and l_max_row )
 14          loop
 15              null;
 16          end loop;
 17      end loop;
 18      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 19  
 20      insert into run_stats select 'after 1', stats.* from stats;
 21  
 22      l_start := dbms_utility.get_time;
 23      for i in 1 .. 1000
 24      loop
 25          for x in ( select *
 26                        from ( select rownum r, a.*
 27                                 from ( select * from t ) a
 28                                where rownum <= l_max_row )
 29                       where r >= l_min_row )
 30          loop
 31              null;
 32          end loop;
 33      end loop;
 34      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 35  
 36      insert into run_stats select 'after 2', stats.* from stats;
 37  end;
 38  /
198 hsecs
170 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.active checkpoint queue latch               1          0         -1
LATCH.session timer                               0          1          1
STAT...enqueue releases                           4          3         -1
STAT...parse time elapsed                         1          2          1
STAT...calls to get snapshot scn: kcmgss       5006       5005         -1
LATCH.messages                                    4          2         -2
STAT...enqueue requests                           5          3         -2
LATCH.multiblock read objects                     2          0         -2
LATCH.library cache                            2288       2291          3
STAT...redo entries                              16         13         -3
LATCH.redo writing                                3          0         -3
STAT...db block changes                          28         22         -6
STAT...db block gets                          12026      12017         -9
LATCH.redo allocation                            13         23         10
LATCH.shared pool                               175        159        -16
STAT...recursive cpu usage                      177        150        -27
LATCH.cache buffers lru chain                    36          6        -30
STAT...prefetched blocks                         33          0        -33
STAT...physical reads                            34          0        -34
STAT...free buffer requested                     38          2        -36
STAT...redo size                              21532      21420       -112
STAT...buffer is not pinned count             41016      27016     -14000
STAT...consistent gets                        45011      31011     -14000
STAT...table scan blocks gotten               41000      27000     -14000
STAT...no work - consistent read gets         41008      27008     -14000
STAT...session logical reads                  57037      43028     -14009
LATCH.cache buffers chains                   114127      86132     -27995
STAT...table scan rows gotten                100000      60000     -40000

28 rows selected.

Quite a difference in the buffers processed....
 

it is good

November 21, 2001 - 4:06 pm UTC

Reviewer: mano from usa

sorry tom,

i understood the example but not the theory behind that.
can you please explain me more about your

"For the reason that sometimes (NOT EVERYTIME) the optimizer can stop the inner
query after MAX_ROW rows and then just through away the first MIN_ROW rows"


I don't get it yet...

November 21, 2001 - 4:21 pm UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

So, from what I gathered, what you first said:

"Query b ) will >>>sort<<< the entire emp table by SAL desc (unless an index exists, in which case we'll do something different) and then take the first 10 rows."

That is:

1) Sorting (supposing there is no index)

2) Getting the top 10 values

>>>contradicts<<< what the OU book says:

"As shown in the slide, >>>when only the top 10 rows are requested, Oracle does not sort all the rows in the table to produce the result<<<. Oracle stores a maximum of 10 rows as data is retrieved from the table. When the 11th row is read, it discards the row with the lowest margin. This operation continues until all the data is read and the >>>sorting takes place only on the 10 rows at the end of the operation<<<."

That is:

1) "Collecting" the top 10 values NOT in a sorted state hrough a FTS, grabbing the greatest values along the way.

2) Sorting the resulting, >>>unordered<<< 10 values.

I understand that you might be mistakenly considering the last 1) a "sort". This is where we are diverging. You WILL have to agree this is no kind of sorting, at best some kind of MAXing.

I read your code carefully. Sorry I am sometimes a bit too slow, I saw the performance difference between both queries. I am impressed.

But I did not see anyhthing accounting for the discrepancy of concepts. If there was something else I missed, please show me more clearly.

Excuse me, but I am not convinced yet the 1000 bucks were worth it in the case the teacher told half the story: yes, faster, but working differently.


Tom Kyte

Followup  

November 21, 2001 - 4:37 pm UTC

Ok, i retract everything. You win.

Let me rephrase.

It get the 10 rows from EMP such that they have the 10 biggest salaries, it'll do this in a super good way.

It sorts no data whatsoever. It compares values to find out which is bigger then then other. It kept the 10 biggest ones. It did a super efficient "kind of sort that is not a sort". I was too loose with my terminology.

Hopefully the exhaustive example clears it all up and removes all confusion. The stats don't lie in this case, they show the difference.

it is good

November 21, 2001 - 4:41 pm UTC

Reviewer: mano from usa

sorry tom,

i understood the example but not the theory behind that.
can you please explain me more about your

"For the reason that sometimes (NOT EVERYTIME) the optimizer can stop the inner
query after MAX_ROW rows and then just through away the first MIN_ROW rows"


Tom Kyte

Followup  

November 21, 2001 - 5:00 pm UTC

The optimizer recognizes "where rownum < N" and knows -- Hey, if at all possible, I should STOP processing.

The optimizer sees "where rno between min and max" and doesn't have that ability.

Hence, by breaking it into two inline views, we can manytimes quickly build the result set for "where rownum < max_row" (faster then building the ENTIRE result set) and then applying the "where rno > min_row".

It is just a fact that when there is a rownum in the inline view/subquery, Oracle will materialize that result set. So, when you went:

select *
from ( select emp.*, rownum rno from emp )
where rno between A and B;

The query: "select emp.*, rownum rno from emp" was performed in FULL and then the predicate was applied.

In my case, the query:

select a.*, rownum r
from ( select * from t ) a
where rownum <= MAX_ROWS

was performed -- faster then yours since it only had to do MAX_ROWS rows -- and then the final predicate "where r >= min_rows" was applied.

SUN CHEN

July 23, 2002 - 11:35 pm UTC

Reviewer: SUN CHEN from HK

HI Tom,
Why the following procedure cann't work?

CREATE OR REPLACE procedure mi_clm_dscv is
begin

execute immediate 'delete from te_mi_clm_mvnt';

insert into te_mi_clm_mvnt
(prcp_abbr,
clm_no,
ac_yr_mth,
chnnl_nm,
isrd_nm,
grs_rsrv_bf,
net_rsrv_bf,
grs_paid,
net_paid,
grs_rsrv_cf,
net_rsrv_cf,
grs_incrrd,
net_incrrd,
grs_paid_ytd,
net_paid_ytd,
clm_ind,
mgt_cls_desc,
rn_l,
rn_s)
select e.prcp_abbr,
m.clm_no,
m.ac_yr_mth,
s.chnnl_nm,
p.isrd_nm,
m.grs_rsrv_bf,
m.net_rsrv_bf,
m.grs_paid,
m.net_paid,
m.grs_rsrv_cf,
m.net_rsrv_cf,
m.grs_incrrd,
m.net_incrrd,
m.grs_paid_ytd,
m.net_paid_ytd,
m.clm_ind,
b.mgt_cls_desc,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd desc) rn_l,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd asc) rn_s
from mi_clm_mvnt m,
mi_clm_m c,
mi_clm_pcy p,
mi_sbu_main_cd_m s,
mi_prcp_m e,
mi_mgt_cls_m b,
mi_mgt_cls_dtl a
where m.clm_no = c.clm_no
and c.pcy_no = p.pcy_no
and c.endt_srl = p.endt_srl
and c.endt_yr = p.endt_yr
and c.itm_no = p.itm_no
and m.sbu_main_cd = s.sbu_main_cd
and m.prcp_cd = e.prcp_cd
and c.bsc_typ_cd = a.bsc_typ_cd
and m.pcy_sub_typ_cd = a.pcy_sub_typ_cd
and a.mgt_cls_cd = b.mgt_cls_cd;

end;

Tom Kyte

Followup  

July 24, 2002 - 12:03 am UTC

you make it *really* hard when you don't include any information -- like for example -- versions of software, actual error message/code.....

Ok, so I go into "compiler" mode and say "hey, if I was an 8i database, running 8i plsql -- that analytic function row_number() would bug me and I would fail the compliation"

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3027089372477 <code>
for what is happening (if I guessed right)

btw: why do the delete dynamically? use static sql whenever possible.

SUN CHEN

July 24, 2002 - 12:24 am UTC

Reviewer: SUN CHEN from HK

Hi Tom,
Thank you for your reponse immediately.
why the following store procedure get errors:
CREATE OR REPLACE procedure mi_clm_dscv is
begin

execute immediate 'delete from te_mi_clm_mvnt';


insert into te_mi_clm_mvnt
(prcp_abbr,
clm_no,
ac_yr_mth,
chnnl_nm,
isrd_nm,
grs_rsrv_bf,
net_rsrv_bf,
grs_paid,
net_paid,
grs_rsrv_cf,
net_rsrv_cf,
grs_incrrd,
net_incrrd,
grs_paid_ytd,
net_paid_ytd,
clm_ind,
mgt_cls_desc,
rn_l,
rn_s)
select e.prcp_abbr,
m.clm_no,
m.ac_yr_mth,
s.chnnl_nm,
p.isrd_nm,
m.grs_rsrv_bf,
m.net_rsrv_bf,
m.grs_paid,
m.net_paid,
m.grs_rsrv_cf,
m.net_rsrv_cf,
m.grs_incrrd,
m.net_incrrd,
m.grs_paid_ytd,
m.net_paid_ytd,
m.clm_ind,
b.mgt_cls_desc,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd desc) rn_l,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd asc) rn_s
from mi_clm_mvnt m,
mi_clm_m c,
mi_clm_pcy p,
mi_sbu_main_cd_m s,
mi_prcp_m e,
mi_mgt_cls_m b,
mi_mgt_cls_dtl a
where m.clm_no = c.clm_no
and c.pcy_no = p.pcy_no
and c.endt_srl = p.endt_srl
and c.endt_yr = p.endt_yr
and c.itm_no = p.itm_no
and m.sbu_main_cd = s.sbu_main_cd
and m.prcp_cd = e.prcp_cd
and c.bsc_typ_cd = a.bsc_typ_cd
and m.pcy_sub_typ_cd = a.pcy_sub_typ_cd
and a.mgt_cls_cd = b.mgt_cls_cd

update te_mi_clm_mvnt
set rank_ind = 'Largest'
where rn_l <= 50;

update te_mi_clm_mvnt
set rank_ind = 'Smallest'
where rn_s <= 50;

update te_mi_clm_mvnt
set rank_ind = 'Others'
where rank_ind is null;
end;
/

The errors:
PLS-00103: Encountered the symbol "(" when expecting one of the following:

, from

I run it in oracle 817 and the sql can work if I remark the analyst function.Is that analyst function cann't be used in stored procedure? or others errors have occured? thanks....


order by rownum,x

August 13, 2002 - 5:43 am UTC

Reviewer: PN

Hi Tom
thanks for previous helps.
How to make use of Rownum in query to display Rownum,empno
in ASC order (without emp index ,with emp index)
Exapmle
SELECT ROWNUM,EMPNO FROM SCOTT.EMP
ORDER BY ROWNUM,EMPNO

output=
ROWNUM EMPNO
1 7369
2 7499
3 7521
4 7566
5 7654
6 7698
7 7500
8 5
9 2
10 1

We want as
output=
ROWNUM EMPNO
1 1
2 2
3 5
4 7369
5 7499
6 7500
7 7521
8 7566
9 7654
10 7698

Thanks
PN



Tom Kyte

Followup  

August 13, 2002 - 4:57 pm UTC

select rownum, empno
from ( select empno
from emp
order by empno );


sort AND then assign rownum.

Does rownum behave differently using RBO?

October 31, 2002 - 1:15 pm UTC

Reviewer: Kashif

Hi Tom,

you mention that if rownum <= n is used, the server will not expend the energy to get all rows and then return the 'n' rows as limited by rownum, but rather just get the 'n' rows requested. Does this work any differently when RBO is being used? TIA.

Kashif

P.S. I am unable to download from the link you have provided on your home page to your presentation on searchdatabase.com. (the 'download the slides from here (no sound)' link).

Tom Kyte

Followup  

October 31, 2002 - 9:16 pm UTC

It will work differently with different query plans (eg: you can get a different N rows back with different plans -- add an index and you might get a different N rows then without it).

So -- since the RBO might pick a different plan -- yes, the RBO and CBO might well return different answers depending on HOW rownum was used.

As for the PS -- its been downloaded 548 times and I haven't heard from anyone else having issues -- anyone else having problems?

rownum vs. distinct

March 12, 2003 - 4:55 pm UTC

Reviewer: VJ

Tom,

I was told that using rownum would be faster than using distinct in a query. Can you provide an example if this statement is correct or direct me?

Tom Kyte

Followup  

March 13, 2003 - 7:31 am UTC

rownum is not a replacement for distinct???


I don't know what you mean.

rownum vs. distinct

March 13, 2003 - 2:44 pm UTC

Reviewer: VJ

what i mean is that if a query is returning multiple rows with same values (duplicate)then use rownum =1 rather than use distinct in the query. But i got a doubt is that if i use rownum=1 then it is going to return only one row and not distinct values. So this question about rownum vs. distinct.
If it still doesnt drive a point...let just leave it!!!



Error on your page today 03-14-2003

March 14, 2003 - 12:29 pm UTC

Reviewer: vj

Hi Tom,

I saw this error message on your site today..

report error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I was gonna ask you this very same question today as i was getting this in one of my packages yesterday and saw this on your site. Can you tell me why this error?

rownum

May 09, 2003 - 6:53 pm UTC

Reviewer: mo

Tom:

I have a table of 900 stock numbers where stock number is PK. I am trying to get the order of a given stock number in the table meaning the position. What I did is :

select rownum from (select rownum,stock_number from stock_item order by stock_number)
where stock_number='MS208'

and it is still giving me rownum=1. How can I get the rownum for that stock number which is 800.

Thank you


Tom Kyte

Followup  

May 09, 2003 - 7:15 pm UTC

there is no such thing as the "positition in a table".


rownum is a fleeting number assigned to a row in a result set. It exists for the brief moment in time that result set does. There is no such thing as a row having a positition or place in a table.


You do not want what you ask for -- trust me.

rownum

May 09, 2003 - 10:50 pm UTC

Reviewer: mo

Tom:

1. I am sure there has to be a way to get a corresponding number for a value in a table. You say rownum would not work. is there anything else like a random number that can be assigned in incrementing order? I am trying to pass this number (x) to a javascript array value
'document.form.field[0].option['||x||'].selected = true

Basically I have a html for with a dropdown list for all stock numbers. The user can do a lookup where a child window is open and he enters a keyword and oracle does a search and pulls all matching records. Then he clicks on select for one stock number and I am assigning that number to the value in the dropdown on the parent page. I can not find a way in javascript to access the different values in the dropdown to find the location of the matching value so i am trying to get it in oracle.

Tom Kyte

Followup  

May 10, 2003 - 7:34 am UTC

if you say "i am sure", you would lose that bet.


rows having "order" is nonsense in a relational database.


If you assigned a sequence number to this (application data) -- you could use that, but to ask the database to assign a "number to a row" isn't going to happen.

Position in table

May 12, 2003 - 10:21 am UTC

Reviewer: Jim Tran from NJ USA

I believe that reviewer "Mo" wants to rewrite the query:

select rownum from
(select rownum,stock_number from stock_item
order by stock_number)
where stock_number='MS208'

as:

select RN from
(select rownum RN,stock_number from stock_item
order by stock_number)
where stock_number='MS208'

I think the confusion is that "rownum" in the outer query is NOT the same "rownum" in the inner query. The "rownum" in the outer query is an entirely new "rownum".


query

May 13, 2003 - 6:26 pm UTC

Reviewer: mo

Thanks

RN worked. I thought rownum on the outer were coming from the inner query but it was not.

August 12, 2003 - 2:29 am UTC

Reviewer: Helena Marková from Bratislava, Slovakia


rownum = 1 and rwnum < 2

January 28, 2004 - 12:25 pm UTC

Reviewer: A reader

Hi

I have this query which runs happily using rownum < 2 but slowly using rownum = 1


1 SELECT bo_id_contact, interaction_id, interact_begin,
2 TO_CHAR (interact_begin, 'YYYY-MM-DD-HH24.MI.SS'), interact_status
3 FROM sysadm.ps_ri_interaction partition(SYS_P692)
4* WHERE ROWNUM < 2

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2761 Card=1 Bytes=33)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'PS_RI_INTERACTION' (Cost=2761 Card=1057390 Bytes=34893870)

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

1 SELECT bo_id_contact, interaction_id, interact_begin,
2 TO_CHAR (interact_begin, 'YYYY-MM-DD-HH24.MI.SS'), interact_status
3 FROM sysadm.ps_ri_interaction partition(SYS_P692)
4* WHERE ROWNUM = 1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2761 Card=1 Bytes=33)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'PS_RI_INTERACTION' (Cost=2761 Card=1057390 Bytes=34893870)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18297 consistent gets
18165 physical reads
0 redo size
815 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


I think rownum = 1 is an incorrect use?

Tom Kyte

Followup  

January 28, 2004 - 2:35 pm UTC

something fishy -- can we see a tkprof with the real plans?

seems that cursor_sharing is the guilty boy

January 29, 2004 - 8:36 am UTC

Reviewer: A reader

Hi

cursor_sharing = FORCE change rownum = 1 into rownum = :"SYS_B_0"

seems like that is the problem!

which one is better ?

February 24, 2004 - 4:46 pm UTC

Reviewer: A reader

which one is better ?

select * from ( select id from t1 where name = v_name)
where rownum = 1;

or

select max(id) from t
where name= v_name;


(In the view "t" there are multiple rows with the "same name" and "same id" and I have to select only one of them)
eg. id name other_attr
1 peter x
1 peter y
2 john x
3 tom z
1 peter z

which one is faster ?

there can be 10 maximum repetation based on other_attr field. Total record in table are 4k

The explain plan shows only one difference

count stopkey vs. sort aggregate

please advise !!

Tom Kyte

Followup  

February 24, 2004 - 9:02 pm UTC

well, they are not comparable, they answer different questions

q1) give me a random ID where name = v_name
q2) give me the MAX id where name = v_name

they are differnet and cannot never ever be compared for performance based on that fact.

really ?

February 25, 2004 - 10:38 am UTC

Reviewer: A reader

Hi tom, Thanks for your reply.
First of all sorry for my mistake. the view name is wrong both the views are t (not t1 in one of the cases) so we quering against the same view t now.

I listed the view t below...

eg. id name other_attr
1 peter x
1 peter y
2 john x
3 tom z
1 peter z

if I query it by

select * from ( select id from t where name = 'peter')
where rownum = 1;

I will get 1

or

if I query it using..
select max(id) from t
where name= 'peter';

I will get 1 too..

so why aren't they compareable ?


please explain !!


Tom Kyte

Followup  

February 25, 2004 - 11:32 am UTC

ops$tkyte@ORA920PC> create table t1 ( id number, name varchar2(10) );
Table created.
 
ops$tkyte@ORA920PC> insert into t1 values ( 1, 'peter' );
1 row created.
 
ops$tkyte@ORA920PC> insert into t1 values ( 1000, 'peter' );
1 row created.
 
ops$tkyte@ORA920PC> select * from ( select id from t1 where name= 'peter')
  2  where rownum = 1;
 
        ID
----------
         1
 

ops$tkyte@ORA920PC> select max(id) from t1 where name = 'peter';
 
   MAX(ID)
----------
      1000
 


it is exactly as I said - query 1 returns a random ID for peter.  query 2 returns the max(id) for peter.

tell me -- which do you want?  that'll drive which query you use. 

once again...

February 25, 2004 - 12:34 pm UTC

Reviewer: A reader

sorry tom, I think I am not able to explain you the senerio or something.

the objective of this query is to get single row out of multiple rows with the same (id,name) pair...

eg. id name other_attr
1 peter x
1 peter y
2 john x
3 tom z
1 peter z

if you see this table.

peter will have ONLY one ID that is 1 ever. but multiple occurance of (id,name) pair
John will have only one ID that is 2 ever. but multiple occurance of (id,name) pair

let's put it in other way
consider there is another table "user" where id and username are part of the pk . T is "depandent table" on users. so pk(id,name) pair is migrated from users (fk) into t and is part of the pk in "T"

in this case name and id pair is unique. so peter will never have id = 1000 peter will always have id = 1 but , basted on the other_attr, there can be a redudancy,
=> same values of (id,name) pair will be repeated with the different values of other_attr.

so, it will not be a random number but always same id.
so, inthe result set there will be multiple rows with SAME
(id,name) pair, but we are selecting the first one returned.
in rownum technique
where as we are selecting the maximum (of the same id's)
form group of records with the same ids just to get the 1 row out of multiple rows

Hope this will make it clearer...:)



Tom Kyte

Followup  

February 25, 2004 - 12:41 pm UTC

given all of those additional constraints, just

select id from t where name = 'peter' and rownum = 1;

is sufficient.

Thanks you...

February 25, 2004 - 1:28 pm UTC

Reviewer: A reader

cool. I don't know why I was doing all other things.. this is cool. I should have done this in the first place.. my bad. you are the man !!

Materialized Views created using ROWNUMS

May 13, 2004 - 2:32 pm UTC

Reviewer: Suren Dharanikota from Milwaukee,WI

Hello Tom,
It was very nice meeting you at IOUG-04,Toronto - CN

I am facing a strange problem during the refresh process on the materialized views.

When the refresh is run for the first time it shows as successfully completed and when we do a select count(*) on the view it shows " 0 rows ". But when the same view is ran
for the second time it UPDATES successfully and the total rows are displayed.

Here is the syntax for the Materialized View.

CREATE MATERIALIZED VIEW MATVIEW.MV2_XYZ
PCTFREE 0
PCTUSED 40
MAXTRANS 255
TABLESPACE MATVIEW_DATA
NOLOGGING
STORAGE(INITIAL 16M
PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS SELECT * FROM fiowner.xyz@linkdb
WHERE ROWNUM >0
/

SOURCE DATABASE:
----------------

Master Database:
SQL*Plus: Release 9.2.0.4.0 - Production on Fri May 7 11:05:37 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select count(*) from xyz;
COUNT(*)
----------
5811420
SQL>

Refresh/Materialized View Database:
-----------------------------------
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> set time on;
10:54:45 SQL> set timing on;
10:54:49 SQL> set serveroutput on;
10:54:58 SQL> exec dbms_mview.refresh('MATVIEW.MV2_XYZ','C');
PL/SQL procedure successfully completed.
Elapsed: 00:06:38.84
11:01:59 SQL> select count(*) from mv2_XYZ;
COUNT(*)
----------
0
Elapsed: 00:00:00.01
11:02:28 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
11:04:54 SQL> select count(*) from mv2_xyz;
COUNT(*)
----------
0
Elapsed: 00:00:00.01
11:05:03 SQL> select count(*) from xyz@dblink;
COUNT(*)
----------
5811420
Elapsed: 00:00:18.77
11:07:54 SQL>

It is not the only Materialized View we are having problems with, There are a couple more. Two of them are always common and a couple random.

Note*: The source tables are Partitioned tables. These are used as Rollup tables for the ETL process(using SQL-LOADER) and these same tables are used for refreshing the Materialized views once the Nightly load finishes.

We did not face any problems while we were in Oracle 8i on 8.1.7.4, but these problems are showing up every now and then since we moved to 9.2.0.4

I have logged a TAR with Oracle and they said i might be hitting a bug# 2980403. I installed the fix and was successfully updating the rows for the MVs untill two days.
But today we ran into the same problem.

Now Oracle Support says that, The defining query of the materialized view cannot contain any non-repeatable expressions like ROWNUM, SYSDATE etc + all the source tables "SHOULD HAVE PRIMARY KEY DEFINED" for creating the Materialized Views, without which they are saying that they cannot even accept a test case.

1) Is this true about what Oracle says ? What is the harm in using the condition where rownum > 0 in the create Materialized View statement ?

2) If that is true, then the complete refresh for all the tables which now take around like 45 minutes will take about 2 to 3 hours with the primary keys on it as it has to validate all the rowns in the tables.Added to that some of the tables do not have primary keys defined as they are rollup tables. 

Is there any other way for us to create the Materialized views without the primary keys ?

Your help is greatly appreciated.

Thanks,
Suren


 

Tom Kyte

Followup  

May 13, 2004 - 3:28 pm UTC

what's up with "where rownum > 0"?

1) rownum would have very wacky, unpredicable side effects yes. when I saw it and even before reading the rest of the text, little red flags popped up all over the place.

as for the primary key -- thats not correct. you can use rowids as well.


what is the purpose of the rownum????

Spelling numbers in spanish

May 13, 2004 - 4:18 pm UTC

Reviewer: Julian Ortiz from Colombia

Hello, I Modified the function for "SPANISH" language:



function spell_number_espa( p_number in number )
return varchar2
as
begin
return replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace(
lower( spell_number( p_number ))
, 'duodecillion' , 'duodecillon' )
, 'quintillion' , 'quintillon' )
, 'thousand' , 'mil' )
, 'hundred' , 'cien' )
, 'ninety' , 'noventa')
, 'eighty' , 'ochenta' )
, 'seventy' , 'setenta' )
, 'sixty' , 'sesenta' )
, 'fifty' , 'cincuenta' )
, 'forty' , 'cuarenta' )
, 'thirty' , 'treinta' )
, 'twenty' , 'veinte' )
, 'nineteen' , 'diecinueve' )
, 'eighteen' , 'dieciocho' )
, 'seventeen' , 'diecisiete' )
, 'sixteen' , 'dieciseis' )
, 'fifteen' , 'quince' )
, 'fourteen' , 'catorce' )
, 'thirteen' , 'trece' )
, 'twelve' , 'doce' )
, 'eleven' , 'once' )
, 'ten' , 'diez' )
, 'nine' , 'nueve' )
, 'eight' , 'ocho' )
, 'seven' , 'siete' )
, 'five' , 'cinco' )
, 'six' , 'seis' )
, 'four' , 'cuatro' )
, 'three' , 'tres' )
, 'two' , 'dos' )
, 'one' , 'uno' )
, 'zero' , 'cero' )
, '-' , ' y ' )
, 'veinte y ' , 'veinti')
, 'sediezta' , 'setenta')
, 'uno cien ' , 'ciento ' )
, 'uno cien' , 'cien' )
, 'dos cien' , 'docientos' )
, 'tres cien' , 'trescientos' )
, 'cuatro cien' , 'cuatrocientos' )
, 'cinco cien' , 'quinientos' )
, 'seis cien' , 'seicientos' )
, 'siete cien' , 'setecientos' )
, 'ocho cien' , 'ochocientos' )
, 'nueve cien' , 'novecientos')
, ' uno mil ' , ' un mil ' )
, 'uno mil ' , 'mil ' )
, ' million ' , ' millones ' )
, 'uno millones' , 'un millon' )
, 'ciento millones', 'cien millones')
, ' billion ' , ' billones ' )
, 'uno billones' , 'un billon' )
, 'ciento billones', 'cien billones')

, ' trillion ' , ' trillones ' )
, 'uno trillones' , 'un trillon' )
, 'ciento trillones', 'cien trillones')

, ' quadrillion ' , ' cuatrillones ' )
, 'uno cuatrillon' , 'un cuatrillon' )
, 'ciento cuatrillones', 'cien cuatrillones')

, ' quintillon ' , ' quintillones ' )
, 'uno quintillones' , 'un quintillon' )
, 'ciento quintillones', 'cien quintillones')

;
end spell_number_espa;


Materialize Views with ROWNUMS

May 13, 2004 - 5:16 pm UTC

Reviewer: Suren Dharanikota from Milwaukee, WI

Thanks Tom !!!

We are testing the Creation and Refresh of MVs using rowids with complete refresh and ON DEMAND options. So far a couple of views we tested are working fine.

We would be testing more and do some benchmarks. The rownum > 0 option was used to find out if the refresh process was more faster than the ones created with Primary Keys. It not only was "NOT" creating the Primary Keys but in fact was much faster. As there were no problems we had to move along with them.

But now, we are retracting from using the "ROWNUMS".

One final question:
1) Can you suggest any other methods that could be used in the Exec dbms_mview.refresh(parameters) that can make the refresh complete faster ?

Regards,
Suren


Tom Kyte

Followup  

May 14, 2004 - 9:51 am UTC

1) that is a truncate plus insert /*+ APPEND */ of the defining query -- as long as the snapshot is not in a snapshot group.

So, make the defining query faster. see what it is waiting on (use a 10046 level 12 trace with a refresh, analyze the tkprof (if 9i, you'll see waits, if 8i, you'll have to review the trace to see the waits) and see if you can remove the waits.

if part of a snapshot group, it'll be delete plus insert -- consider that. not as fast as truncate plus insert append.

sorting by the index

June 14, 2004 - 6:46 pm UTC

Reviewer: Fernando Sánchez from Madrid, Spain

Hello.

I've been told it is possible sorting a result set without the 'order by', simply by using a unique index clause but I can't make it work.

Maybe it works for other rdms but I really don't know.

I had never to be worried about execution plans but it seems I have to begin with it.

This is what I'm trying:

CSR>select /*+INDEX(ir_tb_env_clts,IN_PK_ENV_CLTS)*/
2 CO_MSDN_V, CO_IMSI_V, SQ_ECL_01, FX_FECH_ENV_D
3 from ir_tb_env_clts
4 where CO_MSDN_V >= '35899995000'
5 and FX_FECH_ENV_D >= '01/01/2004'
6 and CO_IMSI_V >= '10000000000000'
7 and SQ_ECL_01 > 0
8 and rownum <=10;

CO_MSDN_V CO_IMSI_V SQ_ECL_01 FX_FECH_
------------------ --------------- ---------- --------
35899997758 100000000002758 11224 12/06/04
35899997759 100000000002759 11223 12/06/04
35899997760 100000000002760 11222 12/06/04
35899997761 100000000002761 11221 12/06/04
35899997762 100000000002762 11220 12/06/04
35899997763 100000000002763 11219 12/06/04
35899997764 100000000002764 11218 12/06/04
35899997765 100000000002765 11217 12/06/04
35899997766 100000000002766 11216 12/06/04
35899997767 100000000002767 11215 12/06/04


10 filas seleccionadas.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=42)
1 0 COUNT (STOPKEY)
2 1 PARTITION RANGE (ITERATOR)
3 2 INDEX (RANGE SCAN) OF 'IN_PK_ENV_CLTS' (UNIQUE) (Cost=
5 Card=1 Bytes=42)


But I wanted was this:

CSR>select *
2 from (select /*+INDEX(ir_tb_env_clts,IN_PK_ENV_CLTS)*/
3 CO_MSDN_V, CO_IMSI_V, SQ_ECL_01, FX_FECH_ENV_D
4 from ir_tb_env_clts
5 where CO_MSDN_V >= '35899995000'
6 and FX_FECH_ENV_D >= '01/01/2004'
7 and CO_IMSI_V >= '10000000000000'
8 and SQ_ECL_01 > 0
9 order by CO_MSDN_V, CO_IMSI_V, SQ_ECL_01, FX_FECH_ENV_D)
10 where rownum <=10;

CO_MSDN_V CO_IMSI_V SQ_ECL_01 FX_FECH_
------------------ --------------- ---------- --------
35899995000 100000000000000 13982 13/06/04
35899995001 100000000000001 13981 13/06/04
35899995002 100000000000002 13980 13/06/04
35899995003 100000000000001 13984 13/06/04
35899995003 100000000000003 13979 13/06/04
35899995004 100000000000004 13978 13/06/04
35899995005 100000000000005 13977 13/06/04
35899995006 100000000000006 13976 13/06/04
35899995007 100000000000007 13975 13/06/04
35899995008 100000000000008 13974 13/06/04

10 filas seleccionadas.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=42)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=7 Card=1 Bytes=42)
3 2 SORT (ORDER BY STOPKEY) (Cost=7 Card=1 Bytes=42)
4 3 PARTITION RANGE (ITERATOR)
5 4 INDEX (RANGE SCAN) OF 'IN_PK_ENV_CLTS' (UNIQUE) (C
ost=5 Card=1 Bytes=42)


The problem with this second query is that when the table is full enough it takes a lot more the application can afford, that's why I was trying the trick I was told.

Two more questions: what does STOPKEY mean? can it me help me somehow?



Thanks in advance.

Tom Kyte

Followup  

June 15, 2004 - 8:32 am UTC

you have been LIED TO.

you have been misled.

you would be mistaken in trying to get sorted data without using an order by.


YOU MUST, REPEAT MUST, use order by if you want sorted data.

there are NO if's, and's or buts about it.

you want sorted data, you use order by. period.

Returns from start_row to end_row

June 16, 2004 - 4:16 pm UTC

Reviewer: Jennifer Chen from Viena, VA USA

Hi Tom,

We need a stored procedure that accepts start_row and end_row parameters and returns the values of these row.

Since I can't use rownum between start_row and end_row, I couldn't think of a way that using sql not pl/sql to accomplish this.

What do you think?

Thanks in advance for your time and help.

Tom Kyte

Followup  

June 16, 2004 - 4:22 pm UTC

select *
from (select a.*, rownum r
from (select * from t order by...) a
where rownum <= :end )
where r >= :strt

Thank you

June 16, 2004 - 9:36 pm UTC

Reviewer: Jennifer Chen from Viena, VA USA

That’s so brilliant, Tom!

This site is much more helpful and responsive compare to Metalink and OTN technical forums…

Thank you.


Generating additional records using rownum

December 21, 2004 - 10:50 am UTC

Reviewer: Praveen from Bangalore

Hi Tom,

I have a table t(id integer, num integer), which contains 3 records (1, 3), (2, 4), (3, 6). Is it possible to generate duplicate records for each id so that total number of duplicate records for each id is determined by that id's num column.

For example:

create table t (id integer, num integer)

insert into t values(1,3);

insert into t values(2,4);

insert into t values(3,6);

And the output looks like:

rownum, id, num
---------------
1 1 3
2 1 3
3 1 3
---
1 2 4
2 2 4
3 2 4
4 2 4
---
1 3 6
2 3 6
3 3 6
4 3 6
5 3 6
6 3 6
-------------
The query
"select t.* from t, all_objects a where rownum <= t.num"
is giving incorrect output. But I think table t has to join with a large table and records are limited based on rownum. Is it possible without using analytical queries?

Thanks and regards

Praveen

Tom Kyte

Followup  

December 21, 2004 - 1:09 pm UTC

ops$tkyte@ORA9IR2> create or replace type myTabletype as table of Number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function generate_rows( p_n in number ) return myTableType
  2  pipelined
  3  is
  4  begin
  5          for i in 1 .. p_n
  6          loop
  7                  pipe row(i);
  8          end loop;
  9          return;
 10  end;
 11  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( id int, num int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 3 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 2, 4 );
 
1 row created.
 
ops$tkyte@ORA9IR2> select *
  2    from t, table( generate_rows(t.num) )
  3  /
 
        ID        NUM COLUMN_VALUE
---------- ---------- ------------
         1          3            1
         1          3            2
         1          3            3
         2          4            1
         2          4            2
         2          4            3
         2          4            4
 
7 rows selected.
 

Ref: Generating additional records using rownum

December 23, 2004 - 1:07 am UTC

Reviewer: Praveen from Bangalore

Hi Tom,

Thankyou very much for the solution. At the same time, the new feature you taught me, "pipe", is quite astonishing. I am currently experimenting with this. However, I would like to know, which would perform better, for the given problem - a single query written using analytical functions (if it is possible) or the above solution you provided?

Thanks, Tom

Praveen.

Tom Kyte

Followup  

December 23, 2004 - 11:12 am UTC

analytics don't apply, analytics cannot "make up records".

January 04, 2005 - 3:40 pm UTC

Reviewer: A reader

All of these info is very helpfull. Thanks.

I am dealing with a small variation. Our tables consist of multi million rows. We need to do distinct and we want to get exactly N rows back, say 10. Note: First 10 distinct rows, not distinct of first 10. We used rownum < 10, it is always applied first and then the distinct is done on the 10 records.

I know

A)select * from (select distinct * from x) where rownum < 10

will work. But I am worry about performance. Would this performance better..

B) select a, b from x where rownum < 10 group by a, b

My objective is to find a way that get me the first 10 distinct records and then stop, do you know which query achieve that quickest?

Thanks

Tom Kyte

Followup  

January 05, 2005 - 9:01 am UTC

what could the possible business case for this be? seems very strange.

what is the client going to do with these? what is the client?

probably easiest to just open a cursor as a full scan on the table and let the client pull records until it gets 10 unique ones.

January 05, 2005 - 11:20 am UTC

Reviewer: A reader

We are adding support of ORACLE for our product application. We have this available in DB2 as

select distinct * from x fetch first 10 rows;

All we are trying to do is support the same functionlity without minimal hit in performance. All I need to know is which way is the best to achieve that requirement.

Thanks

Tom Kyte

Followup  

January 05, 2005 - 11:33 am UTC

select * from (select distinct * from x) where rownum <= 10;

but, that's going to get the table, distinct it and then return 10 rows, pretty much what db2 would have to do as well.

why not

January 05, 2005 - 11:43 am UTC

Reviewer: Matthias Rogel from Kaiserslautern, Germany

probably easiest to just open a cursor as a full scan on the table and let the
*server* pull records until it gets 10 unique ones (use ptf)
?

Tom Kyte

Followup  

January 05, 2005 - 12:27 pm UTC

ptf?

OK

January 05, 2005 - 12:40 pm UTC

Reviewer: Kumar from Pune,India

Hi Tom,
I tried to sort rows by sal desc. and dynamically get
rows by passing rownum.But it is not working properly.How
to correct this?

select * from(
select rownum,a.* from (select * from emp order by sal desc)a
)
where rownum = &n


Tom Kyte

Followup  

January 05, 2005 - 12:55 pm UTC

select * from(
select rownum R,a.* from (select * from emp order by sal desc)a
)
where R = &n

Confusing about rownum

January 05, 2005 - 2:04 pm UTC

Reviewer: Gerald

Hello,Tom,
In your preevious example:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(object_id);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from ( select owner, object_name, object_id
3 from t
4 order by object_id desc )
5 where rownum < 10;

OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
OPS$TKYTE T 23087
OPS$TKYTE MD5STRING 23086
OPS$TKYTE MD5 23085
OPS$TKYTE THE_OLD_IN_OUT 23084
OPS$TKYTE STRING_ARRAY 23083
OPS$TKYTE ArrayDemo 23082
OPS$TKYTE ArrayDemo 23081
A T 23078
OPS$TKYTE PRINT_TABLE 23048

9 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T'
4 3 INDEX (FULL SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
936 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
i don't understand :if object_id is an index ,so why i can'not do like this:

select owner, object_name, object_id
from t
order by object_id desc
where rownum < 10;

if object_id is an index and i have order by object_id desc
so it will access the index in descending order and then bo rowid from index get to the row and creates the rownum.
Am i right? Clarify me please the point.


Tom Kyte

Followup  

January 05, 2005 - 7:08 pm UTC

because ROWNUM is special like that.

rownum is assigned to a row as it flows out of the where clause (else you would even be able to where on it)

and where clauses happen BEFORE ordering


so, that would get the first 10 rows and sort them, we wanted the first 10 rows AFTER sorting.


You cannot *rely* on it using an index, it can and will and is free to NOT use the index. do not rely on data being in any order without an order by.

Continue rownum

January 06, 2005 - 12:59 am UTC

Reviewer: Gerald

Hello,Tom,
If i add where object_id>7 for example and object_id is an index :
select owner, object_name, object_id
from t
where object_id>7 and rownum < 10

do i still need an order clause and make SQL like this :

select * from (select owner, object_name, object_id
from t
where object_id>7 order by object_id) where rownum < 10

or Oracle will get through the index and bring me 9 rows ordered by object id?


Tom Kyte

Followup  

January 06, 2005 - 10:39 am UTC

you cannot rely on any index EVER being used.

if you need data sorted, SORT IT.

if you want the first 10 object ids that are greater than 7 then you need to:


select * from t where object_id > 7 order by object_id


and get the first N rows.

select * from (THAT_QUERY) where rownum <10;


unless your query includes an order by, you cannot say ANYTHING about the order of the rows.

ptf = pipelined table function

January 06, 2005 - 3:54 am UTC

Reviewer: Matthias Rogel from Kaiserslautern, Germany

sorry for my lazyness not typing three words.

I meant, "pull records until you get n unique ones" is a thing
the server can also do as good as the client.

Please have a look at this example
( I am a bit shocked about it, because it lets me think that
  Rule No. 1 "whatever you can do in SQL, do in SQL, don't use PL/SQL"
  isn't worth too much )

SQL> describe bigtable
 Name                                      Null?    Typ
 ----------------------------------------- -------- --------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(40)

SQL> set timi on
SQL> select * from (select distinct * from bigtable) where rownum<=10;

...

10 Zeilen ausgewõhlt.

Abgelaufen: 00:04:21.00
SQL> select count(*) from bigtable;


  COUNT(*)
----------
 165330211

1 Zeile wurde ausgewõhlt.

Abgelaufen: 00:02:30.02

REM
REM now with ptf 
REM

create type btt as object (
  col1 number,
  col2 varchar2(40));
/  
  
create type bttt as table of btt;  
/

create or replace function first_n_distinct_rows(n integer) 
return bttt pipelined as
bta bttt;
sofar integer;
i integer;
doex boolean;
begin
   bta := bttt();
   sofar := 0; doex := true;
   
   for b in (select * from bigtable) loop
      exit when sofar = n;
      
      if doex then bta.extend; end if;
      
      bta(bta.count) := btt(b.col1, b.col2);
      
      select count(*)
      into i
      from (select distinct * from table(bta));
      
      if i > sofar then
         sofar := i;
         doex := true;
      else
         doex := false;
      end if;
      
   end loop;
   
   for j in 1..bta.count loop
      pipe row (bta(j));
   end loop;
   
   return;
   
end first_n_distinct_rows;
/


SQL> select * from table(first_n_distinct_rows(10));
...
10 Zeilen ausgewõhlt.

Abgelaufen: 00:00:00.00


So, using PL/SQL (ptf) seems to me much more reasonable
(at least for "small" n's) than using pure SQL.

Question:

Why Optimizer doesn't use same strategy as first_n_distinct_rows ?

(Are you sure DB2-Optimizer doesn't ?)



 

Tom Kyte

Followup  

January 06, 2005 - 10:49 am UTC

I literally had no clue what ptf was.

for you see, the words "pipelined table function" appear on this page -- well, only when you typed them in.

"pipelined table function" is not what they are normally referred to as

ptf made no sense at all.

in technology, in talking, especially when we all use different languages and such -- being clear, being precise, being understandable, well, that is mandatory.

the first N distinct rows is something optimizers wouldn't "recognize", I believe you would be 1 of 1 people needing such a 'feature'.




Follow rownum

January 06, 2005 - 2:48 pm UTC

Reviewer: Gerald

Hi,Tom,

You wrote above:
you cannot rely on any index EVER being used.

if you need data sorted, SORT IT.

if you want the first 10 object ids that are greater than 7 then you need to:


select * from t where object_id > 7 order by object_id


and get the first N rows.

select * from (THAT_QUERY) where rownum <10;
object_id is index;

but you gave above the example with index object_id
select * from (select object_id order by object_id) where rownum <10;
and in explain plan is written 9 rows gets , but i didn't understand why-the inner query (select object_id order by object_id) should have brought all the records-so if i write the query in a such way how the index could affect the performance?
Thank's
Gerald







Tom Kyte

Followup  

January 06, 2005 - 7:03 pm UTC

indexes *can* affect performance, however you cannot RELY on an index access happening, therefore to get the correct answer you MUST ORDER BY.


IF you code
select * from (select object_id order by object_id) where rownum <10;

AND there is an index on object_id)

THEN the optmizer will MOST LIKELY use it to do the "sort" and it'll be super fast...


but if you just coded:

select * from (select object_id from t where object_id > 7 ) where rownum < 10;

AND MOST rows were > 7 (as they are), then a FULL SCAN could be the plan of choice -- and you would get seriously burnt.

98 not 99 !!

January 07, 2005 - 2:56 am UTC

Reviewer: Rajesh from Hyderabad , India

Quoting Tom's first response

"and we said...

It depends. If we can get 99 rows and then stop -- yes. For example, if T is a
table:

select * from t where rownum < 99;

will get just the first 99 rows and stop -- it'll not expend the engery to get
all of the rows in T."

the above query would return you first 98 rows, not first 99 rows!

Follow rownum

January 07, 2005 - 9:43 am UTC

Reviewer: Gerald

Hi,Tom,

In the above example how the optimizer will use index to the sort?Will optimizer read first of all the index and then fetchs the row by rowid( will read the data twice)
and will not order the records after fetching?

select * from (select object_id order by object_id) where rownum <10;

AND there is an index on object_id)
THEN the optmizer will MOST LIKELY use it to do the "sort" and it'll be super
fast...




Tom Kyte

Followup  

January 07, 2005 - 9:46 am UTC

do you have "Effective Oracle by Design"? i go over rownum in depth there.


the optimizer would index range scan the index, which is sorted, and just reads the first 9 entries in it -- stopping, not reading the entire thing (that is the count stopkey entries you see in the plans)



Rownum

January 07, 2005 - 10:17 am UTC

Reviewer: Gerald

Tnank's, Tom,
Why we have to be sure that in the result set of the inner query the rownum is like :

ROWNUM OBJECT_ID
====== =========
1 5
2 6
3 7
4 8
As i understood the result set of the inner query is also a table so Oracle may fetch the rows not by how they are ordered?
select * from (select object_id order by object_id) where rownum <10;
Thank's.
Gerald





Tom Kyte

Followup  

January 07, 2005 - 10:27 am UTC

you've got the order by in there, they are returned that way and then rownum is assigned to it. this is documented as a "top-n" query. it is the way it works.


</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements6a.htm#33158 <code>

January 13, 2005 - 3:31 pm UTC

Reviewer: TS from USA

Tom,

The below query takes anywhere between 26 secs to
1 minute to execute.

table1 has 3.5 million rows
table2 has 2000 rows

select symbol,tdate,price from
(select c.symbol SYMBOL,b.tdate TDATE,b.price PRICE
FROM table1 b,table2 c
where c.inst_id=b.inst_id
and c.symbol='ABCD'
order by b.tdate desc) a
where rownum < 2000

there is a concatenated index for
inst_id and tdate on table1(b) and
an index for b.tdate.

tkprof output:-
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 135 14.97 42.69 13187 24358 813 1999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 138 14.99 42.70 13187 24358 813 1999

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY
0 VIEW
0 SORT ORDER BY STOPKEY
0 TABLE ACCESS BY INDEX ROWID table1
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID table2
0 INDEX RANGE SCAN FUTURES_KEY (object id 6392)
0 INDEX RANGE SCAN IDX_idx_table1_1 (object id 6401)

idx_table_1 is a concatenated index on INST_ID and TDATE

Is there a way to rewrite this query to make
it run faster?

Any help would be appreciated.





Tom Kyte

Followup  

January 13, 2005 - 3:46 pm UTC

can you get the rows in the row source operation to be filled in.

(eg: run the query in sqlplus and EXIT sqlplus and then run tkrprof)

January 13, 2005 - 4:14 pm UTC

Reviewer: TS from USA

Tom,

Here's the tkprof output with rows filled in.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 135 10.39 18.67 909 17133 352 1999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 137 10.42 18.69 909 17133 352 1999

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1999 COUNT STOPKEY
1999 VIEW
1999 SORT ORDER BY STOPKEY
359833 TABLE ACCESS BY INDEX ROWID table1
359835 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID table2
1 INDEX RANGE SCAN FUTURES_KEY (object id 6392)
359833 INDEX RANGE SCAN IDX_idx_table1_1 (object id 7965)


Tom Kyte

Followup  

January 13, 2005 - 5:04 pm UTC

problem is you want to order by b.tdate, but the predicate is on c.  so, if we use index for predicate, we cannot for sort and vice versa.

so, can you tell me - is SYMBOL unique in table2 C?

if so, perhaps

ops$tkyte@ORA9IR2> create table table1( tdate date, price number, inst_id number );
 
Table created.
 
ops$tkyte@ORA9IR2> create index t1_idx on table1(inst_id,tdate);
 
Index created.
 
ops$tkyte@ORA9IR2> create table table2( inst_id number, symbol varchar2(5) );
 
Table created.
 
ops$tkyte@ORA9IR2> create index t2_idx on table2(symbol);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable symb varchar2(5)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select *
  2    from (  select /*+ FIRST_ROWS */ :symb, tdate, price
  3              from table1
  4             where inst_id = (select inst_id
  5                                from table2
  6                               where symbol = :symb )
  7             order by inst_id DESC, tdate DESC )
  8   where rownum < 2000;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=24)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=1 Bytes=24)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (Cost=1 Card=1 Bytes
   4    3         INDEX (RANGE SCAN DESCENDING) OF 'T1_IDX' (NON-UNIQUE) (Cost=2 
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=1 Card=1 By
   6    5             INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Car 
 
 
ops$tkyte@ORA9IR2> set autotrace off


that'll be as good as it gets. 

How does the ROWNUM works on joins ?

February 21, 2005 - 4:52 am UTC

Reviewer: Purushotham from Hyderabad, India

Hi Tom,

We have two tables MTL_SYSTEM_ITEMS_B and MTL_ITEM_REVISIONS_B with 1.3 Million and 2.19 Million respectively And below is the query I executed

SQL> select count(*) from mtl_system_items_b where item_catalog_group_id = 515 and organization_id = 204

------------
205061


SQL > select count(*) from mtl_item_revisions_b where inventory_item_id in (
                                      select inventory_item_id from mtl_system_items_b where item_catalog_group_id = 515 and organization_id = 204 )

-------------
305067


Indexes Used (Non-unique)  
-------------------
MTL_SYSTEM_ITEMS_B_N1     ( ORGANIZATION_ID,SEGMENT1)
MTL_SYSTEM_ITEMS_B_N6     (ITEM_CATALOG_GROUP_ID)
MTL_ITEM_REVISIONS_B_N1  (INVENTORY_ITEMD_ID,ORGANIZATION_ID)


The below query is used to find if there are 2000 and more records (just a UI requirement to disable the order by options) and then fire the 
same query (without count(*) OVER() and ROWNUM filter clause) again to fetch the records with order by or without it based on the rows. 

SELECT *
FROM
 ( 
  SELECT  
          I.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID_B
    , I.ORGANIZATION_ID AS ORGANIZATION_ID_B
    , 'V1' AS ORGANIZATION_CODE
    , I.ITEM_CATALOG_GROUP_ID AS  ITEM_CATALOG_GROUP_ID
    , IR.REVISION_ID AS REVISION_ID
    , IR.REVISION AS   REVISION
    , (SELECT ICGKFV.CONCATENATED_SEGMENTS  
        FROM
            MTL_ITEM_CATALOG_GROUPS_B_KFV     ICGKFV 
         WHERE 
            ICGKFV.ITEM_CATALOG_GROUP_ID =I. ITEM_CATALOG_GROUP_ID )  AS CATALOG_GROUP
        , (SELECT  ITL.DESCRIPTION  
         FROM   
            MTL_SYSTEM_ITEMS_TL ITL 
        WHERE 
            ITL.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID  
            AND ITL.ORGANIZATION_ID = I.ORGANIZATION_ID 
            AND ITL.LANGUAGE =  USERENV('LANG') 
        )  AS DESCRIPTION
     IR.REVISION_LABEL AS REVISION_LABEL
     ,I.SEGMENT1 AS ITEM_NUMBER
    ,COUNT(*) OVER() AS CNT
   FROM 
    MTL_SYSTEM_ITEMS_B I
    ,MTL_ITEM_REVISIONS_B IR 
   WHERE  
    I.ORGANIZATION_ID   = 204 
    AND I.ITEM_CATALOG_GROUP_ID IN ( 515) 
    AND IR.ORGANIZATION_ID =  I.ORGANIZATION_ID 
    AND IR.INVENTORY_ITEM_ID =  I.INVENTORY_ITEM_ID 
    AND IR.EFFECTIVITY_DATE IN 
                (
            SELECT FIRST_VALUE(IR2.EFFECTIVITY_DATE) OVER  (ORDER BY IR2.EFFECTIVITY_DATE DESC) 
            FROM MTL_ITEM_REVISIONS_B IR2 
            WHERE   IR2.ORGANIZATION_ID =   IR.ORGANIZATION_ID 
                              AND IR2.INVENTORY_ITEM_ID =  IR.INVENTORY_ITEM_ID 
                      AND IR2.EFFECTIVITY_DATE <=  SYSDATE AND
                     IR2.IMPLEMENTATION_DATE IS NOT NULL 
            ) 
        AND 1 = 1 AND ROWNUM < 2001) 
/

The rowsource plan is

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      6.37       9.15       9002      24620          7          31
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      6.41       9.19       9002      24620          7          31

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 316  


Rows     Row Source Operation
-------  ---------------------------------------------------
     31  VIEW  
     31   WINDOW BUFFER 
   2000    COUNT STOPKEY 
   2000     TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B 
   4000      NESTED LOOPS  
   2000       TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 
   2000        BITMAP CONVERSION TO ROWIDS 
      1         BITMAP AND  
      2          BITMAP CONVERSION FROM ROWIDS 
  59228           SORT ORDER BY 
 205064            INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N6 (object id 265284)
      3          BITMAP CONVERSION FROM ROWIDS 
 100418           SORT ORDER BY 
1066910            INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N1 (object id 607654)
   2000       INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)
   2000        FILTER  
   2000         WINDOW SORT 
   2000          TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B 
   2000           INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)


   I understand this plan. But when we ran this with the performance team they said we need to disable the bitmap conversions in the db.
   After turning off _b_tree_bitmap_plans I ran the same query.  Here is the tkprof output for the same 


   call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.03          5         16          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      135      0.35       0.40        442      30410          0        2000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      137      0.39       0.43        447      30426          0        2000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 316  

Rows     Row Source Operation
-------  ---------------------------------------------------
   2000  VIEW  
   2000   WINDOW BUFFER 
   2000    COUNT STOPKEY 
   2000     TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B 
   4000      NESTED LOOPS  
   2000       TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 
   2003        INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N6 (object id 265284)
   2000       INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)
   2000        FILTER  
   2012         WINDOW SORT 
   2015          TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B 
   2015           INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)





Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1344 Card=1 Bytes=2555)
   1    0  VIEW (Cost=1344 Card=1 Bytes=2555)
   2    1    WINDOW (BUFFER) (Cost=1344 Card=1 Bytes=77)
   3    2      COUNT (STOPKEY)
   4    3        TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_REVISIONS_B' 
   5    4          NESTED LOOPS (Cost=1344 Card=1 Bytes=77)
   6    5            TABLE ACCESS (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_B' 
   7    6              INDEX (RANGE SCAN) OF 'MTL_SYSTEM_ITEMS_B_N6' 
   8    5            INDEX (RANGE SCAN) OF 'MTL_ITEM_REVISIONS_B_N1' 
   9    8             FILTER
  10    9              WINDOW (SORT) (Cost=17 Card=1 Bytes=26)
  11   10               TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_REVISIONS_B' 
  12   11                  INDEX (RANGE SCAN) OF 'MTL_ITEM_REVISIONS_B_N1' 



I have too many questions I am desepartely trying to understand couple of things 

1. The query (though is used to find out the count) in fact retrieves values from more than 2 tables (columns do a select query). However I don't see them in the plan. I am quite astonished that oracle knows what I am doing do restricts to only two tables ?  I can see those tables in the plan if we remove the COUNT(*) OVER()

2. The plan 1 looks fine to me. As, it first scans the records in MTL_SYSTEM_ITEMS table though using (N6 & N1) to get the inventory_item_id and then join with the MTL_REVISIONS table to get the current revision label. The row scan numbers match your expectations. 

 3. However after turning off bitmap conversion options I am pretty much confused
    a)  CBO scans MTL_ITEM_REVISIONS_B_N1 and MTL_SYSTEM_ITEMS_B_N6. Will it ensure that 2000 items scaned in REVISIONS table will be of item_catalog_group 515  ? I really doubt this.

    b) The ROWNUM filter clause will be applied to all indexes used in the plan  or only the driving table indexes (assuming that indexes are used to scan the rows ) ? 

    Please help me here Tom. I have been searching for the answers for more than a week but without any success. 

Thanks 

Tom Kyte

Followup  

February 21, 2005 - 10:31 am UTC

am I understanding... do alot of work to figure out if we need to prevent them from sorting?


this could be a new question at some point but it is too "big and new" for a review/followup to the original

Thanks for looking into my questions

February 22, 2005 - 1:19 am UTC

Reviewer: Purushotham from India

Tom,

Thanks for looking into my queries. I hope I will get opportunity to start a new thread. But unforunately I sit in other part of the world. I tried so many times to post a new question but I always see "Sorry.." message. Hope I will get lucky in near future.

what does rownum > 0 do

March 08, 2005 - 10:40 am UTC

Reviewer: A reader

hi

what does rownum > 0 do?

is it same as no_merge i.e force an inline view is evaluated first? how does it work? is it in the documentation ?

cheers

Tom Kyte

Followup  

March 08, 2005 - 11:42 am UTC

rownum, like intersect, union, minus, .... are non-mergeable set operations.

so it sort of 'documented' in as much as the set operations are non-mergable operations.

so rownum > 0 is

March 08, 2005 - 12:14 pm UTC

Reviewer: A reader

Hi

so rownum > 0 == /*+ NO_MERGE */ in a inline view?

cheers

Tom Kyte

Followup  

March 08, 2005 - 3:07 pm UTC

pretty much in most cases

> so rownum > 0 == /*+ NO_MERGE */ in a inline view?

March 08, 2005 - 1:51 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

No, and since "all it takes is a single counter-example" :

dellera@ORACLE9I> create table t (x int primary key, y char(100) default 'x');

dellera@ORACLE9I> insert into t (x) select rownum from all_objects where rownum <= 1000;

dellera@ORACLE9I> exec dbms_stats.gather_table_stats (user, 't', cascade=>true);

======================================================================
select * from (
select x, y from t
) where x = 1

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("X"=1)

======================================================================
select * from (
select x, y from t where rownum > 0
) where x = 1

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 |
|* 1 | VIEW | | 1000 | 112K| 4 |
| 2 | COUNT | | | | |
|* 3 | FILTER | | | | |
| 4 | TABLE ACCESS FULL| T | 1000 | 101K| 4 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("X"=1)
3 - filter(ROWNUM>0)

======================================================================
select * from (
select /*+ no_merge */ x, y from t
) where x = 1

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | VIEW | | 1 | 115 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("X"=1)

no_merge prevents merging, rownum > 0 prevents merging AND predicate pushing. They are not equivalent (there's a nice discussion about merging vs predicate pushing here on asktom).

Note: plans fetched from v$sql_plan.

Tom Kyte

Followup  

March 08, 2005 - 3:21 pm UTC

good point -- it does both (since rownum must be considered without the predicates)

> so rownum > 0 == /*+ NO_MERGE */ in a inline view?

March 08, 2005 - 1:57 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

No, and since "all it takes is a single counter-example" :

dellera@ORACLE9I> create table t (x int primary key, y char(100) default 'x');

dellera@ORACLE9I> insert into t (x) select rownum from all_objects where rownum <= 1000;

dellera@ORACLE9I> exec dbms_stats.gather_table_stats (user, 't', cascade=>true);

======================================================================
select * from (
select x, y from t
) where x = 1

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("X"=1)

======================================================================
select * from (
select x, y from t where rownum > 0
) where x = 1

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 |
|* 1 | VIEW | | 1000 | 112K| 4 |
| 2 | COUNT | | | | |
|* 3 | FILTER | | | | |
| 4 | TABLE ACCESS FULL| T | 1000 | 101K| 4 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("X"=1)
3 - filter(ROWNUM>0)

======================================================================
select * from (
select /*+ no_merge */ x, y from t
) where x = 1

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | VIEW | | 1 | 115 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("X"=1)

no_merge prevents merging, rownum > 0 prevents merging AND predicate pushing. They are not equivalent (there's a nice discussion about merging vs predicate pushing here on asktom).

Note: plans fetched from v$sql_plan.

rownum + OR + CONTAINS

March 09, 2005 - 4:55 am UTC

Reviewer: Andy from Germany

Hi Tom,

I've encountered behaviour when using rownum with OR-ed CONTAINS terms that I don't understand. Here is a reduced case to illustrate:

mires@WS2TEST> create table t as select object_id as id, object_name as txt1, object_name as txt2 from all_objects;

Tabelle wurde angelegt.

mires@WS2TEST> create index fti_1 on t(txt1) indextype is ctxsys.context;

Index wurde angelegt.

mires@WS2TEST> create index fti_2 on t(txt2) indextype is ctxsys.context;

Index wurde angelegt.

mires@WS2TEST> alter index fti_1 rebuild;

Index wurde geändert.

mires@WS2TEST> alter index fti_2 rebuild;

Index wurde geändert.

mires@WS2TEST> select count(*) from t where contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0;

COUNT(*)
----------
10

So I have 10 rows that meet this criteria...

mires@WS2TEST> select count(*) from t where contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0 and rownum < 12;

COUNT(*)
----------
2

I expect here that, in the absence of clarifying brackets, the first 11 rows are fetched and then checked to see if the fulltext index is satisfied.

mires@WS2TEST> select count(*) from t where (contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0) and rownum < 12;

COUNT(*)
----------
10

..so, with brackets, I get what I expect.

mires@WS2TEST> select count(*) from (select * from t where (contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0)) where rownum < 12;

COUNT(*)
----------
2

This last one i don't understand: the inner query is the same as the first one I executed above, with a "window" applied to the whole result to get the first 11 rows. What am I missing?


Tom Kyte

Followup  

March 09, 2005 - 7:49 am UTC

last one looks like "a bug", please contact support.

No bug!

March 09, 2005 - 8:27 am UTC

Reviewer: Urs from Stuttgart, Germany

Tom,

here is what the SQL Reference says: </code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/conditions001.htm#i1034834 <code>

AND is evaluated before OR, so the brackets should go like

select count(*) from t
where contains(txt1, 'default%', 1) > 0
or ( contains(txt2, 'profile%', 2) > 0
and rownum < 12);

regards, Urs

Tom Kyte

Followup  

March 09, 2005 - 8:29 am UTC

I said the last one:

select count(*)
from (
select *
from t
where (contains(txt1,'default%', 1) > 0
or contains(txt2, 'profile%', 2) > 0)
)
where rownum < 12;


that can only apply the rownum <12 AFTER the or's have all been done.

using rownum to get extra rows

March 29, 2005 - 4:52 pm UTC

Reviewer: Eric Peterson from Issaquah, WA

My coworker and I have been racking our brains to solve this. We'd like ten items returned for every feature, whether or not there are actually ten. We've come up with:

SELECT
NVL ( B.feature, ' ' ) AS feature,
concat_all ( concat_expr ( NVL ( B.item, ' ' ), '|' )) items
FROM
(
SELECT
ROWNUM AS dr
FROM
emp
WHERE
ROWNUM < 11
) A
LEFT JOIN
(
SELECT
*
FROM
(
SELECT
feature,
TRIM ( item ) AS item,
DENSE_RANK () OVER ( PARTITION BY feature
ORDER BY item ) dr
FROM
features
)
WHERE
dr < 11
) B
ON ( A.dr = B.dr )
GROUP BY
NVL ( B.feature, ' ' )
/


But this returns

fea1 item1|item2|item3|item4|item5|item6|item7|item8|item9|item10
fea2 item21|item22

whereas we'd like to see

'fea2 item21|item22| | | | | | | | '
<quotes added to show the tenth space, concat_all doesn't end with a delimiter, which is fine>

We thought the left join to a defined set of 10 would force each of the second query to always have ten items. But we're missing something. Can you point us in the right direction?

Thanks much.
Eric

Tom Kyte

Followup  

March 29, 2005 - 8:02 pm UTC

ops$tkyte@ORA10G> create table t ( feature varchar2(4), item varchar2(6) );
 
Table created.
 
ops$tkyte@ORA10G> insert into t select 'fea1', 'item' || rownum from emp;
 
14 rows created.
 
ops$tkyte@ORA10G> insert into t select 'fea2', 'item' || (20+rownum) from emp where rownum <= 2;
 
2 rows created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> column items format a40
ops$tkyte@ORA10G> variable n number
ops$tkyte@ORA10G> exec :n := 10
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select feature,
  2         n,
  3         case when n > :n
  4                  then substr( items, 1, instr(items,'|',1,:n)-1 )
  5                          when n < :n
  6              then items || replace(rpad('|',:n-n-1,'|'), '|', '| ' )
  7                          else items
  8                  end items
  9    from (
 10  select feature, (length(items)-length(replace(items,'|',''))) n, items
 11    from (
 12  select feature, replace(stragg(item),',','|') items
 13    from t
 14   group by feature
 15         )
 16         )
 17  /
 
FEAT          N ITEMS
---- ---------- ----------------------------------------
fea1         13 item1|item2|item3|item5|item7|item9|item
                11|item13|item14|item12
 
fea2          1 item21|item22| | | | | | | |


does that work ok? 

using rownum to get extra rows

March 30, 2005 - 4:32 pm UTC

Reviewer: Eric Peterson from Issaquah, WA

Yes, this is very helpful. We were caught up in trying to get the sub-query to only return 10 items, we did not consider getting all the data and then limit to the first 10 elements.

But I am still curious to why the left join did not work. (Data and tables from Oracle sample database, not the Scott/Tiger but the newer one.) Note that we are on Oracle 9iR2.

SELECT
*
FROM
(
SELECT
ROWNUM AS dr
FROM
HR.employees
WHERE
ROWNUM < 11
) A
LEFT JOIN
(
SELECT
department_id,
last_name,
salary,
DENSE_RANK() OVER ( PARTITION BY department_id
ORDER BY salary,
last_name ) dr
FROM
HR.employees
) B
ON ( A.dr = B.dr )
ORDER BY
B.department_id,
A.dr,
B.last_name,
B.salary

returns data like:

DR DEPARTMENT_ID LAST_NAME SALARY DR
--- ------------- ------------------------- ------------ ------------
1 10 Whalen 4400 1
1 20 Fay 6000 1
2 20 Hartstein 13000 2
1 30 Colmenares 2500 1
2 30 Himuro 2600 2
3 30 Tobias 2800 3
4 30 Baida 2900 4
5 30 Khoo 3100 5
6 30 Raphaely 11000 6
....


Whereas the use of the left join would lend me to believe that department 10 should be listed sort of like this:

DR DEPARTMENT_ID LAST_NAME SALARY DR
--- ------------- ------------------------- ------------ ------------
1 10 Whalen 4400 1
2
3
4
5
6
7
8
9
10

Am I using the left join incorrectly here? Is the optimizer tuning out the first sub-query? Is the use of ROWNUM not really the best use here?


Thanks much for your insight and help.
Eric

Tom Kyte

Followup  

March 30, 2005 - 5:25 pm UTC

because you need a 10g PARTITIONED outer join!!

you generated the rows 1, 2, ... 10

you outer joined that set to the other table. Well, rownum "5" had a mate in the first feature, it didn't need to "make one up"

you could have:

a) generated the 10
b) generated the set of unique features
c) cartesian product aXb to get feature,1 feature,2, ... for every feature/number
d) outer join to that.

see:

</code> http://www.oracle.com/technology/oramag/oracle/04-jan/o14asktom.html <code>

near the bottom for a example.

holy cr*p, it worked!

May 26, 2005 - 12:26 pm UTC

Reviewer: Andrew Dubry from Medford, OR USA

Using the clues on this page, I was able to construct a query using count and group by and then having it return JUST results 5 thru 10, out of the 15 I limited it to!!

Where Rownum >= 5 and Rownum <= 10 (pseudo code)

That's the holy grail of SQL queries and I figured out how to do it!!! woo hoo!!! thankyou thankyou.. here's my query, BTW:

select * from (
SELECT x, y, rownum r from
(select pd.base_id as X, COUNT(pd.full_id) as Y
FROM prod_detail pd
HAVING COUNT(pd.full_id) > 20
GROUP BY pd.base_id
ORDER BY COUNT(pd.full_id)
), dual
where rownum <= 15
)
where r >= 5
and r <= 10


Thanks!!

gr8

June 01, 2005 - 10:49 am UTC

Reviewer: A reader


Saket

January 02, 2008 - 11:15 am UTC

Reviewer: Saket from Louisville, KY



Hi Tom,

Please accept my best wishes and a very Happy New Year 2008. I thank you Tom and all your team members for everything.

I doubt Mr. Andrew's solution. I am afraid it still does full table scan, as it has order by count(pd.full_id).

I am dealing with a different scenario. I tried this solution in my scenario and did not find any performance gain. I am developing an auditing application, which needs to select random sample rows from a large table for auditing purpose.

Scenario:
I have a table with about 150 million rows. To select say 10 random rows from this table (every time) I use following query (a simplified version)

Select * from (SELECT ct.*
FROM cost_tracking ct
ORDER BY (ct.cost_tracking_id + dbms_random.random) DESC)
Where rownum <=10

Question:
As the above query does full table scan on cost_tracking table, I am looking for an alternate and efficient solution. Is there any better way of selecting 10 random rows form a large table?

Waiting for your reply.

Thanks

Saket


Tom Kyte

Followup  

January 02, 2008 - 2:39 pm UTC

I don't think anyone every said "full scan is avoided there" so - I don't get your point about that.... so what?


have you read about the SAMPLE query??? there is a SAMPLE clause for sql.

go for a random percentage of the table, and then take 10 of that.

sample rows

January 16, 2008 - 4:56 pm UTC

Reviewer: saket from Louisville, KY

Well, we are talking of performance here. What Mr. Andrew claims that he is just accessing first 15 rows (return JUST results 5 thru 10, out of the 15 I limited it to!!), which is wrong? He still access full table to retrieve 10 rows. Or may be I misunderstood his statement!

2. I have gone through sample clause. It is not suitable in my application because, its argument expects %, which may not give me desired results in all possible table sizes.

What I am looking for is; Instead of getting n% of sample rows, Can I get n sample rows?

Thanks

Saket

runstats prob

March 24, 2008 - 5:16 pm UTC

Reviewer: Harrison Picot from Haymarket (motto: "Dogs are happy here") Virginia USA

HiTom,

Thanks for all the great help. I am having what seems to
be a pretty basic problem, but I have been working on it for a full day and not getting any closer. When I run this code from the part above on setting up runstats, it errors:

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
/

hpicot@ora10g> @part1
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges

If I just do the select, runs, but when I try to creat the view in either 10.2 or 11G, I get that error. I queried all_objects for an existing object named 'STATS' on 11G and got nothing, and I ran it as system to see if there are any permission errors, and tried changing the view name, but those changes generated the same error. Like I say, I am sure it something simple but certainly has not occurred to me yet. The oerr help says (like) that I am trying to change a password without authority, but that seems off also.



Tom Kyte

Followup  

March 24, 2008 - 7:40 pm UTC

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

grant select on v_$mystat to USERNAME;

not via a role

glad to see you back developing :)

rownum >0 & insert.

March 26, 2008 - 3:15 am UTC

Reviewer: Dhairyasheel Ttawde from India.(Mumbai.)

Hi Tom,

a few months back, we had a unique problem at our site.

1. We were selecting from a normal table
2. & inserting into a global temp table.

the insert would not work for even 10 records. It would just hang.

then the d.b.a out here put another filter in the where clause of the select statement, that was
..... and rownum >0;

and the insert started to work.

Whats happening here....

Sorry can't provide any technical details for the above.

asc and desc in top-n

March 31, 2008 - 4:40 am UTC

Reviewer: dk from Poland

Hello Tom,
I've tried you example
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2   from ( select owner, object_name, object_id
  3                    from t
  4                   order by object_id desc )
  5   where rownum < 10;
Could you please explain why the index is not used if changing to ascending order?

Tom Kyte

Followup  

March 31, 2008 - 9:36 am UTC

I cannot explain that which doesn't happen.


order by object_id desc
order by object_id asc


either one or both can and will use an index on object_id.

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(object_id) ;

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from (select * from t order by object_id ASC) where rownum < 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993548034

------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%C
------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     9 |  1152 |     3
|*  1 |  COUNT STOPKEY                |       |       |       |
|   2 |   VIEW                        |       |     9 |  1152 |     3
|   3 |    TABLE ACCESS BY INDEX ROWID| T     | 49794 |  4522K|     3
|   4 |     INDEX FULL SCAN           | T_IDX |     9 |       |     2
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)

ops$tkyte%ORA10GR2> select * from (select * from t order by object_id DESC) where rownum < 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3806458071

------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%C
------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     9 |  1152 |     3
|*  1 |  COUNT STOPKEY                |       |       |       |
|   2 |   VIEW                        |       |     9 |  1152 |     3
|   3 |    TABLE ACCESS BY INDEX ROWID| T     | 49794 |  4522K|     3
|   4 |     INDEX FULL SCAN DESCENDING| T_IDX |     9 |       |     2
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)

ops$tkyte%ORA10GR2> set autotrace off

it's working

April 02, 2008 - 1:08 am UTC

Reviewer: dk from Poland

I must cancel my last question. I've checked once more. It's using the index properly.

Will ROWNUM return same row everytime ?

September 12, 2010 - 11:37 am UTC

Reviewer: ankitv from INDIA

Hi Tom, the article (along with the rest ones) have been very helpful. I have a small query :
select * from A where rownum = 1
Will the above query return same row everytime in the future if:
1. No row is ever added into the table.
2. There is constant addition of rows in the table.
Tom Kyte

Followup  

September 13, 2010 - 2:22 pm UTC

answer is definitely NO, it does not have to return the same row and you certainly cannot count on it.

1) if rows are updated or deleted, the 'first' row can easily change.

2) if you add rows, we add them where ever we feel like and that could certainly also be "right at the top". The last row inserted could easily be the first row returned.


also, anything that causes a query plan change will change the order of the rows being returned.



Will ROWNUM return same row everytime ?

September 17, 2010 - 3:07 am UTC

Reviewer: ankitv from INDIA

But what if no change happens in table (no add/upd/delete) , suppose its a Parametrization table, will
select * from table where rownum =1
will return same row every time.
Tom Kyte

Followup  

September 17, 2010 - 7:13 am UTC

no, it does not have to.

will it? probably...

can you count on it? no more than you can count on group by sorting data - which you cannot.

If you have a concept of a 'first row' you better have some idea how to identify it - and then use that technique to deterministically find it each and every time.

July 24, 2013 - 2:00 pm UTC

Reviewer: vaibhaw from INDIA

if i have to fetch 2(like 2 and 3) row by using rownum then its possible?
Tom Kyte

Followup  

July 31, 2013 - 6:56 pm UTC

huh? no idea what you are trying to say

confusion two times filter

September 15, 2013 - 2:59 pm UTC

Reviewer: Ranjan

Hi Tom,

Hope you are ok and doing great.

I have a doubt regarding the explain plan of query using rownum.

################
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 15 20:01:28 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: ********

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table demo1(a number,b number);

Table created.
SQL>  insert into demo1  select level,level+10 from dual connect by level<5;

4 rows created.

SQL> insert into demo1 select * from demo1;

4 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'DEMO1');

PL/SQL procedure successfully completed.

SQL> set lines 100 pages 90
SQL> set autotrace traceonly explain
SQL> select * from (select a,count(*) from demo1 group by a) where rownum<3;

         A   COUNT(*)
---------- ----------
         1          2
         2          2


Execution Plan
----------------------------------------------------------
Plan hash value: 1574380217

---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     2 |    52 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |       |       |       |            |          |
|   2 |   VIEW                  |       |     4 |   104 |     4  (25)| 00:00:01 |
|*  3 |    SORT GROUP BY STOPKEY|       |     4 |    12 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | DEMO1 |     8 |    24 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<3)
   3 - filter(ROWNUM<3)
###############################

My doubt is why it uses two time filters. 
I think it should be 3-sort group by instead of "3-sort group by stopkey" .

But thinking deep(keeping in mind for huge number of data)
,then I thought that it sorting for 2 different values(like 1,2 in this case) and grouping those two diff values and leave all remaining values as they are under it and then do
the final sort for two record(which is 1-count stopkey.

It even showing two filter for query like
#######
select * from (select * from demo1 order by a) where rownum <3;
###########
But I am not sure if that really does as I explained.

Could you please clarify and clear my above doubt.



Tom Kyte

Followup  

September 23, 2013 - 6:14 pm UTC

it is just an artifact, is "nuance" it doesn't really rownum < 3 twice, it is just that an explain plan is in imperfect representation of the binary program generated to run the plan.

typo error

September 15, 2013 - 3:10 pm UTC

Reviewer: Ranjan

"final search for 2 records" instead of "final sort for 2 record"

hmm got it :)

September 23, 2013 - 6:37 pm UTC

Reviewer: A reader