how do i limit the number of rows returned by an oracle query after ordering

Source http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering

I did some performance testing between:

A.) Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statemenet with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

B.) Analytic approach

select * from (
  <select statemenet with order by clause>
) where myrow between MIN_ROW and MAX_ROW

C.) Short alternative

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Results:

Table had 10 million records, sort was on an unindexed datetime row:

• Explain plan showed same value for all three selects (323168)
• But the winner is AskTom (with analytic following close behind)

Selecting first 10 rows took:

• AskTom: 28-30 seconds
• Analytical: 33-37 seconds
• Short alternative: 110-140 seconds

Selecting rows betwwen 100.000 and 100.010:

• AskTom: 60 seconds
• Analytical: 100 seconds

Selecting rows between 9.000.000 and 9.000.010:

• AskTom: 130 seconds
• Analytical: 150 seconds

Getting the space left on the DEFAULT_TABLESPACE of the logged user

SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  user_free_space fs,
  user_tablespaces ts,
  user_users us
WHERE
  fs.tablespace_name(+)   = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
  ts.tablespace_name;

get the sequence have created

select * from all_sequences;
select * from user_sequences;
select * from dba_sequences ;

view trigger on table

select trigger_body
from user_triggers
where triggering_event = 'INSERT'
and table_name = 'YOUR_TABLE';