Querying a database efficiently for a huge chunk of data

Question

Here is my query

SELECT *
FROM
 (
 SELECT a.*, rownum rnum
 FROM
 (
 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
FROM t
ORDER BY id

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?

• Justin Cave: Based on the presence of rownum, I assume this is an Oracle-specific question.

Answer 1

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.

• Kraken: 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.
• Justin Cave: @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?
• Kraken: Java. I am just working on the data that i get from the database. Extracting the info and populating my objects.
• Justin Cave: @Kraken - Updated my answer
• Jack Douglas: @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.

Answer 2

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.