Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 10, 2007

Getting Rows N through M of a Result Set From Database

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 6:24 am

You would like to fetch data and sort it based on some field. As this query results into approx 100 records, you would like to cut the result set into 4, each of 25 records and you would like to give sequence number to each record.

SELECT *
FROM (SELECT A.*, ROWNUM rnum
FROM (your query including the order by) A
WHERE ROWNUM <= MAX_ROWS )
WHERE rnum >= MIN_ROWS;

A simple test done on SQL*Plus is as follows;

Code listing 50 : Getting Rows N through M of a Result Set From Database Example

Here is also a brief explaination for SQL*Plus autotrace statistics used above;

db block gets : Number of logical I/Os for current gets(from undo segments)
consistent gets : Reads of buffer cache blocks
physical reads : Number of blocks read from disk
redo size : Amount of redo generated (for DML statements)
sorts (memory) : Number of sorts performed in memory
sorts (disk) : Number of sorts performed using temporary disk storage

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
“getting rows N through M of a result set” thread on Asktom

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: