Tell me more ×
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.
up vote 0 down vote favorite

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;

Say i want to use the innermost query with OrderBy again and again and pass different values of higher and lower. Is not it possible somehow to do it, without actually executing the entire query for each value of higher and lower?

Thanks

share|improve this question
What are you trying to achieve? Of course the query need to be re-executed each time it is run - the data may have changed between times. If you are trying to avoid reparsing you could consider a parametrised view - is it being executed many times per second? – Jack Douglas♦ Aug 27 '12 at 8:54
Having seen this related question I have to ask: what processing are you doing in Java that can't be done instead on the database or in PL/SQL? If you must do it in Java, why not run the whole query and just fetch 100 results at a time? – Jack Douglas♦ Aug 27 '12 at 9:01
@Jack Douglas I must do it in java, well, if i run the whole query over and again, the order by clause makes it pretty inefficient. – Kraken Aug 27 '12 at 9:26
@Jack Douglas I was thinking maybe i can keep the results of the innermost query somewhere stored but then , since my dataset is pretty huge(millions), i cant afford to replicate so much of data. – Kraken Aug 27 '12 at 9:27
Why do you think "the order by clause makes it pretty inefficient"? It is the rnum >= LOWER filter that makes it inefficient as Justin pointed out on your other question. Also you haven't answered my question about just fetching the rows in batches rather than querying multiple times. Lastly, have you considered running a single query to partition the data on id and then multiple queries on known id ranges? – Jack Douglas♦ Aug 27 '12 at 9:29
show 4 more comments

closed as not a real question by jcolebrand♦ Aug 27 '12 at 17:41

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

Browse other questions tagged or ask your own question.