Solved

Effect of rownum operator in query.

Posted on 2010-11-19
10
1,211 Views
Last Modified: 2013-12-18
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
Comment
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
  • Learn & ask questions
10 Comments
 
LVL 37

Expert Comment

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

Expert Comment

by:TommySzalapski
ID: 34170980
Typo, should read
They will be different. The rownum column is populated BEFORE the order by.
0
 
LVL 37

Expert Comment

by:TommySzalapski
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 32

Expert Comment

by:awking00
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

by:Akenathon
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

by:Mitali05
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

by:POracle
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

Open in new window

0
 
LVL 19

Expert Comment

by:Thommy
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

by:Mitali05
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

by:
Devinder Singh Virdi earned 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join actual table rows based on the column 25 44
Help with Oracle IF statment 5 50
Oracle Date 6 42
Oracle perfomance tuneing. 3 47
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question