Resultset Paging & Performance increase using ROWNUM

[seralex]

Hello friends,

/*I've pasted this question in Oracle Administration as well. Hopefully it will be answered somewhere.*/

Is there a gain in performance when executing a query restricted by rownums? For example, I would like to create a procedure that would return 10 records (similar to altavista paging, or so).

I have the following query:

SELECT *
FROM table1
WHERE conditon AND rownum>N AND rownum
What I would like to know - when this query is executed, does Oracle first select all rows, and then returns the needed records, or it uses some optimization to select the needed rows without selecting all rows that satisfy the condition.

If there isn't much gain using such a query, It is probably better to write some 3GL caching system.

Has anyone coded such a system before?
I will appreciate it very much if you share your ideas.

Thank you.

[ShrutiM]

Even I fqced the same problem, but couldn't do any thing cause the sesults shown due to the ROWNUMS couldn't be sorted and thus didn't proved fruitful.

If anyone can help.......Thanks!!

[seralex]

Well, actually they can be sorted, just use the nested selects:

SELECT *
FROM (SELECT * FROM table1 WHERE Condition ORDER BY some fields)
WHERE rownum>N AND rownum<N+10.

But the question still remains - is there a permormance gain, or some cacheing system has to be written.

[jmodic]

Ignoring the fact that part of your query with "lest then" character was cut off, your query will newer return a single row!

You can't use "grather then" with rownum that way as the condition will never be met. For your query to work you should recode it like:

SELECT * FROM
(SELECT * FROM table1 WHERE Condition ORDER BY some fields) WHERE rownum>N+k
MINUS
SELECT * FROM
(SELECT * FROM table1 WHERE Condition ORDER BY some fields) WHERE rownum>N;

Hovewer the query will not be very efficient one on large table, especially when your sliding window approches to the end of the total result set.

HTH,

[jmodic]

Hey, sorry for the typos in my previous post. Replace the "greather than" with "less then" characters in a query and you will get the k-row sliding window result with the offset by N.

Well, even if I would have write the query correctly, this damn HTML forum software would have messed it totaly because it doesn't allow the usage of "less then" characters ... :-(

[chrisrlong]

Agree with the LESS THAN symbol point! :)

However, check out this same thread on the Administration forum for a better solution using ROWNUM.

- Chris

[jmodic]

Hi, Chris et al

I agree with one of your post when you sugested not to post the same question to two different forums. It realy is confusing.

About your solution - I agree it is more efficient in some rare situations. However in a real life requirenments, when sliding window mechanism has to be applied to a *sorted* total resultset your solution is wrong because ROWNUM and ORDER BY in the same selct doesn't work as one could expect (and I know you are perfectly aware of that, it must just have slipped your mind). I'll explain what I mean in more detaile in Administartion forum under your solution.

[seralex]

Hello all,

I'm sorry I confused everyone by posting it to two different forums - please follow this thread at ORACLE ADMINISTRATION thread.

Thank you!

[Carmem]

[QUOTE][i]Originally posted by seralex [/i]
[B]Hello all,

I'm sorry I confused everyone by posting it to two different forums - please follow this thread at ORACLE ADMINISTRATION thread.

Thank you! [/B][/QUOTE]

Helo Seralec.
Your obtained to make
an Query??

If your obtaines, it could say me as to make?

Thanks

Im from Brazil, I dont´t Speak Englihs