oracle getting rows N through M of a result

Question:

I would like to fetch data after joining 3 tables and
sorting based on some field. As this query results into approx
100 records, I would like to cut the result set into 4, each of
25 record. and I would like to give sequence number to each
record. Can I do using SQL Plus ?

Answer:

n Oracle8i, release 8.1 -- yes.

select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS
/

that'll do it. It will *not* work in 8.0 or before.

Here is a discussion on the same


Your first query "where rownum between 90 and 100" never returns ANY data. that
predicate will ALWAYS evaluate to false -- always.

I've already proven in another question (believe it was with you again) that

select * from (
select p.*, rownum rnum
from (select * from hz_parties ) p
where rownum < 100
) where rnum >= 90

is faster then:

select * from (
select p.*, rownum rnum
from (select * from hz_parties ) p
) where rnum between 90 and 100

which is what I believe you INTENDED to type. It has to do with the way we
process the COUNT(STOPKEY) and the fact that we must evaluate

select p.*, rownum rnum
from (select * from hz_parties ) p

AND THEN apply the filter where as the other will find the first 100 AND THEN
stop.

so, say I have an unindexed table:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big_table;

COUNT(*)
----------
1099008

(a copy of all_objects over and over and over) and I run three queries. Yours
to show it fails (no data), what I think you meant to type and what I would
type:

select p.*, rownum rnu
from ( select * from big_table ) p
where rownum between 90 and 100

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 6.17 15.31 14938 14985 81 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 6.17 15.31 14938 14985 81 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 216

Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY
0 FILTER
1099009 TABLE ACCESS FULL BIG_TABLE


your query -- no data found.... Look at the number of rows inspected
however



select *
from (
select p.*, rownum rnum
from ( select * from big_table ) p
)
where rnum between 90 and 100

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 7.93 17.03 14573 14986 81 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.93 17.03 14573 14986 81 11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216

Rows Row Source Operation
------- ---------------------------------------------------
11 VIEW
1099008 COUNT
1099008 TABLE ACCESS FULL BIG_TABLE

what I believe you mean to type in -- agein -- look at the rows processed!

Now, what I've been telling everyone to use:


select * from (
select p.*, rownum rnum
from (select * from big_table ) p
where rownum < 100
) where rnum >= 90

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 1 7 12 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 1 7 12 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 216

Rows Row Source Operation
------- ---------------------------------------------------
10 VIEW
99 COUNT STOPKEY
99 TABLE ACCESS FULL BIG_TABLE


HUGE difference. Beat that...

Claims -- don't want em.
Benchmark, metrics, statistics -- love em -- want em -- need em.