Resultset Paging & Performance increase using ROWNUM
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Resultset Paging & Performance increase using ROWNUM

  1. #1
    Join Date
    Dec 2000
    Posts
    22
    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.

  2. #2
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Unhappy

    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!!


  3. #3
    Join Date
    Dec 2000
    Posts
    22
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 ... :-(
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Agree with the LESS THAN symbol point! :)

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

    - Chris

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2000
    Posts
    22
    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!

  9. #9
    Join Date
    Jan 2001
    Posts
    2
    [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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width