Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month 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. 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, kamini.

Asked: July 10, 2001 - 10:23 am UTC

Answered by: Tom Kyte - Last updated: January 21, 2020 - 1:36 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom

I have 2 questions

1. I have a table called a where I have more than one row. When I put a query "SELECT * FROM A WHERE ROWNUM=1" it gives me the first row. But if I put a query specifying any number other than 1 for e.g. "SELECT * FROM A WHERE ROWNUM=2" it is not returning any rows. Why? But at the same time if I use > or < it returns row. Could u please clarify me?

2. Can we have a static type variable in PL/SQL like what we have in VB, VC etc.

Thanx

and we said...

1) rownum is assigned to rows AS THEY SATISFY the predicate.

the logic would be:


rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;

in the case of where rownum = 1, the first row passes the test, is output and rownum goes to 2. No other row ever satisfies the predicate and rownum stays at 2 for the rest of the query.

in the case of where rownum = 2, the first row is rownum 1, it fails. The next row is ALSO rownum = 1 and likewise fails. And so on. There can be NO row 2 if there is not a row 1.


Btw: it does not return the "FIRST" row -- it returns A row.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:912210644860 <code>

that deals with the "last"


2)

declare
x CONSTANT number default 5;
begin
null;
end;
/

and you rated our response

  (95 ratings)

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

Reviews

Rownum

July 11, 2001 - 2:25 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia

It's a very good explanation.

September 25, 2001 - 2:10 am UTC

Reviewer: A reader

shouldnt you example better be like

v_rownum = 1
for x in ( select rownum, a.* from A )
loop
if ( x.rownum = v_rownum )
then
OUTPUT the row
v_rownum = v_rownum + 1
end if;
end loop;



Tom Kyte

Followup  

September 25, 2001 - 6:16 am UTC

No, no it shouldn't.

ROWNUM is a psuedo column -- its value is assigned as rows are output. Using your example -- each row, regardless of whether it was output, has a "rownum". I was trying to show that rownum -- THE ROWNUM -- is incremented only AFTER the row is output. Your example would imply each and every row in the base tables would have a rownum assigned to it.

September 25, 2001 - 2:13 am UTC

Reviewer: A reader

Hi

What do you mean by the predicate, what would it be in your example?

Tom Kyte

Followup  

September 25, 2001 - 6:18 am UTC

Predicate is the where clause. In my example, the predicate is:

where rownum = 1

My example could have been:

rownum = 1
for x in ( select * from A )
loop
if ( rownum = 1 /* same as x satisfies the predicate */ )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;



"static" variable

September 25, 2001 - 10:22 am UTC

Reviewer: Tom Best from King of Prussia, PA

I think what Kamini was asking in question 2, was for a C-like "static" variable, meaning it maintains its value from call to call. That would mean declare it in the package spec, right? Will it maintain it's value from call to call then?

Tom Kyte

Followup  

September 25, 2001 - 8:04 pm UTC

I posted psuedo code (not real plsql) -- there is no need for a static variable -- in fact a static variable would mess it up (rownum needs to be reset for each query).

Grabbing "slices" of result sets

September 25, 2001 - 11:30 am UTC

Reviewer: Daryl Biberdorf from Carrollton, TX USA

Grabbing rows from the middle of a select (e.g., a slice) is something I've had to do with web-based apps in the past to allow the user to move through web pages of results ("Items 21-30 of 52").

Perhaps there is a better way, but one way to accomplish the original poster's question is with a query like:

SELECT * FROM
(SELECT * FROM a_table WHERE ... AND rownum <= 2)
WHERE ROWNUM=2

We've used the following to fetch a given "slice":
SELECT * FROM
(SELECT * FROM a_table WHERE ... AND ROWNUM < 100)
WHERE ROWNUM > 90

It seems expensive, perhaps, but requires a lot less programming overhead than, say, using temp tables to hold results. The upper and lower limit for the row numbers can be passed easily from web browser to server application. Some empirical testing showed it performed no worse than other, more complex, approaches we tried.

Tom Kyte

Followup  

September 25, 2001 - 8:03 pm UTC

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:127412348064 <code>

for how to do this. The above example is close -- but won't work as is (the rownum = 2 and rownum > 90 will NEVER be satisfied -- you need to select rownum in the subquery, alias it, and reference that in the outer query)....



simpler query

September 25, 2001 - 9:57 pm UTC

Reviewer: A reader

select * from (
select inner.*, rownum rn from (query) inner
) where rn between 100 and 200

Tom Kyte

Followup  

September 26, 2001 - 8:31 am UTC

simplier, but infinitely less efficient. Consider a table:

create table t as select * from all_objects;
alter table t add constraint t_pk primary key(object_id);

(my all objects is pretty big -- about 20,000 rows).... Now, using SQL_TRACE and TKPROF I can see:

select *
from ( select inner.*, rownum rn
from ( select object_id, object_name
from t
order by object_id ) inner
where rownum <= :high
)
where rn >= :low

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.01 0 140 0 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.01 0.01 0 140 0 101

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

Rows Row Source Operation
------- ---------------------------------------------------
101 VIEW
200 COUNT STOPKEY
200 VIEW
200 TABLE ACCESS BY INDEX ROWID T
200 INDEX FULL SCAN (object id 18584)


select *
from ( select inner.*, rownum rn
from ( select object_id, object_name
from t
order by object_id ) inner
)
where rn between :low and :high

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.35 0.34 0 16004 0 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.36 0.34 0 16004 0 101

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

Rows Row Source Operation
------- ---------------------------------------------------
101 VIEW
16865 COUNT
16865 VIEW
16865 TABLE ACCESS BY INDEX ROWID T
16866 INDEX FULL SCAN (object id 18584)


(i used low and high of 100, 200)

You see, in my "more complex" version, we can push a predicate down into the inner query. Using the simpler one, we cannot. So, the more complex works much faster then the easier one for that reason.

I recommend my approach of the "where rownum <= :max ) where rn >= :min" for definite performance gains.

rownum expression is not a predicate

September 25, 2001 - 10:52 pm UTC

Reviewer: Mikito Harakiri

Predicates obey laws like this:

select where A or B

is identical to

select where A
union
select where B

Now try 'rownum = 1' instead of A and
'rownum = 2' instead of B, and note that this identity and many many others dosn't hold.

In short, use of rownum gives unpredictable (or to put it mildly - unintuitive:-) results. Especially, for complex queries with rownum hidden somewhere in the middle subquery.


Tom Kyte

Followup  

September 26, 2001 - 8:04 am UTC

Well, first of all -- select where a or b is NOT identical to select where a union select where b.


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int );

Table created.

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

1 row created.

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

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where a = 1 or b = 1;

         A          B
---------- ----------
         1          1
         1          1

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where a = 1
  2  union
  3  select * from t where b = 1;

         A          B
---------- ----------
         1          1

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where a = 1
  2  union all
  3  select * from t where b = 1;

         A          B
---------- ----------
         1          1
         1          1
         1          1
         1          1

That shows that neither of union nor union all are the "same" as or.  So, your basic premise is flawed.

In short, the use of rownum -- which is a PSUEDO COLUMN assigned during output, not a static value in a table -- is a feature which you can choose to understand or not -- to use or not.  

Once you understand how it is assigned, using it can solve some very nasty problems.

It is like understanding why:

select * from t where x NOT IN ( select y from t2 )

is totally different (gives a different answer) then

select * from t where NOT EXISTS ( select y from t2 where t2.y = t.x )

as for "Especially, for complex queries with rownum hidden 
somewhere in the middle subquery" - I differ.  It is these queries where rownum actually becomes a STATIC value, has totally predicable values.  For example:

scott@ORA817DEV.US.ORACLE.COM> select *
  2  from ( select ename, rownum r from emp )
  3  where r between 5 and 7
  4  /

ENAME               R
---------- ----------
MARTIN              5
BLAKE               6
CLARK               7

Now, r = value, r > value -- those all work "intuitively".  The only trick here is to assign rownum after any ordering you might want to do.  To get the 3rd highest paid person you might:

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

ENAME             SAL          R
---------- ---------- ----------
FORD             3000          3





 

Not In/ Not Exists In/Exists

September 26, 2001 - 11:56 am UTC

Reviewer: J from Ma

You said:
"It is like understanding why:

select * from t where x NOT IN ( select y from t2 )

is totally different (gives a different answer) then

select * from t where NOT EXISTS ( select y from t2 where t2.y = t.x )"

I've read otherwise, (haven't read yours, yet :( )

Could you please explain in a little more detail?

Thanks
btw I get the rownum

Tom Kyte

Followup  

September 26, 2001 - 1:51 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:442029737684 <code>
...

rownum expression is not a predicate

September 26, 2001 - 12:46 pm UTC

Reviewer: Mikito Harakiri

Hmm... I'm surprised that the optimiser was able to push down a predicate in more complex query as compared to simpler 'between'. Is this just an Oracle artifact or there is a reason behind?

My basic premise is valid if we put 'distinct' around all 'selects' to avoid nasty bag semantics. Besides, many people find counterintuitive the fact that

select * from emp order by ename

and filtering first 10 records on the client is different from

select * from emp where rownum <10 order by ename

Correct, after some trial and error they get used to nested selects.

Tom Kyte

Followup  

September 26, 2001 - 1:58 pm UTC

Well actually the "we" i referred to was us collectively. "We" pushed the predicate down -- Oracle could not. You see, if Oracle DID push the predicate down -- it would change the ANSWER. It would have to push the

where rn between :l and :h

and if it did that -- the result would be ZERO rows. We (we being the humans) can figure out that we can push the "<= :h" into the inner query but we HAVE to leave the ">= :l" in the outer query.


I disagree with the "distinct" and "nasty bag semantics" -- the point is UNION is not OR, not close.


I do not understand why it is counter intuitive myself (i agree -- people get confused by it). Maybe its just understanding SQL better.

Think about it like this:

select * from emp where ename like 'A%' order by ename;

would you expect that to ORDER everything and then find all of the 'A' records or would you expect it to find the 'A' records and then sort?

Just substitute in rownum < 10 for ename like 'A%'. Now, you would expect it to find the first ten records and then sort them....

you drive us crazy by using these small tricks

September 26, 2001 - 2:43 pm UTC

Reviewer: A reader

select ename, sal, r
from ( select ename, sal, rownum r
from ( select * from emp order by sal desc NULLS LAST)
)
where r = 1

what does nulls last mean, is this a part of the syntax for select clause.

Here goes the answer for the above comment

September 26, 2001 - 2:47 pm UTC

Reviewer: Nag from USA

order_by_clause
orders rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order. For a discussion of ordering query results, see "Sorting Query Results".

expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause.

position orders rows based on their value for the expression in this position of the select list; position must be an integer.



You can specify multiple expressions in the order_by_clause. Oracle first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. Oracle sorts nulls following all others in ascending order and preceding all others in descending order.


ASC | DESC
specifies the ordering sequence. ASC is the default.


NULLS FIRST | NULLS LAST
specifies whether returned rows containing null values should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.



Ummm

September 26, 2001 - 2:52 pm UTC

Reviewer: A reader from USA

select ename, sal, r
from ( select ename, sal, rownum r
from ( select * from emp order by sal desc NULLS FIRST)
)
where r = 3

I changed the first to last , and it made no difference, I expected to see no results , but it return row 3.

Why so.


Tom Kyte

Followup  

September 26, 2001 - 4:31 pm UTC

that just means there were no nulls in your SAL column thats all.

SAL is nullable. If you order by sal DESC, it would put the nulls first by default. Lets change the query to COMM (which does have nulls)

scott@ORA817DEV.US.ORACLE.COM> select ename, comm, r
2 from ( select ename, comm, rownum r
3 from ( select * from emp order by comm desc NULLS FIRST)
4 )
5 where r = 3
6 /

ENAME COMM R
---------- ---------- ----------
CLARK 3

scott@ORA817DEV.US.ORACLE.COM> select ename, comm, r
2 from ( select ename, comm, rownum r
3 from ( select * from emp order by comm desc NULLS LAST)
4 )
5 where r = 3
6 /

ENAME COMM R
---------- ---------- ----------
ALLEN 300 3



Does ROWNUM forces FTS?

September 27, 2001 - 11:32 am UTC

Reviewer: A reader

Does the use of rownum force Oracle to do a full table scan? What I understand how rownum works is this, for example

select ename from emp
where rownum < 3;

it firsts do select * from emp then from this result set it assigns the rownum with a set - test fashion

for example

the first row of result set would be SMITH so Oracle assigns rownum 1 to this row, it then tests this value to "where rownum < 3" since rownum 1 is less than 2 then this row pass the test, then do the same thing for the second row and third, the third fells because 3 is not smaller than 3. But in order to do these operations it needs a result set from FTS no?

Or it simply gets one row per fetch and assigns rownum then test for condition "rownum < 3" (fetch one row set & test)? Rather than get everything then set and test (fetch all set & test)



Tom Kyte

Followup  

September 27, 2001 - 2:22 pm UTC

Yes and No.

Your query might say "full scan" -- but it will also say maybe "COUNT STOPKEY" -- meaning when it has hit the end of the rownums you are looking for, it stops. That is why in my example above, the reply to the comment "simpler query", my query works much faster, does much less work then the "simpler query".

The rownum caused the query to STOP after it found the max row. We just then weeded out the rows below the min row and returned.

So, it might full scan -- but it will STOP scanning when its found the answer.

September 27, 2001 - 3:25 pm UTC

Reviewer: A reader

So

Which one is more correct

fetch one row then set & test
or
fetch all then set & test

Tom Kyte

Followup  

September 27, 2001 - 4:07 pm UTC

sorry -- does not compute... don't understand your point.

If you mean rownum -- it tries to whenever possible:

set rownum to 1
start fetching
test
if successful
output
increment rownum
if rownum prevents ANY more rows from being considered -- stop
processing



September 27, 2001 - 4:58 pm UTC

Reviewer: A reader

Hi

What I mean is if the query first fetch all rows from the query then assign rownum individually or the query fetch one row then assign rownum then fetch another row and assign rownum and so on

option 1
for example table emp has 14 rows

select empno from emp
where rownum < 3

this would return 2 rows, what I am wondering if Oracle first select all 14 rows into data buffer (result set) then from this result set it fetch one row then assign rownum, test for predicate, satisfies then fetch another row if this was the case we would get a full table scan because of first part of the query

option 2

for example table emp has 14 rows

select empno from emp
where rownum < 3

Instead of selecting all 14 rows into data buffer Oracle fetch one row into data buffer, assign the rownum test for the predicate then fetch another row into data buffer test again and so on for just 2 rows (since rownum < 3) so in this case we dont do FTS

Which option is Oracle using?

Tom Kyte

Followup  

September 28, 2001 - 7:10 am UTC

It will fetch row, assign rownum, output row.

It will NOT full scan the entire table -- it'll stop.  

