Rownum

How to use rownum

You have to write it this way:

select *
from ( select * from emp order by sal desc )
where rownum <= 5;

Why?

1. The from and where clause go first
2. rownum is assigned and incremented to each output row from the from/where
3. select is applied.
4. group by is applied.
5. having is applied.
6. it is sorted.

So you need an subselect to apply "order by" first, then rownum.

Tuning with rownum

The optimizer has to process the subselect ("inline view") above first, in order to evaluate rownum. This means, the subselect is materialized. So, using rownum, you can force the optimizer to process your queries in a certain way.

Here's an example:

select a12.prc_chk_typ_desc
, a11.prod_key
, max(a)
, max(b)
, max(c)
, max(d)
from (
  select a11.*
  , f ( a11.prod_key, a11.prc_chk_key, 'QTY', 'D', 1) a
  , f ( a11.prod_key, a11.prc_chk_key, 'AMT', 'D', 1) a
  , f ( a11.prod_key, a11.prc_chk_key, 'CODE', 'D', 1) a
  , f ( a11.prod_key, a11.prc_chk_key, 'PRC', 'D', 1) a
  , rownum r
  from t1 a11 ) a11
, t2 a12
where a11.cmpt_loc_key = a12.cmpt_loc_key
and a11.loc_key = a12.loc_key
and a11.prc_chk_key = a12.prc_chk_key
group by a12.prc_chk_typ_desc
, a11.prod_key;

If both tables have 50 rows and rownum is not used, then the function f will be called 10.000 times. With rownum, it will only be called 200 times (4 times for each row of t1). Because of rownum, the result will be stored and there is no need to call f again.

Another example:

You have two queries. Each runs very fast. Then you join them, and now it is really slow!

select *
from ( select ..., rownum r1 from .... ) query1
, ( select ..., rownum r2 from .... ) query2
where join_condition ...

There is something wrong, maybe the statistics, so the optimizer chooses the false plan. As a workaround, you can use rownum. 

Rownum > 2 never selects anything ...

... because rownum will only be incremented if you get a row. So you have to use an alias:

select *
from ( 
  select a.*, rownum rnum
  from ( select * from emp order by sal desc ) a
  where rownum <= 100 
)
where rnum >= 90;


Maybe you think of using between here:

select *
from ( 
  select a.*, rownum rnum
  from ( select * from emp order by sal desc ) a 
)
where rnum between 90 and 100;

That's not a good idea. The inline view will be materialized, so you will get a bad performance.

These informations are from chapter 8 of the book: "Effective Oracle by Design" by Thomas Kyte, McGraw Hill 2003. Still the best book about building performant databases: Very good readable. And since 2003 no other book has been wirtten covering this thema completely.

No comments:

Post a Comment