Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Based on the presence of rownum, I assume this is an Oracle-specific question. –  Justin Cave Aug 27 '12 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.

share|improve this answer
    
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 '12 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? –  Justin Cave Aug 27 '12 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 '12 at 5:30
    
@Kraken - Updated my answer –  Justin Cave Aug 27 '12 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. –  Jack Douglas Aug 27 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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