This is actually demonstrated in the above queries -- the "complex" query STOPS reading the table when it hits the rownum we used.  The "simple" query does not due to the way it was written.

When you see the COUNT STOPKEY -- that indicates we STOP when we hit N rows.

It DOES a FTS in any case, it just STOPS doing the FTS when it found the last row you want.

Perhaps this makes it more clear:


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

Table created.

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


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where rownum < 100;

99 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         13  consistent gets
          3  physical reads
          0  redo size
      13752  bytes sent via SQL*Net to client
       1096  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where rownum < 1000;

999 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
         83  consistent gets
         18  physical reads
          0  redo size
     130234  bytes sent via SQL*Net to client
       7756  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where rownum < 10000;

9999 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
        794  consistent gets
        172  physical reads
          0  redo size
    1161126  bytes sent via SQL*Net to client
      74356  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9999  rows processed

See how the consistent gets go up and up and up as we get more and more data.  We must be full scanning (no indexes) but we read less data as we need less rows. 

Reader

September 28, 2001 - 11:34 am UTC

Reviewer: Reader from USA

Excellent mapping of FTS to statistics (consistent get)
deriving the inference

why consistent block gets increases?

September 28, 2001 - 4:13 pm UTC

Reviewer: A reader

hi

why db block gets increse so little and consistent gets so much? are we reading from RBS?

Tom Kyte

Followup  

September 29, 2001 - 2:45 pm UTC

No, in this case we are not reading from the RBS.

DB BLOCK GETS are gets in "current" mode. These do not change in this example from query to query because the only blocks we need in "current" mode are those from the segment header that tell us how to full scan the table. The db_block_gets are a CONSTANT in this query -- regardless of the number of rows we fetch.

The consistent gets are the actual database blocks with our data on them. the more rows we fetch, the more we read.



I guess

September 29, 2001 - 7:29 pm UTC

Reviewer: Wells from USA

Tom


I guess, that the db block gets are consistent 12 because it is only a select, if it had been an update or delete , then the db block gets would have been more , Iam I right.

Iam I right?

Tom Kyte

Followup  

September 29, 2001 - 8:33 pm UTC

Absolutely:

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set owner = owner where rownum < 100;

99 rows updated.


Statistics
----------------------------------------------------------
         73  recursive calls
        115  db block gets
         35  consistent gets
          0  physical reads
      24432  redo size
       1145  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         99  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set owner = owner where rownum < 1000;

999 rows updated.


Statistics
----------------------------------------------------------
          0  recursive calls
       1033  db block gets
         19  consistent gets
          0  physical reads
     244416  redo size
       1007  bytes sent via SQL*Net to client
        827  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        999  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set owner = owner where rownum < 10000;

9999 rows updated.


Statistics
----------------------------------------------------------
          0  recursive calls
      10245  db block gets
        140  consistent gets
          0  physical reads
    2456648  redo size
       1008  bytes sent via SQL*Net to client
        828  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       9999  rows processed

where t was "create table t as select * from all_objects" and my all_objects has over 10k rows...

 

Once Again Tom

September 29, 2001 - 11:59 pm UTC

Reviewer: Wells

IN

Statistics
----------------------------------------------------------
0 recursive calls
10245 db block gets
140 consistent gets
0 physical reads
2456648 redo size
1008 bytes sent via SQL*Net to client
828 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9999 rows processed

Here we are doing 140 consistent reads , at the read stage to see the location of the blocks ( or is it rows) where in lie the rows which we need to update, and then

we are doing 10245 db block gets or current gets i.e. as they exist at that moment, and updating them , Iam I right.

1.Does this mean that our 9999 rows are located in 140 blocks.

2.Can you explain the difference 246 between db block gets and number of rows.

Are these 246 db block gets for segment header reads .


Tom Kyte

Followup  

September 30, 2001 - 9:15 am UTC

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:878847787577 <code>

it goes into this with a delete. Yes -- we do a consistent read of 140 blocks to find 9999 rows and then do a db block get FOR EACH ROW to do the delete. The 246 blocks extra are rollback segments/header blocks and such.

Tom, what about the recursive calls

September 30, 2001 - 12:02 am UTC

Reviewer: Wells

How about the recursive calls

when you are updating 99 rows there are 73 recursive calls , and when you are updating 9999 rows the number of recursive calls are none, can you explain this

and actually how many levels of recursive levels are there, and what are they..

Thank you very much

Tom Kyte

Followup  

September 30, 2001 - 9:12 am UTC

You are seeing the effects of the data dictionary cache getting warmed up. I had never updated T before so it had to figure lots of stuff out like

- what is T (structurally)
- am I allowed to update T
- any triggers on T
- what about constraints
- etc etc etc

So, it ran lots of sql on behalf of my sql (recursive sql that is called) in order to discover all of this. On the subsequent queries -- this data was in the dictionary cache in the SGA and it did not need to run that recursive sql to find it.



How about

September 30, 2001 - 7:21 pm UTC

Reviewer: A reader

((and actually how many levels of recursive levels are there, and what are they))

can high amount of recursive sql also result from some bad database settings.

Tom Kyte

Followup  

October 01, 2001 - 6:33 am UTC

Sure -- you could have high recursive sql calls due to using a dictionary managed permanent tablespace for TEMP (you should use a true temporary tablespace). The recursive sql in this case is due to the space management.

It could be due to you having too small of a shared pool -- not enough room to cache everything.

On the other hand -- all SQL in PLSQL (all of YOUR sql) is recursive SQL, so it is not all evil.

Another Rownum question

June 30, 2003 - 5:05 am UTC

Reviewer: TH from Manama, BAH

Dear Tom,

I have a case like this.

Table ITEMS with Fields MAIN, SUB & CODE
--------------------------------------------

MAIN SUB CODE
=======================
01 DXFDF 1
01 SDYHJ 2
01 SDFHJ 3
01 EWEHJ 4

02 TYTYY 1
02 TYTY 2
02 IGHGH 3

03 BHGHG 1
03 SDFHH 2


The column "CODE" is curently empty. But it should come like above. What can I do?

Is it possible to do it thro' a single update statement? Or what is the best solution?

Thanx


Tom Kyte

Followup  

June 30, 2003 - 7:09 am UTC

just

create view v
as
select main, sub, row_number() over (partition by main order by sub) code
from t;

that'll dynamically assign code in the view. If you want to "persist" this data, you'll need to (psuedo code, please work out the details)


is
cursor c is select rowid rid, code from v;
begin
open c;
loop
fetch c bulk collect into l_rids, l_codes LIMIT 500;
forall i in 1 .. l_rids.count
update t set code = l_codes(i) where rowid = l_rids(i);
exit when c%notfound;
end loop
close c;
....




July 14, 2003 - 9:57 am UTC

Reviewer: A reader from Virginia, USA

I understand how rownum is assigned and how one can update based on rownum. Here's an example you used:

update t set owner = owner where rownum < 100;

Updates the first 100 rows. But what if I wanted to Update the last 100 rows?




Tom Kyte

Followup  

July 14, 2003 - 10:48 am UTC

that doesn't update "the first 99 rows" as there isn't such a concept.

that updates 99 rows. Not the first 99.

there is no such thing as "the first 99", "the last 99"

there are just rows.


You would have to SORT the data in order to have a "first" and "last". something like:

update t
set ...
where rowid in ( select rid
from (select rowid rid from t order by X)
where rownum < 100 );



ORs and Rownum

January 20, 2004 - 4:30 pm UTC

Reviewer: A reader

Hi

We have a very simple query in 8.1.7.4

select count(account_id)
from account
where holder_id = :b
or payer_id = :b
and rownum = 1

This runs lightling fast in RULE because it transforms OR into UNION ALL (both holder_id and payer_id are indexed)

If I use CBO it full scans the table which has 1 million of rows. Surprisingly if I modify the query to

select count(account_id)
from account
where (holder_id = :b
or payer_id = :b)
and rownum = 1

It transofmrs this into UNION ALL and runs very fast too!

Is this a bug?

Tom Kyte

Followup  

January 20, 2004 - 10:00 pm UTC

what happens when you tell it "the first rows are what I'm interested in"

use /*+ FIRST_ROWS */ and see what happens.

'OR' plus 'AND' with no brackets = 'CONFUSION'

January 21, 2004 - 1:25 am UTC

Reviewer: Gary from Sydney, Aus

select count(account_id)
from account
where holder_id = :b
or payer_id = :b
and rownum = 1

Given that the query is probably 'wrong' (ie doesn't do anything that can logically be wanted) then it is hardly surprising that it confuses the optimizer.

Are you asking for where
1) holder_id is 'x' or (payer_id is 'y' and rownum =1), or
2) (holder_id is 'x' or payer_id is 'y') and rownum =1



ORs and Rownum

January 21, 2004 - 3:55 am UTC

Reviewer: A reader

Hi

I used FIRST_ROWS but no luck, it still uses FTS.

I copied the table to 9i and it runs ok, sounds a bug in 8.1.7.4?

Tom Kyte

Followup  

January 21, 2004 - 6:40 am UTC

if there are any bugs here, it sounds like a bug in the RBO actually after reviewing the above comment :)

 
  1  select * from all_users
  2* where 1=1 or 1=0 and rownum=1
ops$tkyte@ORA9IR2> /
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 12-MAY-02
SYSTEM                                  5 12-MAY-02
OUTLN                                  11 12-MAY-02
DBSNMP                                 19 12-MAY-02
.....


your use of or is in fact "improper".  use the parens and see what happens -- it totally changes the meaning of the query.   

You seem to want to find "the first one" and count it.  But, your query is just like:


select count(account_id)
  from (select rowid, account_id from t where holder_id = :b 
        UNION ALL
        select rowid, account_id from t where payer_id = :b and rownum = 1)
/


 

but then why it runs fast in 9i?

January 21, 2004 - 6:52 am UTC

Reviewer: A reader

Hi

If you say it´s a RBO bug why it runs fast in 9i and not 8i?

Tom Kyte

Followup  

January 21, 2004 - 7:43 am UTC

fix your sql please, put the parens in and then lets talk.

and look this

January 21, 2004 - 7:13 am UTC

Reviewer: A reader

please look this

SQL> var b1 NUMBER
SQL> exec :b1:=2438072

PL/SQL procedure successfully completed.

SQL> SELECT COUNT (bill_scope_id)
  2    FROM swb.om_bill_scope_inst
  3   WHERE titular_id = :b1 OR payer_id = :b1 ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
   1    0   SORT (AGGREGATE)
   2    1     CONCATENATION
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'OM_BILL_SCOPE_INST' (Cost=1 Card=1 Bytes=10)
   4    3         INDEX (RANGE SCAN) OF 'RL_BILL_FK10' (NON-UNIQUE) (Cost=3 Card=1)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'OM_BILL_SCOPE_INST' (Cost=1 Card=1 Bytes=10)
   6    5         INDEX (RANGE SCAN) OF 'RL_BILL_FK' (NON-UNIQUE) (Cost=3 Card=1)

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


I dont really care if there is rownum or not or the order of operator precedence, the problem here is with rownum it choose FTS but without or with brackets it choose the proper plan... Looks like a bug...!!!  

Tom Kyte

Followup  

January 21, 2004 - 7:44 am UTC

YOU HAVE A BUG when you add "and rownum = 1"

Pleaes, fix your query and then lets talk -- ok.

rownum

January 21, 2004 - 10:47 am UTC

Reviewer: Potkin Nemat from London

If I really really want to select a row based on rownum, I select it from an inline view or subquery. Rownum must be aliased in the query though.

SELECT *
FROM
(select ROWNUM r, i.* from tablename i)
WHERE R = 5

Tom Kyte

Followup  

January 21, 2004 - 3:06 pm UTC

well, that certainly gets you a random row for sure. not very meaningful -- the "5th row" from an unordered result set.

the right way for this will be:


select *
from ( select a.*, rownum r
from ( your_query_here with ORDER BY)
where rownum <= :x )
where r >= :x;


that'll be better than "where r=5"

Views and ROWNUM

February 26, 2004 - 6:17 am UTC

Reviewer: Mark from Cambridge, UK

Hi Tom,

I have a performance problem (wrong Optimizer choice) when using rownum in a view in Oracle 8.1.7.4.

The view (see below, most columns left out for readability) combines the actual table called dbentry with its audit table dbentry_audit.
The idea of the view is to find the row as it was in a particular point in time, that is the most recent row before the pck_release.release_time.


So far for my shaky explanation; I just want to show you what's happening now. First a view here without any ROWNUM, it works okay. See execution plan.



CREATE OR REPLACE VIEW DBENTRY_VW
AS
SELECT dbentryid, timestamp
FROM datalib.dbentry
WHERE timestamp < pck_release.release_time
UNION
SELECT dbentryid, timestamp
FROM datalib.dbentry_audit
WHERE timestamp < pck_release.release_time
ORDER BY timestamp DESC
/

View created.

