SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query to get rows betwen x and y + table total row count

jariolaJan 21 2011 — edited Jan 21 2011

Hi,

I try get rows between e.g. 100 and 150 when I sort by some column
My query is now like this

SELECT  *
FROM
  (SELECT a.*,
    row_number() OVER (ORDER BY OWNER ASC) rn,
    COUNT(1) over() mrn
  FROM (SELECT * FROM all_objects) a
  )
WHERE ROWNUM BETWEEN least(100,mrn) AND 150

It is not very fast if I run that kind select from big table
Any tips to optimize this query?

Regards,
Jari

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 18 2011

Added on Jan 21 2011

6 comments

299 views