Here is my query

        SELECT a.*, rownum rnum
                SELECT id, data
                FROM t
                ORDER BY id
            ) a
       WHERE rownum <= HIGHER
WHERE rnum >= LOWER;

I have a huge set of data and hence I am getting parts of it one at a time, say from 1 to 100 in first attempt, 101-200 in the second attempt and so on.

If I execute the above query it will do the following piece for each portion of data:

SELECT id, data

I think it is pretty inefficient to do it for each part. Can't I do something like order my data set once and apply a rownum? Will it be of any use at all?

  • 2
    Based on the presence of rownum, I assume this is an Oracle-specific question. Aug 27, 2012 at 5:10

2 Answers 2


Is your intention to get every row eventually? For example, are you fetching all the rows and passing them to some sort of process that needs to process every row? Or is your intention to present pages of results to a human who will likely only look at the first or second page of results.

Assuming that you are presenting results to a human that will only be looking at the first couple pages of data, the query you have is likely to be reasonably efficient. Assuming that there is an index on the id column, in order to fetch rows 101-200, Oracle would simply have to read the first 200 id values from the index then filter out rows 1-100. That's not quite as efficient as getting the first page of results but it's still pretty efficient.

Of course, as you get further and further into the data, the pagination query gets less and less efficient. That's a problem if your intention is to eventually fetch every row. It's generally not a problem, though, if you are presenting results to a human. Humans don't really care how long it takes to fetch page 50 of a result set-- they're going to give up long before then.

If your intention is to send the data to a Java process to process the data (this will be substantially less efficient than processing the data in the database-- Oracle and PL/SQL are designed specifically to process large amounts of data), it would generally make sense to issue a single query without an ORDER BY, have a master thread on the Java application server that fetches N rows of data, spawns a thread to process that data, fetches the next N rows of data, etc.

  • Since the amount of data is huge for me, i am getting portion of data from the database and getting it all parallely. Though ultimately i have to get the entire data, and i am not presenting it to human but passing to a process. How do i change this query accordingly to make it efficient.
    – Kraken
    Aug 27, 2012 at 5:22
  • @Kraken - What sort of process are you passing the data to? Are you passing the data to some other process in the database? Are you shipping it over the network to an application server? What language/ API are you using? Aug 27, 2012 at 5:28
  • Java. I am just working on the data that i get from the database. Extracting the info and populating my objects.
    – Kraken
    Aug 27, 2012 at 5:30
  • @Kraken - Updated my answer Aug 27, 2012 at 5:42
  • 1
    @Kraken If you need the order by then kkep it, but the rest of what Justin says still stands. Justin, we're discussing this and Kraken's related question in here if you are interested in joining in. Aug 27, 2012 at 9:58

Based on your additional comments, I'd say you could keep the query as is, but create your table as an "index-organized table". This is basically a way to tell Oracle: dear, keep this ORDER BY forever for this data, my queries will use this order a lot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.