select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
from dbentry_vw
where dbentryid = 67846230
/

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=2 Bytes=44)
VIEW OF 'DBENTRY_VW' (Cost=22 Card=2 Bytes=44)
SORT (UNIQUE) (Cost=16 Card=2 Bytes=28)
UNION-ALL
TABLE ACCESS (BY INDEX ROWID) OF 'DBENTRY' (Cost=1 Card=1 Bytes=13)
INDEX (UNIQUE SCAN) OF 'DBENTRY_PKEY' (UNIQUE) (Cost=3 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF 'DBENTRY_AUDIT' (Cost=1 Card=1 Bytes=15)
INDEX (RANGE SCAN) OF 'DBENTRY_AUDIT_DBENTRYID' (NON-UNIQUE) (Cost=4 Card=1)


DBENTRYID TO_CHAR(TIMESTAMP,'D
---------- --------------------
67846230 25-feb-2004 16:33:30
67846230 01-sep-2000 23:30:13
67846230 26-may-2000 03:01:48
67846230 25-may-2000 13:35:00
67846230 27-apr-2000 16:09:42



....





Now what I want is the top row, not five rows. So I alter the view a bit, see below.
Next thing you know, the plan is completely changed. The full table scan is unacceptable because these tables are huge. The statistics are up to date.

It is the View messing it up here. Because if I use the first view (the fast one) and would query that with 'where rownum=1' it's fast; but if I make rownum=1 part of the view itself, it fails.

Help ??




CREATE OR REPLACE VIEW DBENTRY_VW
AS
SELECT * FROM (
SELECT dbentryid, timestamp
FROM datalib.dbentry
WHERE timestamp < pck_release.release_time
UNION
SELECT dbentryid, timestamp
FROM datalib.dbentry_audit
WHERE timestamp < pck_release.release_time
ORDER BY timestamp DESC
)
WHERE ROWNUM =1
/

View created.

select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
from dbentry_vw
where dbentryid = 67846230
/

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=583111 Card=5461380 Bytes=120150360)
VIEW OF 'DBENTRY_VW' (Cost=583111 Card=5461380 Bytes=120150360)
COUNT (STOPKEY)
VIEW (Cost=583111 Card=5461380 Bytes=120150360)
SORT (UNIQUE STOPKEY) (Cost=507841 Card=5461380 Bytes=78342610)
UNION-ALL
TABLE ACCESS (BY INDEX ROWID) OF 'DBENTRY' (Cost=4715 Card=1789045 Bytes=23257585)
INDEX (RANGE SCAN) OF 'I_DBENTRY_TIMESTAMP' (NON-UNIQUE) (Cost=4688 Card=1789045)
TABLE ACCESS (FULL) OF 'DBENTRY_AUDIT' (Cost=429674 Card=3672335 Bytes=55085025)


Tom Kyte

Followup  

February 26, 2004 - 10:22 am UTC

what is Pck_release.release_time? where does it come from.

More details on last question

February 26, 2004 - 11:04 am UTC

Reviewer: Mark

It's just a timestamp in a package. See below.

PACKAGE
pck_release
AS
timestamp DATE := sysdate;
FUNCTION release_time
RETURN date;
END;


PACKAGE BODY
pck_release
AS
FUNCTION release_time
RETURN date
IS
BEGIN
RETURN pck_release.timestamp;
END;
END;


At first I had a single row table with that timestamp as part of the query but that did not make any difference in the optimizer plan. It actually looked worse.

Likse so :
create table release ( time date);
insert into release values (sysdate);


select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
from dbentry_vw
where dbentryid = 67846230

CREATE OR REPLACE VIEW
dbentry_vw
AS
SELECT DBENTRYID,TIMESTAMP
FROM (
SELECT dbentryid, timestamp
FROM datalib.dbentry
,release
WHERE timestamp < time
UNION
SELECT dbentryid, timestamp
FROM datalib.dbentry_audit
,release
WHERE timestamp < time
ORDER BY timestamp DESC
)
WHERE ROWNUM =1






Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7034048 Card=114688979 Bytes=2523157538)
1 0 VIEW OF 'DBENTRY_VW' (Cost=7034048 Card=114688979 Bytes=2523157538)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=7034048 Card=114688979 Bytes=2523157538)
4 3 SORT (UNIQUE STOPKEY) (Cost=5173213 Card=114688979 Bytes=2677395608)
5 4 UNION-ALL
6 5 MERGE JOIN* (Cost=424209 Card=37569944 Bytes=826538768) :Q192870
01

7 6 SORT* (JOIN) (Cost=8 Card=21 Bytes=189) :Q192870
01

8 7 TABLE ACCESS* (FULL) OF 'RELEASE' (Cost=1 Card=21 Bytes=189) :Q192870
00

9 6 SORT* (JOIN) (Cost=424201 Card=35780899 Bytes=465151687) :Q192870
01

10 9 TABLE ACCESS* (FULL) OF 'DBENTRY' (Cost=71207 Card=35780899 Bytes=465151687) :Q192870
01

11 5 MERGE JOIN (Cost=1964276 Card=77119035 Bytes=1850856840)
12 11 SORT (JOIN) (Cost=8 Card=21 Bytes=189)
13 12 TABLE ACCESS (FULL) OF 'RELEASE' (Cost=1 Card=21 Bytes=189)
14 11 SORT (JOIN) (Cost=1964268 Card=73446700 Bytes=1101700500)
15 14 TABLE ACCESS (FULL) OF 'DBENTRY_AUDIT' (Cost=429674 Card=73446700 Bytes=1101700500)


6 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A1.C0,A2.C0,A1.C1,A2.C1,A2.C2 FROM :Q19287
000 A1,(SELECT /*+ NO_EXPAND ROWID(A3) */ A3
."TIMESTAMP" C0,A3."DBENTRYID" C1,A3."TIMEST
AMP" C2 FROM "DATALIB"."DBENTRY" PX_GRANULE(
0, BLOCK_RANGE, DYNAMIC) A3) A2 WHERE A2.C0
<A1.C0

7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_FROM_SERIAL
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT





Tom Kyte

Followup  

February 26, 2004 - 4:20 pm UTC

what does:

CREATE OR REPLACE VIEW
dbentry_vw
AS
SELECT DBENTRYID,TIMESTAMP
FROM (
select * from
(SELECT /*+ FIRST_ROWS */ dbentryid, timestamp
FROM datalib.dbentry
,release
WHERE timestamp < time
order by timestamp desc)
where rownum = 1
UNION
select * from
(SELECT /*+ FIRST_ROWS */ dbentryid, timestamp
FROM datalib.dbentry_audit
,release
WHERE timestamp < time
ORDER BY timestamp DESC)
where rownum = 1
order by timestamp desc
)
WHERE ROWNUM =1


do?

still same problem

February 27, 2004 - 5:19 am UTC

Reviewer: Mark from Cambs, UK


That view - for some reason gives an error.

CREATE OR REPLACE VIEW
dbentry_vw
AS
SELECT DBENTRYID,TIMESTAMP
FROM (
select * from
(SELECT /*+ FIRST_ROWS */ dbentryid, timestamp
FROM datalib.dbentry
,release
WHERE timestamp < time
order by timestamp desc)
where rownum = 1
UNION
select * from
(SELECT /*+ FIRST_ROWS */ dbentryid, timestamp
FROM datalib.dbentry_audit
,release
WHERE timestamp < time
ORDER BY timestamp DESC)
where rownum = 1
order by timestamp desc
)
WHERE ROWNUM =1

/

order by timestamp desc
*
ERROR at line 21:
ORA-00904: invalid column name



When done without that line (which was redundant), the view works but first_rows did not give a better result.


CREATE OR REPLACE VIEW
dbentry_vw
AS
SELECT DBENTRYID,TIMESTAMP
FROM (
select * from
(SELECT /*+ FIRST_ROWS */ dbentryid, timestamp
FROM datalib.dbentry
,release
WHERE timestamp < time
order by timestamp desc)
where rownum = 1
UNION
select * from
(SELECT /*+ FIRST_ROWS */ dbentryid, timestamp
FROM datalib.dbentry_audit
,release
WHERE timestamp < time
ORDER BY timestamp DESC)
where rownum = 1
--order by timestamp desc
)
WHERE ROWNUM =1

/

View created.

select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
from dbentry_vw
where dbentryid = 67846230
/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=7944072 Card=114688979 Bytes=2523157538)
1 0 VIEW OF 'DBENTRY_VW' (Cost=7944072 Card=114688979 Bytes=2523157538)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=7944072 Card=114688979 Bytes=2523157538)
4 3 SORT (UNIQUE STOPKEY) (Cost=7944072 Card=114688979 Bytes=2523157538)
5 4 UNION-ALL
6 5 COUNT (STOPKEY)
7 6 VIEW (Cost=1106323 Card=37569944 Bytes=826538768)
8 7 SORT (ORDER BY STOPKEY) (Cost=1106323 Card=37569944 Bytes=826538768)
9 8 NESTED LOOPS (Cost=529695 Card=37569944 Bytes=826538768)
10 9 TABLE ACCESS (FULL) OF 'RELEASE' (Cost=1 Card=21 Bytes=189)
11 9 TABLE ACCESS (BY INDEX ROWID) OF 'DBENTRY' (Cost=25224 Card=35780899 Bytes=46515
1687)

12 11 INDEX (RANGE SCAN) OF 'I_DBENTRY_TIMESTAMP' (NON-UNIQUE) (Cost=16356 Card=3578
0899)

13 5 COUNT (STOPKEY)
14 13 VIEW (Cost=4172376 Card=77119035 Bytes=1696618770)
15 14 SORT (ORDER BY STOPKEY) (Cost=4172376 Card=77119035 Bytes=1850856840)
16 15 MERGE JOIN (Cost=1964276 Card=77119035 Bytes=1850856840)
17 16 SORT (JOIN) (Cost=8 Card=21 Bytes=189)
18 17 TABLE ACCESS (FULL) OF 'RELEASE' (Cost=1 Card=21 Bytes=189)
19 16 SORT (JOIN) (Cost=1964268 Card=73446700 Bytes=1101700500)
20 19 TABLE ACCESS (FULL) OF 'DBENTRY_AUDIT' (Cost=429674 Card=73446700 Bytes=110170
0500)

Tom Kyte

Followup  

February 27, 2004 - 7:36 am UTC

I messed up on that anyway.

but, then again -- so did you!!!

In looking at this more closely:


CREATE OR REPLACE VIEW DBENTRY_VW
AS
 SELECT * FROM (
     SELECT  dbentryid, timestamp
     FROM     datalib.dbentry
     WHERE    timestamp < pck_release.release_time
     UNION
     SELECT   dbentryid, timestamp
     FROM     datalib.dbentry_audit
     WHERE    timestamp < pck_release.release_time
     ORDER BY timestamp DESC
 )
 WHERE ROWNUM =1
/
View created.


<b>That view will get the maximum DBEntryId all right -- but it'll be a "constant one".  You see the order by, union and then WHERE ROWNUM=1 is applied AND THEN:</b>


select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
from dbentry_vw
where dbentryid =  67846230
/

"where dbentryid = xxxxxx" would be applied -- AFTER the where rownum.  You cannot incorporate into your view the where rownum=1.  That happens FIRST and then the predicate in your query is applied.  Consider:


ops$tkyte@ORA920PC> create table dbentry( dbentryid number, timestamp date );
 
Table created.
 
ops$tkyte@ORA920PC> create table dbentry_audit( dbentryid number, timestamp date );
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into dbentry values ( 67846230, sysdate-1);
 
1 row created.
 
ops$tkyte@ORA920PC> insert into dbentry_audit values ( 67846230, sysdate-1);
 
1 row created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into dbentry values ( 123, sysdate-1/2);
 
1 row created.
 
ops$tkyte@ORA920PC> insert into dbentry_audit values ( 123, sysdate-1/2);
 
1 row created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> CREATE OR REPLACE VIEW DBENTRY_VW
  2  AS
  3   SELECT * FROM (
  4       SELECT  dbentryid, timestamp
  5       FROM     dbentry
  6       WHERE    timestamp < sysdate
  7       UNION
  8       SELECT   dbentryid, timestamp
  9       FROM     dbentry_audit
 10       WHERE    timestamp < sysdate
 11       ORDER BY timestamp DESC
 12   )
 13   WHERE ROWNUM =1
 14  /
 
View created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
  2  from dbentry_vw
  3  where dbentryid =  67846230
  4  /
 
no rows selected
 
