ROWNUM pseudoclumn, Query regarding the working of ROWNUM pseudoclumn Options

[arnab]

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?

[burleson]

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

[SteveC]

http://www.databasejournal.com/features/or...le-Database.htm

[arnab]

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....

[aussie_dba]

"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!