It's a very good explanation.

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;

Hi

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

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?

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.

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

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.

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





 

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

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.

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.

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.

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.

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)

So

Which one is more correct

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

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?

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. 

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

hi

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

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?

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...

 

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 .

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

((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.

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

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?

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?

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

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?

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)
/


 

Hi

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

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...!!!  

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

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)

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

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)

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. 

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 !

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

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

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?

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  

Hi Tom

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


Thank you very much.

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.

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

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.)

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! 

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.

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.

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.

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

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

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

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>

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 ?

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?

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

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, 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

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

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;

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.

*why* is

select rownum from dual connect by rownum <= 57

bad ?

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..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388#76022107086938

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.

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

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

thanks tom!

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)

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)?

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)

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?

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.

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 ?

   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;

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 .

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 ?

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?

   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 = 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;

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

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

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?

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:

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:

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?

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!

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.

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

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

HI Tom,

No ,I dont have access to support.

thanks,
Biswaranjan

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

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.

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.

, I got it

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.

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.

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.

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.

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

5 years later...

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

still returns all the rows.

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

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

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.

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.

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

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.

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>

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.

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?

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

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