ops$tkyte@ORA920PC> select dbentryid, to_char(timestamp, 'dd-mon-yyyy hh24:mi:ss')
  2  from dbentry_vw
  3  where dbentryid =  123
  4  /
 
 DBENTRYID TO_CHAR(TIMESTAMP,'D
---------- --------------------
       123 26-feb-2004 19:35:17



you cannot do the rownum part in your view, not possible unless you want to parameterize the query against dbentry with dbentryid just like you did with the timestamp column. 

Doh !

February 27, 2004 - 9:56 am UTC

Reviewer: Mark from Cambridge, UK

Haha, I did mess it up eh ? The query ran so long I didn't let it finish to see the view was incorrect in the first place. Next time I'll be more patient.

I don't see how to do it in a view now. But thanks for the help !



Another Rownum question 

June 06, 2004 - 6:43 pm UTC

Reviewer: Tobias from Canada

Hi Tom,

I'd like to number rows like the code example (copied again below) as I've got a very similar problem to solve, except doing it without analytical functions (I'm stuck with 8.1.7.4 SE) :-(

Table ITEMS with Fields MAIN, SUB & CODE
--------------------------------------------

MAIN SUB CODE
=======================
01 DXFDF 1
01 SDYHJ 2
01 SDFHJ 3
01 EWEHJ 4

02 TYTYY 1
02 TYTY 2
02 IGHGH 3

03 BHGHG 1
03 SDFHH 2



Will I have to do it procedurally (e.g. insert into a table and loop through to number the records in each different group) without analytical functions being available?

thanks

Tom Kyte

Followup  

June 07, 2004 - 8:08 am UTC

you'll have to do it procedurally, in the client code. I would not suggest stuffing it into a temporary table -- just have the client assign the numbers as it fetched the data. Simple control break processing -- "if last_main <> curr_main then code = 1, last_main = curr_main; else code = code+1 end if" type of processing.

rownum equivalent in sql server

September 03, 2004 - 10:02 am UTC

Reviewer: Asim Naveed from Pakistan

Hi,

Can anyone tell me Oracle's ROWNUM equivalent in
SQL Server, Note that Identity property will not solve
my problem, I want exactly ROWNUM's equivalent in
SQL Server

Tom Kyte

Followup  

September 03, 2004 - 1:13 pm UTC

try askbill@microsoft.com maybe?

oh yeah, sqlserver isn't oracle isn't sqlserver isn't informix isn't oracle isn't db2 isn't redbrick isn't oracle isn't sybase ins't.....

don't expect "to find equivalent functionality or even mapping of functionality", you'll only be disappointed.

> rownum

September 16, 2005 - 10:07 am UTC

Reviewer: Alay from India

Hi Tom,
Yoy have give logic for rownum as follows:
rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;

We can use <, <= with rownum, but what about > and >= ? Can you explain it as per the above logic?

Tom Kyte

Followup  

September 16, 2005 - 10:15 am UTC

look at the psuedo code.

If you say

"where rownum > 2"

well, rownum starts at 1 and only gets incremented upon successful output. Therefore rownum STAYS at one since we never output.

SORT(ORDER BY STOPKEY)

September 28, 2005 - 11:09 am UTC

Reviewer: A reader

Hi Tom,

I'm puzzled about the two sql.
The first sql is rownum>=21 and rownum<=30,the second is rownum>=31 and rownum<=40.
The result should be different,but actually it's repeated,it's the same.
I can't explain the issue.
Could you help me?

SQL> select toneid, tonecode, r
  2    from (select toneid, tonecode, rownum r
  3            from (select toneid, tonecode, status, downloadtimes,
  4                         uploadtype, spid, spcode
  5                    from t_fulltonelib
  6                   where status in (1, 2)
  7                     and spid = 2
  8                     and uploadtype = 1
  9                   order by downloadtimes desc
 10                 )
 11           where rownum <= 30
 12         )
 13   where r >= 21;

    TONEID TONECODE                      R
---------- -------------------- ----------
       127 600888000000000044           21
       367 600888000000000120           22
       118 600888000000000035           23
       111 600888000000000028           24
       103 600888000000000020           25
       293 600888000000000116           26
        95 600888000000000012           27
       458 600888000000000134           28
       457 600888000000000133           29
       456 600888000000000132           30




Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COUNT (STOPKEY)
   3    2       VIEW
   4    3         SORT (ORDER BY STOPKEY)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'T_FULLTONELIB'
   6    5             INDEX (RANGE SCAN) OF 'IX_FULLTONELIB_SPID' (NON-UNIQUE)


SQL> select toneid, tonecode, r
  2    from (select toneid, tonecode, rownum r
  3            from (select toneid, tonecode, status, downloadtimes,
  4                         uploadtype, spid, spcode
  5                    from t_fulltonelib
  6                   where status in (1, 2)
  7                     and spid = 2
  8                     and uploadtype = 1
  9                   order by downloadtimes desc
 10                 )
 11           where rownum <= 40
 12         )
 13   where r >= 31;

    TONEID TONECODE                      R
---------- -------------------- ----------
       127 600888000000000044           31
       367 600888000000000120           32
       118 600888000000000035           33
       111 600888000000000028           34
       103 600888000000000020           35
       293 600888000000000116           36
        95 600888000000000012           37
       458 600888000000000134           38
       457 600888000000000133           39
       456 600888000000000132           40




Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COUNT (STOPKEY)
   3    2       VIEW
   4    3         SORT (ORDER BY STOPKEY)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'T_FULLTONELIB'
   6    5             INDEX (RANGE SCAN) OF 'IX_FULLTONELIB_SPID' (NON-UNIQUE)

===================================================================

When I change the sql——where r>=31 and r<=40, the issue is solved,any combination of rownum doesn't lead to the result repeat.
What is ORDER BY STOPKEY?
Could you explain the internal operational principle of ORDER BY STOPKEY?

SQL> select toneid, tonecode, r
  2    from (select toneid, tonecode, rownum r
  3            from (select toneid, tonecode, status, downloadtimes,
  4                         uploadtype, spid, spcode
  5                    from t_fulltonelib
  6                   where status in (1, 2)
  7                     and spid = 2
  8                     and uploadtype = 1
  9                   order by downloadtimes desc
 10                 )
 11         )
 12   where r >= 31 and r <= 40;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COUNT
   3    2       VIEW
   4    3         SORT (ORDER BY)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'T_FULLTONELIB'
   6    5             INDEX (RANGE SCAN) OF 'IX_FULLTONELIB_SPID' (NON-UNIQUE)


Thank you very much  

Tom Kyte

Followup  

September 28, 2005 - 11:24 am UTC

is downloadtimes unique?

September 29, 2005 - 12:54 am UTC

Reviewer: A reader

Hi Tom

the downloadtimes is not unique
the toneid is unique
the tonecode is unique too.


Thank you very much.

Tom Kyte

Followup  

September 29, 2005 - 7:02 am UTC

order by downloadtime, toneid


then. if you order by download time - the "ordering" is not deterministic. If you have 100 rows with downloadtime= 5, which are the first five????

September 30, 2005 - 7:34 am UTC

Reviewer: A reader

Hi Tom

Thank you in advanced.

I can't understand why the third sql is ok.
The result is correct when I change the sql into r >= 31 and r <= 40.

I found that the result is correct when the SORT(ORDER BY STOPKEY) is not used.

I'm bemused.

Could you explain it to me?

Thank you very very very much.

Tom Kyte

Followup  

September 30, 2005 - 9:42 am UTC

order by something distinct.

else, you tell me -- is this order of rows correct:


downloadtime name
--------------- ----------
5 bob
5 mary
5 sue


or is this one:


downloadtime name
--------------- ----------
5 sue
5 mary
5 bob

or are the both the same???? They are the same when ordering by downloadtime and if we ask for the "first one", anyone of the three is "correct"

Unless the rows have a precise order.....

And you r >= 31 and r <= 40 has the same problem -- the rows do not have to sort the same, they might accidently - and then later they might stop.


make the order by deterministic.



Incredibly helpful for paginating Web queries

November 15, 2005 - 2:18 pm UTC

Reviewer: David from Washington, DC

Thanks for everyone for this discussion! Rownum looks very helpful for paginating query results for a Web interface, when used correctly.

'SORT ORDER BY STOPKEY'

April 02, 2006 - 6:44 am UTC

Reviewer: Davis from China

Dear Tom:
I have a question about 'SORT ORDER BY STOPKEY';

here is my test steps:

--create test table
create table t_testorder
( i_index number,
str_1 varchar2(30)
);

--insert test data
begin
insert into t_testorder values(1, 'aaaa');
insert into t_testorder values(2, 'aaaa');
insert into t_testorder values(3, 'aaaa1');
insert into t_testorder values(4, 'aaaa');
insert into t_testorder values(5, 'aaaa');
insert into t_testorder values(6, 'aaaa');
insert into t_testorder values(7, 'aaaa');
insert into t_testorder values(8, 'aaaa4');
insert into t_testorder values(9, 'aaaa');
insert into t_testorder values(10, 'aaaa');
insert into t_testorder values(11, 'aaaa');
insert into t_testorder values(12, 'aaaa');
insert into t_testorder values(13, 'aaaa5');
insert into t_testorder values(14, 'aaaa');
insert into t_testorder values(15, 'aaaa');
insert into t_testorder values(16, 'aaaa');
insert into t_testorder values(17, 'aaaa');
insert into t_testorder values(18, 'aaaa8');
insert into t_testorder values(19, 'aaaa');
insert into t_testorder values(20, 'aaaa');
end;

--I execute two query statement
--statement1
select *
from (select *
from t_testorder
order by str_1
)
where rownum <= 10

--statement2
select *
from (select *
from t_testorder
order by str_1
)
where rownum <=20

--execute plan
SELECT STATEMENT, GOAL = CHOOSE
COUNT STOPKEY
VIEW Object owner=TEST_SCHEMA
SORT ORDER BY STOPKEY
TABLE ACCESS FULL Object owner=TEST_SCHEMA Object name=T_TESTORDER

--out put
-- statement1 statement2
i_index str_1 i_index str_1
------- ------- ------- -------
1 aaaa 1 aaaa
2 aaaa 2 aaaa
4 aaaa 4 aaaa
6 aaaa 6 aaaa
7 aaaa 7 aaaa
10 aaaa 10 aaaa
12 aaaa 12 aaaa
14 aaaa 11 aaaa
16 aaaa 9 aaaa
20 aaaa 5 aaaa
19 aaaa
17 aaaa
15 aaaa
11 aaaa
9 aaaa
5 aaaa
3 aaaa1
8 aaaa4
13 aaaa5
18 aaaa8

My question is: Why the first 10 output record of the two statements is different.
I guess it caused by 'SORT ORDER BY STOPKEY', but I don't kown the mechanism behind it.
(supplementary: I execute statement1 10 times, the out put is consistent, it doesn't change.)

Tom Kyte

Followup  

April 02, 2006 - 11:22 am UTC

well, you ordered by str_1 and asked for

10 rows one time
20 rows the next time.


there are more than 10 str_1 values with aaaa - you got precisely what you asked for - the first 10 after sorting by aaaa - both are equally correct.

The reason "why" - the sort order by stopkey is a TOP-N optimization.  When you said "10", we CONCEPTUALLY 

a) set up an array of 10
b) read the first 10 records and sorted them - placing them into the array.
c) read the 11th record - to see if the key is less than the 10th key in this array already - if it was, kick out the 10th record and insert this one into the proper place in the array.


Then you did it with 20, we got a different set of 20 records and did the same thing.


It is all because your key str_1 had more than 10.


Both answers are absolutely 100% correct (but the question you are asking is going to lead to "random" results - if you just unload your data - truncate the table and load the rows in a different order - you might well get a different answer!!!! look to your QUESTION and ask about it's validity)


ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t
  2  as
  3  select 'aaaa' a, all_users.*
  4    from all_users;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (select *
  3            from t
  4                   order by a)
  5   where rownum <= 5;

A    USERNAME                          USER_ID CREATED
---- ------------------------------ ---------- ---------
aaaa SYS                                     0 06-DEC-03
aaaa SYSTEM                                  5 06-DEC-03
aaaa OUTLN                                  11 06-DEC-03
aaaa DBSNMP                                 19 06-DEC-03
aaaa WMSYS                                  21 06-DEC-03

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t
  2  as
  3  select 'aaaa' a, all_users.*
  4    from all_users
  5   order by username;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from (select *
  3            from t
  4                   order by a)
  5   where rownum <= 5;

A    USERNAME                          USER_ID CREATED
---- ------------------------------ ---------- ---------
aaaa A                                      80 01-APR-06
aaaa ANONYMOUS                              36 06-DEC-03
aaaa BIG_TABLE                              68 05-OCT-05
aaaa CTXSYS                                 33 06-DEC-03
aaaa DBSNMP                                 19 06-DEC-03



Both are 100% accurate, correct, rational.

Neither of the above likely "makes sense" however given the sort key we used! 

SORT ORDER BY STOPKEY

April 03, 2006 - 5:20 am UTC

Reviewer: Davis from China

When using "sort order by stopkey" Oracle always read the TOP-N rows by physical order of table then sort the "N" rows.

If col_x is non-unique, it causes some repeated results in web paging when "N" changed.

select *
from(
select a.*, rownum r
from ( select *
from t
order by col_x) a
where rownum<=N
)
where rownum>=M

But the result is not repeated when "N" changed if column col_x has a index, because Oracle read the index and ignore sorting.

select *
from(
select a.*, rownum r
from ( select *
from t
order by col_x) a
)
where r between N and M

--The results are always fixed, because all of the rows are sorted, but it is low efficiency.

When "N" is very large the both methods are low efficiency. How to make it high efficiency when "N" is very large?

Thank you in advance.

Tom Kyte

Followup  

April 03, 2006 - 8:16 am UTC

do not let N get large!

reference google!


tell me what is on page 101 when you search for Oracle on google?

and tell me which page is "faster" - page 1 or page 90 on google.


optimize for the search results, don't let people go past page N, if they need to get last page - that is called "order by DESC" and the last page is now page 1

Rownum = 1 Versus Rownum < 2

August 06, 2006 - 12:47 am UTC

Reviewer: A reader

I have a table with 15 million rows and 30 columns.

There are no indexes. The table is not analyzed.

Select * from Rownum < 2
Takes less than one second.

Select * from Rownum = 1
Takes more than a minute.

1) Why the Time difference.
2)How Is Rownum = 1 and Rownum < 2 evaluated ?

3)I analyzed the tables with compute statistics.
Still the same behaviour.

4)Why cant we analyze a synonym ?
I created a synonym for a table.
I tried analyze it says table or view doesnot exists.


Tom Kyte

Followup  

August 06, 2006 - 9:02 am UTC

sql_trace it, tkprof it. see if there isn't anything "obvious"

I cannot reproduce it.


4) because it is a synonym, you don't analyze synonyms, you gather statistics on tables, indexes....

Rownum = 1 Versus Rownum < 2 DB version 9.2

August 06, 2006 - 12:48 am UTC

Reviewer: A reader

I have a table with 15 million rows and 30 columns.

There are no indexes. The table is not analyzed.

Select * from Rownum < 2
Takes less than one second.

Select * from Rownum = 1
Takes more than a minute.

1) Why the Time difference.
2)How Is Rownum = 1 and Rownum < 2 evaluated ?

3)I analyzed the tables with compute statistics.
Still the same behaviour.

4)Why cant we analyze a synonym ?
I created a synonym for a table.
I tried analyze it says table or view doesnot exists.


November 02, 2006 - 11:50 am UTC

Reviewer: Shibdas Roy

Hi Sir,

Can anyone suggest me which method is better to check if at least one record exists in a table? I personally don't prefer the use of ROWNUM for doing just an existence check. ROWNUM is an efficient tool for Top-N Analysis and Pagination. But, is it advisable to use rownum = 1?

Select 1 into variable from T where rownum = 1. is there anything better than this to check for the existence of the record in a table

Regards
Shibdas



Tom Kyte

Followup  

November 02, 2006 - 12:16 pm UTC

I would ask the question "why do you care, since the split instant after you ask, the answer may well be very very different".

Why do you need to know? If you have logic like:

select .... - check for a record
if (there is a record)
then
process it
end if


I recommend you just turn that into:

process it


period, and let process it STOP if there is nothing to process!!!!!!



where rownum=1 works dandy. If the table was ONCE very large and now it empty, it could take a long time if it decides to full scan. So you might:


select count(NON-NULL-INDEXED-COLUMN) from t where rownum=1;

using the cbo, the count of a non-null column that has an index on it should use an index scan instead of a full table scan (avoiding hitting lots of empty blocks looking for the first row)

November 02, 2006 - 12:42 pm UTC

Reviewer: Shibdas

Is my understanding correct then
That for small tables having couple of record or for a huge table where data has not been deleted, the rownum query would work fine.

Rregards
Shibdas

Tom Kyte

Followup  

November 02, 2006 - 7:23 pm UTC

sure

Autotrace with ROWNUM=1 and ROWNUM<2

February 24, 2007 - 2:44 am UTC

Reviewer: Dakshinamurty from INDIA

SQL> set timing on
SQL> set autot on
SQL> set head off
SQL> select * from adt where rownum<2;

D11305008 TDT
M68967759 /003
001 060400 M68967759 10100 36321LMA 28561RKA
ACCOUNT AUTHORIZED AND NEW NUMBER ASSIGNED IS ^0604003100006017^
29-10-2003
RECORD ADDED|||
A 060400 29-10-2003


Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2696162112

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |

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

| 0 | SELECT STATEMENT | | 1 | 148 | 2 (0)| 00:00:0
1 | | |

|* 1 | COUNT STOPKEY | | | | |
| | |

| 2 | PARTITION HASH ALL| | 1 | 148 | 2 (0)| 00:00:0
1 | 1 | 10 |

| 3 | TABLE ACCESS FULL| AUDIT_TABLE | 1 | 148 | 2 (0)| 00:00:0
1 | 1 | 10 |

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


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

1 - filter(ROWNUM<2)


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

SQL> select * from adt where rownum=1;

D11305008 TDT
M68967759 /003
001 060400 M68967759 10100 36321LMA 28561RKA
ACCOUNT AUTHORIZED AND NEW NUMBER ASSIGNED IS ^0604003100006017^
29-10-2003
RECORD ADDED|||
A 060400 29-10-2003


Elapsed: 00:22:45.74

Execution Plan
----------------------------------------------------------
Plan hash value: 2696162112

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |

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

| 0 | SELECT STATEMENT | | 1 | 148 | 2 (0)| 00:00:0
1 | | |

|* 1 | COUNT STOPKEY | | | | |
| | |

| 2 | PARTITION HASH ALL| | 1 | 148 | 2 (0)| 00:00:0
1 | 1 | 10 |

| 3 | TABLE ACCESS FULL| AUDIT_TABLE | 1 | 148 | 2 (0)| 00:00:0
1 | 1 | 10 |

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


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

1 - filter(ROWNUM=1)


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

--FACTS ABOUT ADT

SQL> set timing off
SQL> set autot off
SQL> set head on

SQL> select table_name, table_owner,owner from all_synonyms where synonym_name='ADT';

