How to sorting and paging in oracle

1. Using the feature count(stopkey). Oracle will get rows in max rownum and then omit rows under min rownum.

select owner,object_name,object_id
from ( select rownum rno,a.*
from (select /*+first_rows*/ * from t where object_name < ‘Z’ order by object_id ) a
where rownum <= 30
)
where rno >= 20

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=53053 Card=30 Bytes=1800)
1 0 VIEW (Cost=53053 Card=30 Bytes=1800)
2 1 COUNT (STOPKEY) –this is an special internal count stopkey method
3 2 VIEW (Cost=53053 Card=52483 Bytes=2466701)
4 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=53053 Card=52483 Bytes=4513538)
5 4 INDEX (FULL SCAN) OF ‘T_OID_IND’ (NON-UNIQUE) (Cost=101 Card=52952)

2. Using rowid, use Fast Full scan index when possible

select owner,object_name,object_id
from t a,
( select rid from
( select rownum rno,rowid rid from
( select rowid from t
where object_name < ‘Z’
order by object_id  )
) where rNo between 20 and 30
) b
where a.rowid=b.rid
order by a.object_id

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How to sorting and paging in oracle

  1. neworacledba says:

    you post great on unique topics…keep up the good job…this post is very useful and interesting…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: