Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ROWNUM pseudoclumn, Query regarding the working of ROWNUM pseudoclumn
arnab
post Apr 1 2011, 05:53 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 1-April 11
Member No.: 44,956



Hi All,

I have tried to run two queries viz:

Query1: Select * from table_1 where rownum in (1,2,3,4,5)

Query2: Select * from table_1 where rownum in (Select rownum from table_2)
Note:Query 2 might contain any subquery as the filter condition, I have selected the above as an example only.

Now, the case is such that the filter in query2 would return ,

ROWNUM
-------------
1
2
3
4
5

But query2 returns zero rows as compared to query1 which returns 5 rows.
WHY?
Go to the top of the page
 
+Quote Post
burleson
post Apr 1 2011, 09:02 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,812
Joined: 26-January 04
Member No.: 13



Hi Arnab,

>> Query 2 might contain any subquery as the filter condition, I have selected the above as an example only

Yes, but it will essentially return one fow for every row in table_2 . . .

This does not make sense!

I think you are confusing ROWNUM with ROWID:

http://www.dba-oracle.com/t_rownum_rowid_difference.htm

*************************************************

Using rownum is very dangerous, especially in the hands of beginners, and there are always alternatives to using rownum:

- The WITH clause - Another approach to tuning rownum queries is to separate-out the subquery using the powerful WITH clause. Another benefit of separating-out the sorted subquery is that you can easily apply either a parallel hint or an index hint, if it's faster to retrieve the rows in pre-sorted order.

- The rank or row_number analytics - You can replace rownum in top-n queries with analytics functions, using rank() or row_number() instead, getting the same top-in result, but with much faster response time.

- Optimizer goal hint - In cases where rownum is used to change the optimizer mode to first_rows_n, it may be possible to negate this effect by using an all_rows hint.

- Index hint - In cases where rownum is used to force an index in a subquery, again, deploy an index hint to duplicate the faster execution plan.


The ROWNUM filters RANDOM rows unless it is inside a subquery:

>> Select * from table_1 where rownum in (1,2,3,4,5)

This should be select * from table_1 where rownum < 6;

But it will return six random rows.


Read this carefully, it explains all:

http://www.dba-oracle.com/t_sql_tuning_rownum_equals_one.htm





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
SteveC
post Apr 1 2011, 09:58 AM
Post #3


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



http://www.databasejournal.com/features/or...le-Database.htm
Go to the top of the page
 
+Quote Post
arnab
post Apr 4 2011, 04:59 AM
Post #4


Newbie
*

Group: Members
Posts: 2
Joined: 1-April 11
Member No.: 44,956



Thanks burleson and SteveC for your informative replies....

but still it is not clear to me that why Query1 returns the 5 rows but Query2 returns none.
I know that rownum queries create optimization issues but I am just asking the above question to make me understand the topic .
Please explain it in simple terms as I have only 1 yr experience in this field....
Go to the top of the page
 
+Quote Post
aussie_dba
post Apr 4 2011, 07:42 AM
Post #5


Advanced Member
***

Group: Members
Posts: 622
Joined: 28-August 04
Member No.: 495



"but still it is not clear to me that why Query1 returns the 5 rows but Query2 returns none."

It's clear because ROWNUM is NOT a real data column, it is a temporary!

If this does not make sense, then you need to reread the articles as they explain it as clearly as possible.

Also try the Oracle docs and please, get the training that explains these basic concepts.

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy

No forum can be a substitute for he required Oracle classwork.

READ THIS AGAIN:

http://www.dba-oracle.com/t_rownum_rowid_difference.htm

And recall that ROWNUM is temporary!



Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd December 2014 - 04:17 PM