TABLE_NAME TABLE_OWNER
------------------------------ ------------------------------
OWNER
------------------------------
AUDIT_TABLE ADMIN
PUBLIC

SQL> show user
USER is "ADMIN"

SQL> select last_analyzed,num_rows from user_tables where table_name='AUDIT_TABLE';

LAST_ANALY NUM_ROWS
---------- ----------
15-02-2007 265740617

SQL> select count(*) from adt;

COUNT(*)
----------
265781516

SQL> desc AUDIT_TABLE
Name Null? Type
----------------------------------------- -------- ----------------------------
REF_NUMBER VARCHAR2(15)
TABLE_NAME VARCHAR2(7)
TABLE_KEY VARCHAR2(100)
AUDIT_SRL_NUMBER VARCHAR2(3)
AUDIT_PRODUCT_ID VARCHAR2(8)
ACCOUNT_ID VARCHAR2(11)
HEAD_CODE VARCHAR2(5)
ENTERER_ID VARCHAR2(15)
AUTH_ID VARCHAR2(15)
RMKS VARCHAR2(100)
AUDIT_DATE DATE
MODIFIED_FIELDS_DATA VARCHAR2(240)
FUNC_CODE CHAR(1)
INIT_PRODUCT_ID VARCHAR2(8)
AUDIT_BEGIN_DATE DATE
CALLER_MODULE CHAR(1)

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.2.0


Tom Kyte

Followup  

February 26, 2007 - 12:57 pm UTC

(hit the CODE button please - fixed font = good for code)\

In any case, I cannot reproduce at all - can you help us reproduce this from start to finish?

suggest you might try a sql_trace on it yourself as well to see what it is doing




ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (object_id,object_name,object_type)
  3  PARTITION BY hash (object_id) partitions 8
  4  as
  5  select object_id, object_name, object_type
  6    from all_objects
  7  /

Table created.

ops$tkyte%ORA10GR2> set termout off
ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select * from t where rownum = 1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3652464552

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    41 |     3  (34)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY      |      |       |       |            |          |       |       |
|   2 |   PARTITION HASH ALL|      | 60491 |  2422K|     3  (34)| 00:00:01 |     1 |     8 |
|   3 |    TABLE ACCESS FULL| T    | 60491 |  2422K|     3  (34)| 00:00:01 |     1 |     8 |
--------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)

Note
-----
   - dynamic sampling used for this statement


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

ops$tkyte%ORA10GR2> select * from t where rownum < 2;


Execution Plan
----------------------------------------------------------
Plan hash value: 3652464552

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    41 |     3  (34)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY      |      |       |       |            |          |       |       |
|   2 |   PARTITION HASH ALL|      | 60491 |  2422K|     3  (34)| 00:00:01 |     1 |     8 |
|   3 |    TABLE ACCESS FULL| T    | 60491 |  2422K|     3  (34)| 00:00:01 |     1 |     8 |
--------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<2)

Note
-----
   - dynamic sampling used for this statement


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

ops$tkyte%ORA10GR2> select * from t;

50135 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3609007437

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 60491 |  2422K|    78   (3)| 00:00:01 |       |       |
|   1 |  PARTITION HASH ALL|      | 60491 |  2422K|    78   (3)| 00:00:01 |     1 |     8 |
|   2 |   TABLE ACCESS FULL| T    | 60491 |  2422K|    78   (3)| 00:00:01 |     1 |     8 |
-------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       3741  consistent gets
          0  physical reads
          0  redo size
    2270721  bytes sent via SQL*Net to client
      37147  bytes received via SQL*Net from client
       3344  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50135  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>



Autotrace with ROWNUM=1 and ROWNUM<2

February 24, 2007 - 2:58 am UTC

Reviewer: Dakshinamurty from INDIA

Sorry. Forgot to put my questions. Same questions were asked earlier without any sql trace/tkprof info.

1) Why the Time difference.
2)How Is Rownum = 1 and Rownum < 2 evaluated ?


Tom Kyte

Followup  

February 26, 2007 - 12:58 pm UTC

see above.

ROWNUM on Oracle 10G applied after 'order by' clause?!?!?!?

March 15, 2007 - 11:30 am UTC

Reviewer: Bill Swift from Ann Arbor, MI

This is what I get from Oracle 10G. This shows that the rownum is assigned after the 'order by' clause has been applied.

CREATE TABLE TEMPL_MR
(
TEMPLATE_ID NUMBER(15),
POSITION NUMBER(2));

ALTER TABLE TEMPL_MR ADD (
CONSTRAINT PK_TEMPL_MR PRIMARY KEY (TEMPLATE_ID, POSITION));

INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 2);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 6);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 7);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 10);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 12);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 14);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 18);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 21);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 22);
INSERT INTO TEMPL_MR ( TEMPLATE_ID, POSITION ) VALUES (
480464, 28);
COMMIT;

select rownum, position
from templ_mr
where template_id = 480464
order by position asc;

ROWNUM POSITION
---------- --------
1 2
2 6
3 7
4 10
5 12
6 14
7 18
8 21
9 22
10 28
10 rows selected


select rownum, position
from templ_mr
where template_id = 480464 and
rownum < 3
order by position asc;

ROWNUM POSITION
---------- --------
1 2
2 6
2 rows selected


select rownum, position
from templ_mr
where template_id = 480464
order by position desc;

ROWNUM POSITION
---------- --------
1 28
2 22
3 21
4 18
5 14
6 12
7 10
8 7
9 6
10 2
10 rows selected


select rownum, position
from templ_mr
where template_id = 480464 and
rownum < 3
order by position desc;

ROWNUM POSITION
---------- --------
1 28
2 22
2 rows selected

Since this flys in the face of everything you have posted previously on the subject and I have found you to be 100% correct in the past.......how have I erred in my analysis?
Tom Kyte

Followup  

March 15, 2007 - 12:40 pm UTC

your test above that "proves" rownum is assigned after sorting is akin to "proving" that coins always land heads up by flipping a coin 5 times and seeing it land heads up.

It doesn't prove that coins land heads up.

However, if on the sixth flip I get it to land tails up, I just proved that coins do not always land heads up :)

and here is my sixth coin flip:

ops$tkyte%ORA10GR2> rename templ_mr to x;

Table renamed.

ops$tkyte%ORA10GR2> create table templ_mr as select * from x order by dbms_random.random;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, position
  2  from templ_mr
  3  where template_id = 480464
  4  order by position asc;

    ROWNUM   POSITION
---------- ----------
         6          2
         7          6
         8          7
         1         10
         2         12
         3         14
         4         18
        10         21
         9         22
         5         28

10 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, position
  2  from templ_mr
  3  where template_id = 480464 and
  4      rownum < 3
  5  order by position asc;

    ROWNUM   POSITION
---------- ----------
         1         10
         2         12

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, position
  2  from templ_mr
  3  where template_id = 480464
  4  order by position desc;

    ROWNUM   POSITION
---------- ----------
         5         28
         9         22
        10         21
         4         18
         3         14
         2         12
         1         10
         8          7
         7          6
         6          2

10 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, position
  2  from templ_mr
  3  where template_id = 480464 and
  4      rownum < 3
  5  order by position desc;

    ROWNUM   POSITION
---------- ----------
         2         12
         1         10

ROWNUM on Oracle 10G

March 15, 2007 - 1:18 pm UTC

Reviewer: Bill Swift from Ann Arbor, MI

OK. I see that I made a classic blunder in logic and that your counter example clearly shows that it does not assign rownum until after the 'order by' clause. However, one would be fair to question the randomness of the order of the rows returned given my example. The second query 'just happens to' return the results in the exact opposite order? Surely this is not random, but rather a deterministic function of the order in which the rows were inserted and/or some other factor. I would think that your counter example would indicate that it is only random if the rows are inserted in a random order. Is this just crazy thinking on my part?

I will use the in-line view solution for my current problem. Thanks for setting me straight.
Tom Kyte

Followup  

March 15, 2007 - 1:31 pm UTC

rows are always inserted in a "random order", that yours were stored accidentally sorted by your column of interest was just an unfortunate accident, the use of features such as ASSM would change the entire discussion (rows are not stored in order of insertion), also, subsequent deletes and updates would cause the rows to "get out of order" as well.

Inconsisten rowcount with rownum and aggregate

March 16, 2007 - 8:14 am UTC

Reviewer: Jose from Florence, Indiana

Tom, the following query should return only one row. I see that sometimes it returns 2 rows. It seems to be random. When I take out the order by clause if becomes consistent. Do you why the order by if affecting it?

Query:
SELECT *

FROM

(SELECT DISTINCT "SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."GOAL_ID" AS "GoalId",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CAMPAIGN_SEARCH_ENGINE_ID" AS "CampaignSearchEngineId",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."WEEK_IN_MONTH" AS "WeekInMonth",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CLICKS"), 0) AS "Clicks",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_UNIQUE_CLICKS"), 0) AS "UniqueClicks",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CONVERSIONS"), 0) AS "Conversions",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_VALUED_CONVERSIONS"), 0) AS "ValuedConversions",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_COST"), 0) AS "Cost",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE"), 0) AS "Revenue",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_SE_CLICKS"), 0) AS "SeClicks",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_SE_IMPRESSIONS"), 0) AS "SeImpressoins",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_SE_COST"), 0) AS "SeCost",

nvl(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADVERTISER_SALES"), 0) AS "Sales",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CLICKS"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_COST") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CLICKS")), 2), 0) AS "CPC",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CLICKS"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CLICKS")), 2), 0) AS "EffectiveCPC",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CONVERSIONS"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_COST") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CONVERSIONS")), 2), 0) AS "CPA",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CONVERSIONS"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_CONVERSIONS")), 2), 0) AS "EffectiveCPA",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADVERTISER_SALES") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE")), 2), 0) AS "RevenueShare",

nvl(ROUND(decode(SUM("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE"), 0, 0, (MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE") -MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADVERTISER_SALES")) / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE")) * 100, 2), 0) AS "Margin",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADVERTISER_SALES"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADVERTISER_SALES")), 2), 0) AS "ROI",

nvl(ROUND(decode(MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE"), 0, 0, MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADVERTISER_SALES") / MAX("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CUM_ADC_REVENUE")), 2), 0) AS "EffectiveRevenueShare",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."ISO_CURRENCY_CODE" AS "IsoCurrencyCode", "SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."SYMBOL" AS "Symbol"

FROM "SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"

WHERE(("MV_PME_MTD_OPT_CSE_GOAL_METRIC"."GOAL_ID" = 1375

AND "MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CAMPAIGN_SEARCH_ENGINE_ID" = 86158

AND "MV_PME_MTD_OPT_CSE_GOAL_METRIC"."DAY" BETWEEN '01-MAR-2007'

AND '31-MAR-2007'))

GROUP BY "SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."GOAL_ID",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."CAMPAIGN_SEARCH_ENGINE_ID",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."WEEK_IN_MONTH",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."ISO_CURRENCY_CODE",

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."SYMBOL"

ORDER BY "SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."GOAL_ID" ASC,

"SEM_BOD"."MV_PME_MTD_OPT_CSE_GOAL_METRIC"."WEEK_IN_MONTH" ASC)

WHERE rownum <= 500


Tom Kyte

Followup  

March 17, 2007 - 2:52 pm UTC

I think it should return up to 500 rows.

Not sure why you think otherwise - you must have some inside information about the data I don't.

So, when it gets two rows, are they two rows that could make it out given the query as written. That is, does one of the rows obviously not belong - just by looking at it?

Rownum Returning different results.

May 25, 2007 - 12:23 pm UTC

Reviewer: Divyang Patel from MA. USA

Hi
I have a query
SELECT rownum||'|'||user_id
   FROM table1
   WHERE parent_id = 2

that gives me the result.

1|10
2|11
3|12
4|15
5|18
6|19
7|20
8|21
9|22
10|26
11|27
12|29
13|32
14|36
15|39


However when I put rownum=1 in the where clause, it give me back row 10|26.
   SELECT rownum||'|'||user_id
       FROM table1
       WHERE parent_id = 2
       and rownum = 1

returns
1|26

I was expecting a value of : 1|10
Is rownum based on any row identifier like, row address.I'm not sure why I'm
Tom Kyte

Followup  

May 26, 2007 - 12:12 pm UTC

rownum is a psuedo column assigned to rows AFTER the where clause (sort of during)

rownum := 1;
for x in (select * from table1 where parent_id=2)
loop
   if (rownum = 1)
   then
      output;
      rownum = rownum + 1;
   end if;
end loop;


that is sort of the psuedo code logic there. Both answers above are absolutely acceptable - a row doesn't have a rownum, a tuple in a result set if temporarily assigned one.





Comment to the "rownum" question

May 28, 2007 - 2:53 am UTC

Reviewer: Todor Botev

I would like to shortly coment the last posting (the "rownum" question). I think an explain plan or - even better - a trace file - will give a good understanding of why the "unexpected" row comes out of the second query. My expectation is it happens the following way:

SELECT rownum||'|'||user_id
FROM table1
WHERE parent_id = 2

The fact that the rows are returned sorted by user_id makes me think that an index MIGHT have taken an effect here. I expect there is an index on user_id or (parent_id,user_id). Oracle needs to return some (unknown) number of rows so in this case the index is scanned and that's why the rows are returned sorted.

SELECT rownum||'|'||user_id
FROM table1
WHERE parent_id = 2
and rownum = 1

Only one row needs to come out - hence Oracle just grasps the first row matching the condition. No index, no sorted output.


rownum

May 28, 2007 - 9:51 am UTC

Reviewer: A reader

*why* is

select rownum from dual connect by rownum <= 57

bad ?
Tom Kyte

Followup  

May 30, 2007 - 10:11 am UTC

*where* do we say that?

May 30, 2007 - 10:00 am UTC

Reviewer: john

Hi,
I have problem with finding missing number in a column.
I have a column ID in table Member table.

Values of ID column fall in range between 10 to 100.

How to find out missing values in column.

Thanks..
Tom Kyte

Followup  

May 30, 2007 - 3:48 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3225867788098
read the entire page, more than one way is presented.

you said that on

May 30, 2007 - 5:24 pm UTC

Reviewer: A reader

Tom Kyte

Followup  

May 30, 2007 - 6:26 pm UTC

and I answered that right there???

You want to use an inline view or with subquery factoring.


ops$tkyte%ORA9IR2> select rownum from dual connect by level < 10;

    ROWNUM
----------
         1

ops$tkyte%ORA9IR2> with data as (
  2  select rownum from dual connect by level < 10 )
  3  select * from data;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.


in 9i, dual was "known" to be a single row - the inline view/with subquery works.

November 12, 2007 - 7:14 am UTC

Reviewer: zhuojm from ching

when i exec
select * from dual where rownum=1.9;
one row will be selected ?
why?

Tom Kyte

Followup  

November 16, 2007 - 12:58 pm UTC

