Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Effect of rownum operator in query.

Posted on 2010-11-19
Medium Priority
1,215 Views
Hi,

Look at the below two queries.

select * from (
select inv_seq,inv_nm_txt from invoice
where inv_nm_txt like 'A%'
order by inv_seq)
where rownum < = 100

select inv_seq,inv_nm_txt from invoice
where inv_nm_txt like 'A%'
and rownum <=100
order by inv_seq

Will both the queries produce the same results?
Would there be any difference as of rownum in both queries is concerned?
0
Question by:sakthikumar
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 37

Expert Comment

ID: 34170979
They will be different. The rownum column is populated after the order by.
0

LVL 37

Expert Comment

ID: 34170980
They will be different. The rownum column is populated BEFORE the order by.
0

LVL 37

Expert Comment

ID: 34170986
So in the second query the numbers in rownum will not be in order. They will represent the original order before the sort.
Of course, in the first query the numbers in rownum will be sorted along with the inv_seq.
0

LVL 32

Expert Comment

ID: 34172985
Rownum is a pseudo column of the result set and not of the table. As such, the rownum does not have to be the same for a query that uses a subselect and one that doesn't. However, in your example, because the subquery is doing an order by, I believe they will be the same, although that may also not be reliable if, for example, the table is partitioned.
0

LVL 11

Expert Comment

ID: 34174305
Even the exact same query with ROWNUM can give you different results. For instance, say you do select * from big_table where rownum<=100 order by <anything>. Then insert one row and re-run the select. If the inserted row happened to go to the first data blocks of the table segment, it will appear in the results. If it happened to get located after the "first 100 rows", the results won't change.

ROWNUM just reads the first N records from a result set. No repeatability is guaranteed.
0

Expert Comment

ID: 34211480
Both the queris will fecth different results.
Reason being
In the 1st query it will return the 1st 100 rows whose inv_nm_txt like 'A%'
In the 2nd Query it scan only the 1st 100 rows of table invoice to find the rows that match the condition inv_nm_txt like 'A%' and return the result in ascending order.
0

LVL 8

Expert Comment

ID: 34212332
Examine output of query in this example, it will solve your doubts

``````SQL> create table temp(a number,b char(10));
Table created.

SQL> insert into temp values(1,'a');
1 row created.

SQL> insert into temp values(3,'c');
1 row created.

SQL> insert into temp values(4,'d');
1 row created.

SQL> commit;
Commit complete.

SQL> select rownum,temp.* from temp;

ROWNUM          A B
---------- ---------- ----------
1          1 a
2          3 c
3          4 d

SQL> select rownum,temp.* from temp order by b desc;

ROWNUM          A B
---------- ---------- ----------
3          4 d
2          3 c
1          1 a

SQL> select rownum,d.* from (select temp.* from temp order by b desc) d;

ROWNUM          A B
---------- ---------- ----------
1          4 d
2          3 c
3          1 a
``````
0

LVL 19

Expert Comment

ID: 34243915
Both queries return 100 rows.

But indeed the results are different:

The first query sorts all rows which match the WHERE-clause and then returns the first 100 of them

The second query takes the first 100 rows which match the WHERE-clause and then returns them according to the sort order
0

Expert Comment

ID: 34245738
As I said above
Both the queris will fecth different results.
Reason being
In the 1st query it will return the 1st 100 rows whose inv_nm_txt like 'A%'
In the 2nd Query it scan only the 1st 100 rows of table invoice to find the rows that match the condition inv_nm_txt like 'A%' and return the result in ascending order
And if there are 100 matching records then definately both queries will return 100 rows.
0

LVL 15

Accepted Solution

Devinder Singh Virdi earned 2000 total points
ID: 34339255
Here is the reason of diff rows.

Oracle doesn't guarantee the order of data when NOT using order by clause. Why? because, incoming data can be added in first block. I mean whereever it finds the space. So last row added can reside in first block, therefore in select statement you may see first row, last row then second row, middle row etc.
When you specify, oracle do sorting.
Your second query is fetching 100 records from sorting list, but first query is using first few blocks to return query result.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logicalâ€¦
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I Â will demonstrate that undo for DMLâ€™s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasoâ€¦
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
###### Suggested Courses
Course of the Month7 days, 14 hours left to enroll