DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Resultset Paging & Performance increase using ROWNUM

  1. #11
    Join Date
    Dec 2000
    Posts
    22
    Hello.

    But if we modify Chris's original posting :

    [B][QUOTE][i]Originally posted by chrisrlong [/i]
    [B]
    SELECT
    --C1,
    --C2
    FROM
    --(
    --SELECT
    ----C1,
    ----C2,
    ----ROWNUM
    ------AS QUERY_ROWNUM
    --FROM
    ----TABLE
    --WHERE
    ----ROWNUM < Y
    --/* You can add an ORDER BY here if you want */
    --) T1
    WHERE
    --T1.QUERY_ROWNUM > X
    [/B][/QUOTE]

    and include Jurii's sugestions:

    SELECT C1,C2

    FROM
    (
    SELECT
    --inner.C1,
    --inner.C2,
    --inner.QUERY_ROWNUM
    FROM (SELECT C1, C2, ROWNUM AS QUERY_ROWNUM
    FROM table1
    ORDER BY condition
    ) inner
    WHERE inner.QUERY_ROWNUM < N+k
    ) outer

    WHERE outer > N


    Will this be a solution?

  2. #12
    Join Date
    Dec 2000
    Posts
    22
    Sorry for a little bug:
    Last row:

    WHERE outer.ROWNUM > N


  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Seralex, sorry to dissapoint you but even this sollution will not work. To explain why this will have to be somewhat longer message, I'll try to demonstrate it in more detail with some output (slightlly modified for better readability in this web forum) from a real SQL*Plus session with some explain plans. Here it goes.

    ** I have a table TABLE1 with 10 rows:
    SQL> SELECT c1 FROM table1;

    C1
    ----------
    3
    1
    5
    8
    6
    7
    2
    9
    4
    10
    10 rows selected.

    An ordered resultset:
    --SQL> SELECT c1 FROM table1 ORDER BY c1;

    C1
    ----------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    10 rows selected.

    ** I want to get the rows rows in positions 4 to 7 in my sliding window. My sollution:

    SQL> SELECT c1 FROM
    2 (SELECT * FROM table1 ORDER BY c1)
    3 WHERE ROWNUM < 8
    4 MINUS
    5 SELECT c1 FROM
    6 (SELECT * FROM table1 ORDER BY c1)
    7 WHERE ROWNUM < 4;

    C1
    ----------
    4
    5
    6
    7

    ** The result is what we expected. However performace-vise this is not an optimal sollution, it has to perform on table scan up to teh upper limmit and one up to the lower limit of the sliding window. Now let us see how Chris's solution works:

    SQL> SELECT c1 FROM
    2 (SELECT c1, ROWNUM AS query_rownum
    3 FROM TABLE1
    4 WHERE ROWNUM < 8
    5 order by c1
    6 ) t1
    7 WHERE t1.query_rownum >= 4;

    C1
    ----------
    2
    6
    7
    8

    ** Obviously this is not what we expected. Let's see why by looking at the explain plan:

    Execution Plan
    -------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 VIEW
    2 1 SORT (ORDER BY)
    3 2 COUNT (STOPKEY)
    4 3 TABLE ACCESS (FULL) OF 'TABLE1'

    ** See, The sorting is done *after* the ROWNUMs are assigned (COUNT (STOPKEY))!

    ** Let's see what your last proposed sollution does:
    SQL> SELECT outer.c1 FROM
    2 (SELECT c1, query_rownum query_rownum FROM
    3 (SELECT c1, ROWNUM AS query_rownum FROM table1
    4 ORDER BY c1
    5 ) inner
    6 WHERE inner.QUERY_ROWNUM < 8
    7 ) outer
    8 WHERE outer.query_rownum >= 4;

    C1
    ----------
    2
    6
    7
    8

    ** Again wrong result. Let's see the reason by looking at explain plan again:

    Execution Plan
    -------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 VIEW
    2 1 SORT (ORDER BY)
    3 2 COUNT
    4 3 TABLE ACCESS (FULL) OF 'TABLE1'

    We can see optimizer has merged bot inner and outer view in a single view and again, sorting is done *after* the original rownums are assigned.

    Shure, wee could cheet and persuade the optimizer with some hints or by implementing sorting with GROUP BY to get the expected result , however I don't think it is worth the effort.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #14
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Wow. Nice comeback. I fully admit that my first example was incorrect. It is a shortcut I use when I don't use the ORDER BY (or know I'm using an index that returns the rows in order before the ORDER BY) and then erroneously stuck that comment in there about where one could add an ORDER BY.

    My bad. I apologize.

    Having said that, I still hold firm that my method is faster.

    To prove this, I created the same table with the same rows in the same order. I then correctly amended my statement as follows:

    SELECT
    ---OUTER.C1
    FROM
    ---(
    ------SELECT
    ---------INNER.C1,
    ---------ROWNUM
    ------------AS QUERY_ROWNUM
    ------FROM
    ---------(
    ---------SELECT
    ------------C1
    ---------FROM
    ------------TABLE1
    ---------ORDER BY
    ------------C1
    ---------) INNER
    ------WHERE
    ---------ROWNUM (LESS THAN) 8
    ---) OUTER
    WHERE
    ---OUTER.QUERY_ROWNUM >= 4

    Where I moved *all* ROWNUM manipulation outside of the SELECT with the ORDER BY. This yields the proper results and the following plan:

    SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=3 Card=164 Bytes=4264)
    VIEW (Cost=3 Card=164 Bytes=4264)
    COUNT (STOPKEY)
    VIEW (Cost=3 Card=164 Bytes=2132)
    SORT (ORDER BY STOPKEY) (Cost=3 Card=164 Bytes=2132)
    TABLE ACCESS (FULL) OF TABLE1 (Cost=1 Card=164 Bytes=2132)

    (Minor Note: my optimizer default is CHOOSE and since I wanted a cost, I added the ALL_ROWS hint *for that reason only*. This hint did not change the plan at all)

    For comparison, I provide the plan for the MINUS implementation:

    SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=8 Card=328 Bytes=4264)
    MINUS
    SORT (UNIQUE NOSORT) (Cost=4 Card=164 Bytes=2132)
    COUNT (STOPKEY)
    VIEW (Cost=3 Card=164 Bytes=2132)
    SORT (ORDER BY STOPKEY) (Cost=3 Card=164 Bytes=2132)
    TABLE ACCESS (FULL) OF TABLE1 (Cost=1 Card=164 Bytes=2132)
    SORT (UNIQUE NOSORT) (Cost=4 Card=164 Bytes=2132)
    COUNT (STOPKEY)
    VIEW (Cost=3 Card=164 Bytes=2132)
    SORT (ORDER BY STOPKEY) (Cost=3 Card=164 Bytes=2132)
    TABLE ACCESS (FULL) OF TABLE1 (Cost=1 Card=164 Bytes=2132)

    While this may not seem like much of a difference on this 10-row table, it will become a massive difference with 10,000 row result sets. Basically, it has to run the entire statement twice and then do a MINUS on top of it all.

    So while I applaud the ingenuity of the MINUS implementation, I would not recommend it as the optimal solution.

    Good debate, however, and good job catching my mistake. (/me tips hat) :)


    - Chris

  5. #15
    Join Date
    Jan 2001
    Posts
    318

    Wink

    Out of way question: If any one also working with SQl server.
    Is their any way to do where rownum<10 say ??
    I mean is their any functionality for rownum in SQlserver 7.0 or in sql server2000 ??
    I need help on this... please.

    Thanks in advance
    Sonali
    Sonali

  6. #16
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yes. While I haven't worked with SQLServer for a few years, there definitely was the same concept, although I don't think it was called Rownum. Unfortunately, that's all I remember. :)

    - Chris

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Chris (et all),

    Your point entirelly taken! I admit, loud and clear: I was jumping to conclusions too soon, this last one is a totaly viable solution with best performance possible.

    In fact, when analysing your solution (with the seralex's proposal of adding another view inside a view), I made a mistake. I referenced "WHERE inner.QUERY_ROWNUM < 8" instead of simply "WHERE ROWNUM < 8", which makes a huge difference and yields the correct result.

    I can say I was enjoying this discussion too!

    PS. Am I wrong or does this forum software now allow "less than" and "more than" characters? It still trims leading spaces off, though... :-((
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Dec 2000
    Posts
    22
    Hi

    I applaud to you friends, nice discussion, Chris and Jurii were a hit.

    As to Sonali's question, in SQL Server you have to write the following query to get first 10 results:

    SELECT TOP 10 c1 FROM table1 ORDER BY c1

    I think SQL Server firsts sorts the results, and then applies TOP 10.

    Thank you friends for a nice debate!
    Seralex

  9. #19
    Join Date
    Jan 2001
    Posts
    318
    I found out something today...
    I don't know where one can use.

    I found that the ROWNUM is assigned by the where condition, and then we can use the same in
    GROUP-HAVING condition

    select max(work_id), rownum
    from mwebwork
    where WORK_LEVEL in(1,2,3,4,5)
    group by rownum having rownum=2 or rownum=4 or rownum=8 or rownum=16 or rownum=32;

    This way one can say which rownum to select.
    Sonali

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