I filed a bug on this, it is wrong.

lol

November 17, 2007 - 1:15 pm UTC

Reviewer: Sokrates

to zhuojm
great bug you found !
*****


November 21, 2007 - 11:31 am UTC

Reviewer: zhuojm from CHINA

thanks tom!

SORT ORDER BY STOPKEY

August 06, 2008 - 7:41 pm UTC

Reviewer: A reader

In the predicate information section, why does the filter "rownum <= 10" appear twice (in step 1 and 3)?

CREATE TABLE x AS
SELECT * FROM all_objects
WHERE  rownum <= 100;

SELECT *
FROM (
   SELECT *
   FROM   x
   ORDER  BY object_name
)
WHERE  rownum <= 10;

---------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    10 |  2030 |     4  (25)|
|*  1 |  COUNT STOPKEY          |      |       |       |            |
|   2 |   VIEW                  |      |   100 | 20300 |     4  (25)|
|*  3 |    SORT ORDER BY STOPKEY|      |   100 | 20300 |     4  (25)|
|   4 |     TABLE ACCESS FULL   | X    |   100 | 20300 |     3   (0)|
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)


Tom Kyte

Followup  

August 07, 2008 - 12:28 pm UTC

sort order by stopkey is an indication that we are doing a top-n query optimization.

the count stopkey is the actual "we limit to 10 rows"

it is OK, normal and very desirable to see the sort order by stopkey line.

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
see "Top-N Query Processing with ROWNUM" on that page

you definitely want this step to happen and to have the rownum <= value available to it.

August 07, 2008 - 5:31 pm UTC

Reviewer: A reader

Thanks. The article is very useful. After reading it, I ran a few tests and have a few more questions on the execution plans for the pagination SQL using rownum.

CREATE TABLE x AS
SELECT * FROM all_objects
WHERE  rownum <= 10000;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => user,
    tabname          => 'X'
  );
END;
/

SELECT *
FROM (
   SELECT a.*, rownum rn
   FROM (
      SELECT *
      FROM   x
      ORDER  BY object_name
   ) a
   WHERE  rownum <= 10
)
WHERE  rn >= 1;

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    10 |  2160 |       |   232   (1)| 00:00:03 |
|*  1 |  VIEW                    |      |    10 |  2160 |       |   232   (1)| 00:00:03 |
|*  2 |   COUNT STOPKEY          |      |       |       |       |            |          |
|   3 |    VIEW                  |      | 10000 |  1982K|       |   232   (1)| 00:00:03 |
|*  4 |     SORT ORDER BY STOPKEY|      | 10000 |   830K|  2296K|   232   (1)| 00:00:03 |
|   5 |      TABLE ACCESS FULL   | X    | 10000 |   830K|       |    32   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)



So far so good. Oracle is using the Top-N Optimization. I was wondering if I'd be able to improve the performance by creating an index on object_Name (the column used for sorting).

CREATE INDEX x_n1 ON x(object_name);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => user,
    tabname          => 'X',
    cascade          => TRUE);
END;
/

SELECT *
FROM (
   SELECT a.*, rownum rn
   FROM (
      SELECT *
      FROM   x
      ORDER  BY object_name
   ) a
   WHERE  rownum <= 10
)
WHERE  rn >= 1;

----------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    10 |  2160 |     2   (0)|
|*  1 |  VIEW                          |      |    10 |  2160 |     2   (0)|
|*  2 |   COUNT STOPKEY                |      |       |       |            |
|   3 |    VIEW                        |      |    10 |  2030 |     2   (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| X    | 10000 |   830K|     2   (0)|
|   5 |      INDEX FULL SCAN           | X_N1 |    10 |       |     1   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)



Question #1 - Why isn't the "rownum <= 10" applied on the index read? I was expecting that Oracle will read only the first 10 entries from the index since index entries are already sorted. I can't really tell from the execution plan whether Oracle is actually doing that or not (I see "rownum<=10" being applied in operation ID 2. Also Operation ID 4 returned 10,000 rows instead of 10 rows).


Now suppose I want to sort the SQL by object_name and object_id so that I won't see duplicate records across pages.

CREATE UNIQUE INDEX x_u2 ON x (object_name, object_id);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => user,
    tabname          => 'X',
    cascade          => TRUE);
END;
/
SELECT *
FROM (
   SELECT a.*, rownum rn
   FROM (
      SELECT *
      FROM   x
      ORDER  BY object_name, object_id
   ) a
   WHERE  rownum <= 10
)
WHERE  rn >= 1;

------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    10 |  2160 |       |   232   (1)|
|*  1 |  VIEW                    |      |    10 |  2160 |       |   232   (1)|
|*  2 |   COUNT STOPKEY          |      |       |       |       |            |
|   3 |    VIEW                  |      | 10000 |  1982K|       |   232   (1)|
|*  4 |     SORT ORDER BY STOPKEY|      | 10000 |   830K|  2296K|   232   (1)|
|   5 |      TABLE ACCESS FULL   | X    | 10000 |   830K|       |    32   (0)|
------------------------------------------------------------------------------


Question #2 - Why is Oracle doing a full table scan here? Why wouldn't it use the unique index on (object_Name, object_id)?
Tom Kyte

Followup  

August 07, 2008 - 6:33 pm UTC

the index is used iteratively in the select, the select drives the "goto index" and the step #2 stops it, it doesn't read past what it needs.


I think you made a mistake in your last case, it would use the index - cut and paste the ENTIRE thing from sqlplus:

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

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE UNIQUE INDEX x_u2 ON t (object_name, object_id);

Index created.

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

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> SELECT *
  2  FROM (
  3     SELECT a.*, rownum rn
  4     FROM (
  5        SELECT *
  6        FROM   t
  7        ORDER  BY object_name, object_id
  8     ) a
  9     WHERE  rownum <= 10
 10  )
 11  WHERE  rn >= 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1839585532

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    10 |  1410 |     8   (0)| 00:00:01 |
|*  1 |  VIEW                          |      |    10 |  1410 |     8   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |      |       |       |            |          |
|   3 |    VIEW                        |      |    10 |  1280 |     8   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    | 49844 |  4526K|     8   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | X_U2 |    10 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)


(note in 10g, when you create an index on a non-empty table, it computes statistics - cascade not necessary)

August 07, 2008 - 8:09 pm UTC

Reviewer: A reader

I tried it again. Now it's using the index. I must be doing something wrong the first time. Sorry.

So does this mean that

1. we should always create a composite index on the column we're sorting by plus the column that allows us to uniquely identify a record?

2. If the unique column is rowid, it will always be a full table scan?
Tom Kyte

Followup  

August 07, 2008 - 8:35 pm UTC

1) not always - it is not always possible - in this case it is, since all of the columns are in the same table. But sure, if you are paginating over the rows in a table - it would make sense.

2) currently, it is not realizing that rowid is already sorted in the index..

Further more clear

March 03, 2010 - 3:45 pm UTC

Reviewer: shamma from USA

rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;

I have basic knowledge of PL/SQL. Could you please explain the above logic with real select statements and OUTPUT the row in a PL/SQL form. I mean from the BEGIN to END. I was looking for this logic of processing one row at a time but couldn't find anywhere.
Tom Kyte

Followup  

March 04, 2010 - 8:24 am UTC

ummm, I did that already.

if you need a begin/end to make sense of it, just

begin
... put that code here
end


the select statement was simply "select * from (A) where (X)"

(A) is any query.
(X) is any predicate valid for that query.

September 16, 2010 - 10:05 am UTC

Reviewer: A reader

How ROWNUM works ?

Assume ROWNUM as a COUNTER (ex; V_COUNT).
Its intially value is always =1

Conceptually this is the way how it works.
Loop
Get a row 'OUT' of the 'result set' where the position of the row in the result set=V_COUNT
Assign the current of the V_COUNT to the row.
Give the row to the client
V_COUNT=V_COUNT+1
STOP and EXIT WHEN V_COUNT=the no of rows requested by the user .


Remind that the table is organised as a heap - so Oracle inserts the data into the table(heap) whererver the new data fits when we insert some data and generally we do deletions/insertions into a table -so the Position of a row in a table may not be costant but the position of a row in a result set (select * from table order by salary desc) is always constant .

Hi Tom,

Do you agree with the above conceptual way of ROWNUM ?

Tom Kyte

Followup  

September 16, 2010 - 10:22 am UTC

read my original answer, I presented correct psuedo code there that describes how rownum works.


   rownum = 1
   for x in ( select * from A )
   loop
       if ( x satisifies the predicate ) 
       then
             OUTPUT the row
             rownum = rownum + 1
       end if;
   end loop;


rownum is assigned during the predicate evaluation - but before any sorting/aggregating is performed. That is why:


select * from t where rownum <= 10 order by x;

is very very very different from

select * from (select * from t order by x) where rownum <= 10;



In the former case, we get 10 rows and then sort them, that is the result set.

In the latter case, we return the first ten rows *after* sorting.


September 16, 2010 - 10:36 am UTC

Reviewer: A reader

thanks Tom, How does Oracle get a record from a table ?
if a table has 10 blocks , Oracle will know the start and end address of the blocks .

first it will get the BLOCK with the LOWEST address (fist block) and go on reading the records in the ascending order of addresses . Correct ?

well the records will not come out in the order of insertion as the table is organised as heap .

Tom Kyte

Followup  

September 16, 2010 - 11:35 am UTC

the data dictionary records all of the relevant information on "where stuff is"

... first it will get the BLOCK with the LOWEST address (fist block) and go on
reading the records in the ascending order of addresses . Correct ?
...

it depends on the access path chosen entirely.

what if the table is an index organized table - it would be different from a heap table and a clustered table and an external table and so on.

In general, a heap based, non-partitioned table would be read from it's first extent to its last extent in extent order, and within the extents by block order

.... well the records will not come out in the order of insertion as the table is
organised as heap . ...

records never have to come out in order of insertion, regardless of the table type - never. The only way to get records in some predicable order would be to use the ORDER BY clause - without that, consider them being returned randomly.

September 16, 2010 - 11:51 am UTC

Reviewer: A reader

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from temp1 where rownum >5;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2603186868

--------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time   
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    35 |  1365 |     3   (0)| 00:00:0
|   1 |  COUNT              |       |       |       |            |        
|*  2 |   FILTER            |       |       |       |            |        
|   3 |    TABLE ACCESS FULL| TEMP1 |    35 |  1365 |     3   (0)| 00:00:0
--------------------------------------------------------------------------

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

   2 - filter(ROWNUM>5)

Note
-----
   - dynamic sampling used for this statement (level=2)


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


does it mean - would it SCAN the FULL TABLE when we ask the rows - ROWNUM > X ?   Why does the optimizer not understand this and say 'No I can't give you the rows as you used '>' simply with out full scanning the table ? is this not waste of time for the Optimizer ?

Tom Kyte

Followup  

September 16, 2010 - 12:55 pm UTC

it is a suboptimization (since that query would never return any rows, you would tend to not execute it) that just isn't implemented. In 'real life', it doesn't affect you - since the query is wrong to begin with.

Rownum

February 06, 2011 - 11:55 pm UTC

Reviewer: vinothkarthick from India

Hi Tom,

You said rownum will be assigned after fetching each row.

But when i do an update statement ,

update test set col_1='A' where rownum<3;


The table is getting updated. Could you please explain this?
Tom Kyte

Followup  

February 07, 2011 - 6:41 am UTC

using the logic from above:

   rownum = 1
   for x in ( select * from A )
   loop
       if ( x satisifies the predicate ) 
       then
             OUTPUT the row
             rownum = rownum + 1
       end if;
   end loop;


it should be clear, your update is just like:

   rownum = 1
   for x in ( select * from A )
   loop
       if ( x satisifies the predicate ) 
       then
             <b>UPDATE</b> the row
             rownum = rownum + 1
       end if;
   end loop;



You are updating instead of outputting - so it gets the first three random rows it happens to find in the table, updates them - rownum becomes 4 after the third update and the predicate is never satisfied again.

February 07, 2011 - 7:46 am UTC

Reviewer: Oleksandr Alesinskyy

It is less than 3 not less or equal, so only 2 rows will be updated. :)
Tom Kyte

Followup  

February 09, 2011 - 6:57 am UTC

you are correct ;)

RowNum

February 08, 2011 - 3:08 pm UTC

Reviewer: Ananth from Richmond, VA USA

Hi Tom,,

As per the algorithm

rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;

you have mentioned that "for x in (select * from A)" .
on what order, the output of "Select * from A" is processed.

Can we say that order in which output from "SELECT * FROM A" is processed, the same order by which ROWNUM gets assigned. (assuming that "x satisfies the predicate" returns TRUE in all cases).

Regards
Ananth
Tom Kyte

Followup  

February 10, 2011 - 3:57 pm UTC

... you have mentioned that "for x in (select * from A)" .
on what order, the output of "Select * from A" is processed. ...

in whatever order we fill like. In whatever order we find pleasing to us. In no specific order that you can count on.


... Can we say that order in which output from "SELECT * FROM A" is processed, the
same order by which ROWNUM gets assigned. ...

I don't know what you mean by that exactly.

Rownum<X where X is way bigger than actual rows

June 14, 2011 - 5:06 am UTC

Reviewer: Ankur Sharma from Gurgaon, India

I understood that rownum limits the rows when you give any lesser values and it obviously gives a performance benefit as well.

We have an actual scenario in Oracle 10gR2 where putting a ROWNUM<500000 makes a query finish execution whereas when we remove the clause it hangs indefinitely. The notable fact is that the query is only fetching around 100000 rows.

Now my question is Should adding a STOPKEY giving it a higher than resultset value give me a performance benefit for a query which is hanging?
Tom Kyte

Followup  

June 17, 2011 - 11:42 am UTC

and it
obviously gives a performance benefit as well.


and it might in some cases give a performance benefit as well - would be a more correct statement.


It isn't obvious, nor is it true, that it will definitely give a performance benefit.



If what you say is true, then when is happening is that you are correcting an estimated cardinality. Look at the plans with and without the rownum - and you'll see the estimates are different (you are lowering an estimated cardinality of some step to 500,000 - that can and will cause plan changes)

Since the actual cardinality is 100,000 - we must be getting the estimated cardinality way off - by an order of magnitude I would guess. When that happens, you get the bad plan syndrome. You would want to investigate why we are getting some a bad cardinality estimate and then fix that - by gathering more stats, current stats, using dynamic sampling, using a sql profile, gathering extended statistics, and so on.

ORA nested select :

November 17, 2011 - 9:45 am UTC

Reviewer: A reader from FRANCE

