Solved

Effect of rownum operator in query.

Posted on 2010-11-19
10
1,197 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
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
 
LVL 31

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now