Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1222
  • Last Modified:

Effect of rownum operator in query.

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
sakthikumar
Asked:
sakthikumar
1 Solution
 
TommySzalapskiCommented:
They will be different. The rownum column is populated after the order by.
0
 
TommySzalapskiCommented:
Typo, should read
They will be different. The rownum column is populated BEFORE the order by.
0
 
TommySzalapskiCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
awking00Commented:
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
 
AkenathonCommented:
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
 
Mitali05Commented:
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
 
POracleCommented:
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
 
ThommyCommented:
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
 
Mitali05Commented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now