Hi
I met the legendary SQLSyntaxErrorException: ORA-00918 error within a nested select. For now, I have no solution.
my select is some think like that:
SELECT * FROM -- first select parent
(SELECT tab.*, ROWNUM rnum -- second select parent
FROM (
-- child select
SELECT t1.DESCR, t1.REST_ID, t1.COL8, t1.COL10,
t0.DESCR, t0.REST_ID, t0.COL1, t0.COL2
FROM
TABLE_1 t1, TABLE_0 t0
WHERE t0.REF_ID = t1.REF_ID
) tab
WHERE ROWNUM <= 50
)
WHERE rnum >= 0 ;

--
The problem seems to be related to absence of aliasing on column t1.DESCR, t1.REST_ID and t0.DESCR, t0.REST_ID.
--> no error ORA-00918 if query is modified with aliasing
t1.DESCR d1, t1.REST_ID r1 and t0.DESCR d0, t0.REST_ID r0.

In my project I intercept differents sql query in order to set Min ROW and MAX row, so I have not to modify the query
Solution ??
thank for help :p
Tom Kyte

Followup  

November 17, 2011 - 7:22 pm UTC

You'll have to unambiguously define those column names - sorry, there is no magical workaround for that in this case.

Pagenation query - Need your inputs

February 23, 2012 - 7:40 am UTC

Reviewer: Rajeshwaran, Jeyabal

Tom:

I was reading your examples at this below link, and ended up with this below question. Can you please guide me on this?

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:948366252775#1482133696556

Question
1) Pagenation, Query is fast when it produces results for first few pages & its slow for the Very last result set page. ( say, this query is producing 500 pages of results with each page having 100 records, when i navigate to 500th page, the query which is producing this result set is very slow below is the demonstration of this). How is query has to be re-desinged to produce result sets faster when navigating to last page.

2)If possible, can you share how you implemented pagenation in asktom website?

I have a table of 18M records with Primary key on 'ID' column.
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from big_table
  2  /

  COUNT(*)
----------
  18137600

Elapsed: 00:00:30.64
rajesh@ORA11GR2>

I need to get the rows from 100 to 200, so i used this query and it was very fast.
variable l_start_id number;
variable l_end_id number;
exec :l_start_id := 100;
exec :l_end_id := 200;

select * from (
 select rownum as r,t.*
 from  (select * 
   from big_table 
   order by id )t
 where rownum <= :l_end_id
   )
where r > :l_start_id
/

select * from (
 select rownum as r,t.*
 from  (select *
   from big_table
   order by id )t
 where rownum <= :l_end_id
   )
where r > :l_start_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     11.82      32.83     273278     273293          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     11.82      32.83     273278     273293          0         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84  

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  VIEW  (cr=273293 pr=273278 pw=0 time=0 us cost=503441 size=36800 card=200)
    200   COUNT STOPKEY (cr=273293 pr=273278 pw=0 time=298 us)
    200    VIEW  (cr=273293 pr=273278 pw=0 time=199 us cost=503441 size=3101529600 card=18137600)
    200     SORT ORDER BY STOPKEY (cr=273293 pr=273278 pw=0 time=99 us cost=503441 size=1886310400 card=18137600)
18137600      TABLE ACCESS FULL BIG_TABLE (cr=273293 pr=273278 pw=0 time=26300544 us cost=74576 size=1886310400 card=18137600)

When i need to get the records from 14999900 to 15000000, the query is slow. since it has to assign rownum to 15M records and that is where it gets delayed.
exec :l_start_id := 14999900;
exec :l_end_id := 15000000;

select * from (
 select rownum as r,t.*
 from  (select * 
   from big_table b1
   order by id )t
 where rownum <= :l_end_id
   )
where r > :l_start_id
/


select * from (
 select rownum as r,t.*
 from  (select *
   from big_table b1
   order by id )t
 where rownum <= :l_end_id
   )
where r > :l_start_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     33.40     522.91     506650     273293       7017         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     33.40     522.91     506650     273293       7017         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84  

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  VIEW  (cr=273293 pr=506650 pw=233372 time=0 us cost=503441 size=2760000000 card=15000000)
15000000   COUNT STOPKEY (cr=273293 pr=506650 pw=233372 time=101150536 us)
15000000    VIEW  (cr=273293 pr=506650 pw=233372 time=92576752 us cost=503441 size=3101529600 card=18137600)
15000000     SORT ORDER BY STOPKEY (cr=273293 pr=506650 pw=233372 time=84118032 us cost=503441 size=1886310400 card=18137600)
18137600      TABLE ACCESS FULL BIG_TABLE (cr=273293 pr=273278 pw=0 time=42014720 us cost=74576 size=1886310400 card=18137600)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                             3849        0.22         29.21
  direct path write temp                       1526        0.13         10.03
  control file sequential read                16836        0.30         43.42
  db file sequential read                      2806        0.21          3.75
  Disk file operations I/O                     4209        7.86        218.13
  db file single write                         1403        0.04          0.86
  control file parallel write                  4209        0.26          7.29
  rdbms ipc reply                              1408        0.42         71.79
  local write wait                             5612        0.17         10.61
  latch: cache buffers lru chain                  1        0.00          0.00
  asynch descriptor resize                        3        0.00          0.00
  direct path read temp                       31800        0.47         45.75
  SQL*Net message from client                     2        0.01          0.01

Tom Kyte

Followup  

February 23, 2012 - 8:04 pm UTC

1) of course it is - just like google (the gold standard today for searching...), the goal was to optimize the retrieval of the FIRST page. The 500'th page - meh - I don't care about it.

If you need to get to the last page - what you REALLY wanted to do was go to the FIRST PAGE of that query - sorted backwards.


I suggest you

a) give links to the first page and a prev/next button ONLY - or, give links to the first 10 pages - and make them go to page 10 to get to the next 10 pages and so on (NO ABILITY to go right to page 257 - they have to page to get there).

Also, be like google - don't let them go past page 100 (if what they wanted wasn't on the first 100 pages - they sort of need to work on refining their search. I don't know of anyone that can use more that 100 pages of results)

Give them a button to go to the "last page" by making the past page the FIRST PAGE (first page = fast, last page = slow, make last first and last becomes.... fast)


2) http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

Pagenation query - Need your inputs

February 24, 2012 - 1:59 am UTC

Reviewer: Rajeshwaran, Jeyabal

Tom:

Thanks for your reponse.
first page = fast, last page = slow, make last first and last becomes.... fast

I am able to understand this, first page = fast, last page = slow. But what i am unable to understand is make last first and last becomes.... fast, Can you plesae elaborate if possible with examples too?
Tom Kyte

Followup  

February 25, 2012 - 5:05 am UTC

if you make your last page become the first page, all of a sudden - getting that last page (which is now the FIRST PAGE) is fast.

sort the results "backwards", change your order by, and your old last page is the first page.

and we can get the first page fast.

February 24, 2012 - 8:17 am UTC

Reviewer: Centinul from New England, USA

I am able to understand this, first page = fast, last page = slow. But what i am unable to understand is make last first and last becomes.... fast, Can you plesae elaborate if possible with examples too?

Essentially what I believe Tom is saying is that you would reverse the sort order of your inner query. So if your first query was like this:

select * from (
    select rownum as r,t.*
    from  (select * 
            from big_table 
            order by id )t
    where rownum <= :l_end_id
            )
where r > :l_start_id


To make the last become first (and hence fast) you would modify your query like so:

select * from (
    select rownum as r,t.*
    from  (select * 
            from big_table 
            order by id DESC)t
    where rownum <= :l_end_id
            )
where r > :l_start_id


See how the ORDER BY clause is sorted in reverse now? I hope this helps!
Tom Kyte

Followup  

February 25, 2012 - 5:13 am UTC

exactly, thanks :)

rownum & query fetch ..

March 09, 2012 - 5:21 am UTC

Reviewer: Dhairyasheel Tawade from India

Hi Tom
Reading this article I have the following understanding of one my my production queries.

query e.g

select /* index asc hint */ from transaction_master
where account_number >:b1 and rownum <2 order by
(columns in same order as composite index hint above);

My understanding is & as seen from explain plan
It does a range scan of specified index.

but
a. Will it fetch all records > account number specified , into the buffer cache & then apply rownum <2 on the result set in buffer cache and get me 1 record

OR

b. It will fetch one record with account number > specified
see that rownum is <2 and output me just 1 record from buffer cache.

At present I'm seeing very high buffer gets for this query
and the index range scan accesses some 2858M rows and 360G of data as per awr stats and explain plan.

Many Thanks as always.


Tom Kyte

Followup  

March 09, 2012 - 9:28 am UTC

read the plan, trace it, look at the tkprof and you'll see what it does.

there is likely a "count stopkey" in there and that means 'stop running the plan when this condition is met'

I'd remove that hint, I'd use "first_rows(1)" instead.

It is almost certainly reading the index, getting a rowids (any one, a random one if you will - if there are 50 records matching that account_number - you are getting 'one of them') and getting that record and stopping.


You'd need to post a tkprof example for us to look at if you want any further comments. And an explanation of the schema/indexing/etc

strange output of rownum.

April 19, 2012 - 5:40 am UTC

Reviewer: Biswaranjan from India/fujitsu.

Hi Tom,

I have read your all rownum concept 3 years back :) and have been feeling strong in that concept.

Just now I found a strange thing about rownum(may be I dont know that concept).

when we are querying like
select * from emp where rownum=1;
this will return one random record and makes the conut to 2 for the next row and so only one record is coming to the display.
as for the query 
select * from emp where rownum=2;
through out the query the rownum's value is 1 so we dont get any record.
All i have explained are ok.

But now from the below three queries the last one is the one which confused me ( i was expecting for one record to come but returned all records).

SQL> select * from emp where rownum in (1);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> select * from emp where rownum = (select 1 from dual );

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> select * from emp where rownum in (select 1 from dual );

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1224                    10

14 rows selected.



By thinking deeply then i thought that it might be 1 for each row(but if it is so then why it did not give the all rows in case rownum = ).

can you please tell the strange behaviour of "rownnum in (select 1 from dual );" .

thanks in advance.
Biswaranjan

Tom Kyte

Followup  

April 19, 2012 - 6:01 am UTC

that would be a bug - they rewrote the query wrong:

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   532 |     5   (0)| 00:00:01 |
|   1 |  COUNT              |      |       |       |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   4 |    FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE
              ROWNUM=1))


they cannot push the rownum psuedo column down into the subquery like that.


do you have access to support? if not, let me know and i'll file a bug - if you do, please file a bug yourself.

continuation to strange output of rownum.

April 19, 2012 - 6:02 am UTC

Reviewer: Biswaranjan from India/fujitsu.

HI Tom,

No ,I dont have access to support.

thanks,
Biswaranjan
Tom Kyte

Followup  

April 19, 2012 - 6:14 am UTC

Bug 13980459 - ROWNUM IN (SUBQ) RETURNS DIFFERENT/WRONG ANSWER THAN ROWNUM IN (STATIC LIST)

why ?

April 19, 2012 - 8:56 am UTC

Reviewer: Sokrates


they cannot push the rownum pseudo column down into the subquery like that.

Why is this not legal ?
Documented anywhere ?
( at least not on http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#SQLRF00255 )

One could argue that the outcome of ROWNUM IN (SUBQ) simply is not deterministic due to potential rewriting.

We already have seen that the outcome of
Rownum in join condition
is not deterministic, see
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4136428500346062813
Tom Kyte

Followup  

April 19, 2012 - 9:07 am UTC

because rownum cannot be correlated - they needed to reference EMP.ROWNUM

if you have this query:

select * from emp where rownum in ( select 1 from dual )

they would have to rewrite it as:

select * from emp where exists ( SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE
EMP.ROWNUM=1 )


but they cannot reference EMP.ROWNUM. the rewrite is wrong on the face of it - when you push the column into the subquery - you have to have a correlation name (but rownum won't let you)



If you look at the rewritten query, it is obviously wrong, they turned it into:



select * from emp where exists ( SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE ROWNUM=1 )

rownum is now referencing the DUAL table - not emp.

I don't see this as being deterministic/not deterministic, it is an invalid rewrite of the query. the rewritten query is not semantically equivalent



I don't see any relation to the join issue, that join issue should be totally expected - this is just wrong.

I don't agree

April 19, 2012 - 10:41 am UTC

Reviewer: Sokrates

rownum is now referencing the DUAL table - not emp.

Hmm - how can rownum reference a table ? It doesn't - never does, that doesn't make any sense in my eyes. And that's the reason why you cannot write EMP.ROWNUM nor DUAL.ROWNUM.

Rather, as stated on
http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#SQLRF00255
rownum references a query.
(and you stated similar for example on your very first answer in this thread)

Hence, it doesn't make any sense for example to use rownum in a join or to use rownum in a
 in (SUBQ) 
expression, because it is absolutely indeterministic - and undocumented - which of the queries ( the main query or the subquery ) rownum references.


Therefore the outcome of
where 1 in (select rownum from dual)

is completely indeterministic from my point of view.

One could even argue
where a in (select b from dual)

is equivalent to
where a = b
(which did Biswaranjan from India/fujitsu. ), which is equivalent to
where b in (select a from dual)


So

select * from emp where rownum in (select 1 from dual );

is the same as
select * from emp where 1 in (select rownum from dual );


What do you think the outcome of the latter query should be ?

To me, both queries make no sense.
Tom Kyte

Followup  

April 20, 2012 - 3:26 am UTC

Hmm - how can rownum reference a table ? It doesn't - never does, that doesn't make any sense in my eyes. And that's the reason why you cannot write EMP.ROWNUM nor DUAL.ROWNUM.

did you see the predicate filter? did you read the rewritten query I posted? How could you not see it:

original query:

select * from emp where rownum in (select 1 from dual)


rewritten query:


select * from emp where exists ( SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE ROWNUM=1 )



?????? those are entirely different. by pushing rownum - without a correlation name (which it cannot have by the way) - into the subquery - it totally changed the meaning.


expression, because it is absolutely indeterministic - and undocumented - which of the queries ( the main query or the subquery ) rownum references.


that I absolutely do not agree with.

where something in (set)

is not ambiguous, it is quite clear and in fact documented. That we have:

where rownum in (set)

doesn't change anything.


and it is quite clear due to hard and fast scoping rules what rownum is referencing. it is quite clear.

Otherwise, you would be saying "you cannot use rownum with any query that has a subquery of any type - any where"



Therefore the outcome of
where 1 in (select rownum from dual)

is completely indeterministic from my point of view.


sorry, but you are way off here. It is quite deterministic. The subquery is evaluated and the set of "1" is generated. I don't see any room for controversy here on that.

is equivalent to
where a = b


it is not, dual could have more than one row, it is an IN, not equals.

is equivalent to
where a = b


it is not, it is only equivalent when you replace things with constants - is it NOT true in general, you cannot swap them around like that.


where <expression> in (select <expression2> from ....)

IS NOT REWRITABLE as

where <expression2> in (select <expression> from ....)

and even if it were - so what, they didn't do that - they moved something (changed the SCOPE of rownum) that cannot be moved - becauses your equivalencies are incorrect above - you cannot swap them.

<select * from emp where rownum in (select 1 from dual );

is the same as
select * from emp where 1 in (select rownum from dual );


is

seledt * from emp where emp.deptno in (select dept.deptno from dept)

the same as

select * from emp where dept.deptno in (select emp.empno from dept)

no, it is not. You cannot swap things like that, SQL doesn't work that way. The scoping is all wrong - dept.deptno is "different" when you move it up and out that way. rownum is different when you push it down like that. you cannot swap those expressions.


and again, even if you could - it wouldn't matter, that IS NOT the rewrite they performed.

TO Sokrates

April 19, 2012 - 12:42 pm UTC

Reviewer: Kevin Kirkpatrick from St. Louis, MO

Sokrates wrote:

> select * from emp where rownum in (select 1 from dual );
> 
> is the same as 
> 
> select * from emp where 1 in (select rownum from dual );

No, not the same.  But I agree that Tom's reference to EMP.ROWNUM confuses the issue.  Perhaps a more precise way to express the concept is this rewrite of your queries:

select /*+ QB_NAME(main_query) */ from emp where rownum in (select /*+ QB_NAME(sub_query) */ 1 from dual );

select /*+ QB_NAME(main_query) */ * from emp where 1 in (select /*+ QB_NAME(sub_query) */ rownum from dual );

In the former query, ROWNUM specifically refers to "the incremental number assigned to all rows in the query block labeled main_query".  In the latter, ROWNUM specifically refers to "the incremental number assigned to all rows in the query block labeled sub_query".  So you're two queries are really asking different questions, kind of akin to these versions: 
[illegal syntax used for illustrative purposes]

Q1) select * from emp where rownum@main_query in (select 1 from dual );

Q2) select * from emp where 1 in (select rownum@sub_query from dual );

