31

I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET.

Query like this:

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

I know the sorting ends as soon as it has found the first row_count rows of the sorted result. Can I do sorting all data before calling LIMIT and OFFSET?

MT0
  • 86,097
  • 7
  • 42
  • 90
DraggonZ
  • 1,027
  • 1
  • 15
  • 22

1 Answers1

55

Prior to 12.1, Oracle does not support the LIMIT or OFFSET keywords. If you want to retrieve rows N through M of a result set, you'd need something like:

SELECT a.*
  FROM (SELECT b.*,
               rownum b_rownum
          FROM (SELECT c.*
                  FROM some_table c
                 ORDER BY some_column) b
         WHERE rownum <= <<upper limit>>) a
 WHERE b_rownum >= <<lower limit>>

or using analytic functions:

SELECT a.*
  FROM (SELECT b.*,
               rank() over (order by some_column) rnk
          FROM some_table)
 WHERE rnk BETWEEN <<lower limit>> AND <<upper limit>>
 ORDER BY some_column

Either of these approaches will sort give you rows N through M of the sorted result.

In 12.1 and later, you can use the OFFSET and/or FETCH [FIRST | NEXT] operators:

SELECT *
  FROM some_table
 ORDER BY some_column
 OFFSET <<lower limit>> ROWS
  FETCH NEXT <<page size>> ROWS ONLY
Eugene M
  • 1,258
  • 1
  • 15
  • 22
Justin Cave
  • 212,688
  • 21
  • 342
  • 361
  • 2
    Perhaps not as easily answered as it is asked: Which of these approaches is likely better for very large tables (1-10 million rows)? Maybe it's about a 3-deep select vs 2-deep with rank()? Actually, I guess I'll just try both and see. – Jason Dunkelberger Apr 17 '12 at 16:13
  • 7
    For anyone who's wondering, I found that the first approach was faster for me at several ms, with the second approach at several seconds. I'm not sure if it's the function itself that adds time, or that it prevents any query optimizations. Thanks for showing multiple approaches anyhow! – Jason Dunkelberger Apr 17 '12 at 16:29