In this light, I'd argue that neither version is indeterminant nor nonsensical.

thanks, Tom and Kevin

April 20, 2012 - 4:05 am UTC

Reviewer: Sokrates

, I got it

Oh My God,..Great.

April 20, 2012 - 4:30 am UTC

Reviewer: Biswaranjan from India/fujitsu.


Respected Tom,

Wow..

YOu are great. The way you explained above ( clear and genuine points).

Nice explanation :) .

many knowledge to get from you ,no doubt about it.

Regards,
Biswaranjan.



rownum bug

April 25, 2012 - 2:06 am UTC

Reviewer: Biswaranjan from India/fujitsu.

Hi Tom,

Hope you are doing good.:)
The bug you have raised with below details.
####
Followup April 19, 2012 - 6am Central time zone:
Bug 13980459 - ROWNUM IN (SUBQ) RETURNS DIFFERENT/WRONG ANSWER THAN ROWNUM IN (STATIC LIST)
#######
Is it like oracle support team will rewrite in next release?
I dont have any idea ,so I asked.

Thanks,
Biswaranjan.


Tom Kyte

Followup  

April 25, 2012 - 9:43 am UTC

it is an in progress issue, the outcome is not yet determined.

in 9i it is giving expected result

December 01, 2012 - 4:23 am UTC

Reviewer: Biswaranjan from India

Hi tom,
hope you are fine :)
I just installed 9.0.1.0.1 and for the quere
#############
select * from emp where rownum in ( select 1 from dual) ;
############
returns only one record.

..

Sorry to say, if it is handled in 9i or pre-release ,
why they are confused themselves for its outcome to evaluate(in 10g or 11g).

...
It is definitely a bug as you told before.:)


regards,
Biswaranjan.

FYI regarding rownum bug.

December 24, 2012 - 10:17 am UTC

Reviewer: Biswaranjan

Hi Tom,

I was learning about "STAR_TRANSFORMATION " hint and suddenly I remembered about 
rownum bug and applied to it and smiled :).

#######################
in 9i or 10g r1.0

SQL> set autotrace traceonly explain
SQL> select 1 from emp where rownum in (select 1 from dual);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=42
          )

   1    0   COUNT
   2    1     FILTER
   3    2       MERGE JOIN (CARTESIAN) (Cost=4 Card=14 Bytes=42)
   4    3         VIEW OF 'VW_NSO_1' (VIEW) (Cost=3 Card=1 Bytes=3)
   5    4           FAST DUAL (Cost=2 Card=1)
   6    3         INDEX (FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost
          =1 Card=14)

######################################
in 10g R2 or 11g normally all records but using below hint it differs.
################################

SQL> select /*+ STAR_TRANSFORMATION */ * from emp where rownum in (select 1 from dual);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

Execution Plan
----------------------------------------------------------
Plan hash value: 1368797448

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    14 |   560 |     6  (17)| 00:00:01 |
|   1 |  COUNT                 |          |       |       |            |          |
|*  2 |   FILTER               |          |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN|          |    14 |   560 |     6  (17)| 00:00:01 |
|   4 |     VIEW               | VW_NSO_1 |     1 |     3 |     3  (34)| 00:00:01 |
|   5 |      FAST DUAL         |          |     1 |       |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL  | EMP      |    14 |   518 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("$nso_col_1"=ROWNUM)





Thanks & Regards,
Biswaranjan.

rownum > 0

March 22, 2013 - 1:35 am UTC

Reviewer: Girish from India

Hi Tom,

I have here q query written

select * from emp where rownum > 0;

How this is different from

select * from emp;

Will it lead to any performance gains when using large tables ?

Thanks,
Girish


Tom Kyte

Followup  

March 25, 2013 - 10:57 am UTC

it would not make it run any faster, in fact the only thing it could do it perhaps make it run slower as we'd have to verify rownum was greater than zero for each row.

May 16, 2017 - 8:43 pm UTC

Reviewer: A reader

5 years later...

select * from X where rownum in (select 1 from dual)

still returns all the rows.
Connor McDonald

Followup  

May 18, 2017 - 7:03 pm UTC

Give me the bug# you logged and I'll follow it up

Rownum with order by in subquery

March 14, 2018 - 5:48 am UTC

Reviewer: Fact from Australia

Hi Tom,
I have a sql involving rownum which is just for experiment and I cannot figure out why it keeps on throwing missing right parenthesis error. I ran this sql in HR schema
with a
as(
Select 1 id from dual union all
select 1 from dual union all
select 3 from dual union all
select 4 FROM dual
)
--select id from a where id=1 and rownum=1 order by id
Select country_id,(select id from a where id=1 and rownum=1 order by id
          )
from countries 


If I run the commented Select statement only , it runs fine . But if I ran the second Select with errors out saying Missing Parenthesis. Also when I take the order by clause from the second Select statement, it runs just fine.
Is this a bug or there is something which I don't know. Please reply
Connor McDonald

Followup  

March 14, 2018 - 6:38 am UTC

I think its a poorly phrased error message. A scalar query can only return a single row, so the ORDER BY really has not bearing or sensible usage.

For example, if I wanted the lowest value from 'a', then

select id from a where rownum=1 order by id

is not a valid query (ie, returns the wrong result). It needs to be:

select * from ( select id from a order by id ) where rownum=1

and if that were placed into the more complex query, then it works as expected

SQL> with a
  2  as(
  3  Select 1 id from dual union all
  4  select 1 from dual union all
  5  select 3 from dual union all
  6  select 4 FROM dual
  7  )
  8  Select country_id,
  9    ( select * from
 10     (select id from a where id=1 order by id
 11            ) where rownum=1  ) x
 12  from hr.countries ;

CO          X
-- ----------
AR          1
AU          1
BE          1
BR          1
CA          1
CH          1


Sql

March 15, 2018 - 12:13 pm UTC

Reviewer: Alok Verma from India

i have one table,table contains two columns serial_number and name both column data like
S name
1 y
2 n
3 y
4 y
5 y
6 n
7 n
8 y
9 y
10 y
11 n
12 y
13 y
14 n
Like this i have data.i want to fetch data only where continuously y three times comming.
Like
3 y
4 y
5 y
8 y
9 y
10 y
Like that output will be come.

Chris Saxon

Followup  

March 15, 2018 - 2:01 pm UTC

You what now?

Sql

March 16, 2018 - 9:05 am UTC

Reviewer: Alok Verma from India

Hi tom,
I have a table in table 2 columns are there in that columns data like
Sno name
1 y
2 n
3 y
4 y
5 y
6 n
7 y
8 n
9 y
10 y
11 y
12 n
Here i want to output like where three time continuously y is coming that rows.
Please help me.
Chris Saxon

Followup  

March 16, 2018 - 1:19 pm UTC

That's still not helping... And I'm not sure how it relates to the original question...

If you need help, please provide:

- Create table statements
- Data in the form of insert into statements
- The output you expect from your query
- A written description of the logic you're trying to implement

helpful video

March 16, 2018 - 3:45 pm UTC

Reviewer: s

unsure if this is an exam/test question but I feel this could be answered using Lag() and Lead() perhaps you should look at Connor's helpful youtube video for inspiration. https://youtu.be/wezjYiBvKwc

Connor McDonald

Followup  

March 18, 2018 - 3:02 am UTC

thanks for the shoutout

SQL

March 16, 2018 - 7:47 pm UTC

Reviewer: ALOK from INDIA

CREATE TABLE SY
(SNO NUMBER DEFAULT "ALOK"."S_Y"."NEXTVAL",
NAME VARCHAR2(3 BYTE));
Insert into SY Values(1, 'Y');
Insert into SY Values(2, 'Y');
Insert into SY Values(3, 'N');
Insert into SY Values(4, 'Y');
Insert into SY Values(5, 'N');
Insert into SY Values(6, 'Y');
Insert into SY Values(7, 'Y');
Insert into SY Values(8, 'Y');
Insert into SY Values(9, 'N');
Insert into SY Values(10, 'Y');
Insert into SY Values(11, 'N');
Insert into SY Values(12, 'N');
Insert into SY Values(13, 'N');
Insert into SY Values(14, 'Y');
Insert into SY Values(15, 'N');
Insert into SY Values(16, 'Y');
Insert into SY Values(17, 'Y');
Insert into SY Values(18, 'Y');
THIS IS FINAL DATA
SNO NAM
--- ---
1 Y
2 Y
3 N
4 Y
5 N
6 Y
7 Y
8 Y
9 N
10 Y
11 N
12 N
13 N
14 Y
15 N
16 Y
17 Y
18 Y
HERE I WANT OUTPUT LIKE
SNO NAM
--- ---
6 Y
7 Y
8 Y
16 Y
17 Y
18 Y
IN THIS DATA WHERE THREE TIME CONTINUOUSLY "Y" IS COMING THAT ROWS ONLY DISPLAY.

Not sure what this has to do with rownum though.

March 16, 2018 - 8:41 pm UTC

Reviewer: Chuck Jolley from okc, ok usa

Not sure what this has to do with rownum though.

SQL> CREATE TABLE SY
  2  (SNO NUMBER,
  3  NAME VARCHAR2(1));
SQL> Insert into SY Values(1, 'Y');
SQL> Insert into SY Values(2, 'Y');
SQL> Insert into SY Values(3, 'N');
SQL> Insert into SY Values(4, 'Y');
SQL> Insert into SY Values(5, 'N');
SQL> Insert into SY Values(6, 'Y');
SQL> Insert into SY Values(7, 'Y');
SQL> Insert into SY Values(8, 'Y');
SQL> Insert into SY Values(9, 'N');
SQL> Insert into SY Values(10, 'Y');
SQL> Insert into SY Values(11, 'N');
SQL> Insert into SY Values(12, 'N');
SQL> Insert into SY Values(13, 'N');
SQL> Insert into SY Values(14, 'Y');
SQL> Insert into SY Values(15, 'N');
SQL> Insert into SY Values(16, 'Y');
SQL> Insert into SY Values(17, 'Y');
SQL> Insert into SY Values(18, 'Y');
SQL> commit;
SQL>
SQL> select lag(n) over (order by n) gn,
  2         lag(v) over (order by n) gv,
  3         n,
  4         v,
  5         lead(n) over (order by n) dn,
  6         lead(v) over (order by n) dv
  7    from t
  8   order by 1 ;

        GN G          N V         DN D
---------- - ---------- - ---------- -
         1 y          2 n          3 y
         2 n          3 y          4 y
         3 y          4 y          5 y
         4 y          5 y          6 n
         5 y          6 n          7 y
         6 n          7 y          8 n
         7 y          8 n          9 y
         8 n          9 y         10 y
         9 y         10 y         11 y
        10 y         11 y         12 n
        11 y         12 n
                      1 y          2 n
SQL>
SQL> with l
  2  as (select lag(n) over (order by n) gn,
  3             lag(v) over (order by n) gv,
  4             n,
  5             v,
  6             lead(n) over (order by n) dn,
  7             lead(v) over (order by n) dv
  8        from t
  9    )
 10  select gn sno, gv name
 11    from l
 12   where gv = v
 13     and dv = v
 14  union
 15  select n, v
 16    from l
 17   where gv = v
 18     and dv = v
 19  union
 20  select dn, dv
 21    from l
 22   where gv = v
 23     and dv = v
 24  order by 1;

       SNO N
---------- -
         3 y
         4 y
         5 y
         9 y
        10 y
        11 y
SQL>

Well heck

March 16, 2018 - 8:46 pm UTC

Reviewer: Chuck Jolley from okc, ok usa

Pretend I used your old post for data. Hopefully you get the idea. That's what I get for trying to multitask.
Also, I'm sure there are much more sophisticated ways of doing this.

Is there a better alternative to ROWNUM before 12c?

January 20, 2020 - 11:41 am UTC

Reviewer: Guilherme Taffarel Bergamin from Brazil

I know I'm quite late to the discussion, but I work with a fairly old Oracle version at my company.

FETCH N ROWS
came in 12c, but we are even older than that.

That being said, is there a better way to fetch only the N number of rows in a query than doing
SELECT * FROM (<query>) WHERE ROWNUM <= N
?

This feels like that if my base query returns 10k rows, with this it will continue bringing 10k rows and later on, truncate to the first N rows, which doesn't seem very performatic.

Is there a better way?
Connor McDonald

Followup  

January 21, 2020 - 1:36 am UTC

Couple of things to discuss here

select * from T where ... and rownum <= 10

which is "get me the FIRST ten rows that match the criteria and stop"

versus

select * from ( select * from T where ... order by X ) and rownum <= 10

which is "get me the TOP ten rows that match the criteria" (with the definition of "TOP" determined by X)


For the first case, it will be efficient - we don't go and get ALL of the rows, and then start fetching ten. Fetching and Execution are one and the same thing here - we find a row, we send it back, we find the next row, we send it back....and when we get to 10, we stop. We will NOT visit all of the rows.

For the second case, we MUST visit all of the rows matching the criteria because that is the only way to get the TOP values based on X. However, when we see the rownum, we can use that to our advantage. Here's a video showing that in action - and it becomes very clear why you MUST use rownum and not just use your app to fetch the 10 rows.



More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.