DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: optimized rownum? plz help

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    10

    Question

    I have an EMPLOYEE table with 10,000 records and I only want to display 25 records at a time to my web client. I have been using ROWNUM to minimize the resultset which works fine. Unfortunately the query still needs to parse through all 10,000 records which makes it highly inefficient.

    Here's an example: Lets say I want to display records 51-75 in my web client, ordered by lastname, firstname. This is what my SQL looks like.

    select *
    from
    (select firstname, lastname, rownum as newcnt
    from
    (select firstname, lastname
    from employee
    order by lastname, firstname)
    where rownum <= 75)
    where newcnt > 50

    Even though I am only displaying 25 records to my client I am still parsing though 10,000 records each time due to the forced inline order by query. Is there any way I can optimize this query?

    thanks,
    -Eddie Hernandez

  2. #2
    Join Date
    Mar 2001
    Posts
    17
    I think you should use PL/SQL Table for this. Collect whole record set from physical table to PL/SQL table with lastname, firstname and start index 1 incremented by 1.

    From this index value you can easily separate 25 records to display. In this case only once query is fired against database. Next time u have result set in memory which u can display easily.

    Kancha


  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Hi Kancha,

    Can you please give an example hoe you Mean... If possible give me an Code That could help... Thanks..

    qaisar@canada.com

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sorry Kancha, but that is absolutely *not* what you want to do. *Never* bring back more data than you want - to PL/SQL or any other routine.

    The issue at hand is the performance of the query given that we only wish to display 25 rows.

    Pulling *all* 10,000 rows across the wire and populating a PL/SQL table *before* restricting the list is certainly *not* going to help.

    As far as the performance of the original query goes...

    Unfortunately, you can't do much better.
    On the bright side, however, the performance should not be too bad.

    Here are some examples for you. I performed the same types of queries against a 22,000 record table...

    When the ORDER BY field is NOT indexed
    AND the ROWNUM restriction IS used
    ============================

    The Plan:
    -----------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=22734 Bytes=1364040)
    --VIEW (Cost=98 Card=22734 Bytes=1364040)
    ----COUNT (STOPKEY)
    ------VIEW (Cost=98 Card=22734 Bytes=1068498)
    --------SORT (ORDER BY STOPKEY) (Cost=98 Card=22734 Bytes=682020)
    ----------TABLE ACCESS (FULL) OF ORG (Cost=33 Card=22734 Bytes=682020)

    Note the use of the SORT(ORDER BY STOPKEY). This means that once it has sorted to the point that it knows it has the lowest 75, it will stop sorting the rest.

    Note the COUNT(STOPKEY). This means that when it is assigning rownumbers for all the rows, it knows it can stop that at 75 as well.

    Execution Stats:
    ------------------
    Time: 0.3 seconds
    Logical Reads: 846


    When the ORDER BY field is NOT indexed
    AND the ROWNUM restriction is NOT used
    ============================
    NOTE: This is the solution that Kancha was proposing.

    The Plan:
    -----------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=98 Card=22734 Bytes=1068498)
    --VIEW (Cost=98 Card=22734 Bytes=1068498)
    ----SORT (ORDER BY) (Cost=98 Card=22734 Bytes=682020)
    ------TABLE ACCESS (FULL) OF ORG (Cost=33 Card=22734 Bytes=682020)

    Note that there are no STOPKEY keywords in this plan. The entire table is sorted and returned.

    Execution Stats:
    ------------------
    Time: 28.14 seconds
    Logical Reads: 855


    When the ORDER BY field IS indexed
    AND this index 'covers' the entire query
    AND the ROWNUM restriction IS used
    ============================

    The Plan:
    -----------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=22734 Bytes=591084)
    --VIEW (Cost=1 Card=22734 Bytes=591084)
    ----COUNT (STOPKEY)
    ------VIEW (Cost=1 Card=22734 Bytes=295542)
    --------INDEX (FULL SCAN) OF XPKORG (UNIQUE) (Cost=25 Card=22734 Bytes=90936)

    Note that we once again have STOPKEY usage.
    Note also that we are scanning the index rather than the table

    Execution Stats
    ------------------
    Time: 0.03 seconds
    Logical Reads: 5


    When the ORDER BY field IS indexed
    AND this index 'covers' the entire query
    AND the ROWNUM restriction IS NOT used
    ============================

    The Plan:
    -----------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=22734 Bytes=295542)
    --VIEW (Cost=1 Card=22734 Bytes=295542)
    ----INDEX (FULL SCAN) OF XPKORG (UNIQUE) (Cost=25 Card=22734 Bytes=90936)

    Execution Stats
    ------------------
    Time: 22.23
    Logical Reads: 1542


    When the ORDER BY field IS indexed
    AND this index DOES NOT 'cover' the entire query
    AND the ROWNUM restriction IS used
    ============================

    The Plan:
    -----------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=22734 Bytes=1364040)
    --VIEW (Cost=88 Card=22734 Bytes=1364040)
    ----COUNT (STOPKEY)
    ------VIEW (Cost=88 Card=22734 Bytes=1068498)
    --------TABLE ACCESS (BY INDEX ROWID) OF ORG (Cost=88 Card=22734 Bytes=682020)
    ----------INDEX (FULL SCAN) OF XPKORG (UNIQUE) (Cost=25 Card=22734)

    Execution Stats:
    -------------------
    Time: 0.07 seconds
    Logical Reads: 36

    When the ORDER BY field IS indexed
    AND this index DOES NOT 'cover' the entire query
    AND the ROWNUM restriction IS NOT used
    ============================

    The Plan:
    -----------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=22734 Bytes=1068498)
    --VIEW (Cost=88 Card=22734 Bytes=1068498)
    ----TABLE ACCESS (BY INDEX ROWID) OF ORG (Cost=88 Card=22734 Bytes=682020)
    ------INDEX (FULL SCAN) OF XPKORG (UNIQUE) (Cost=25 Card=22734)


    Execution Stats:
    -------------------
    Time: 23.08
    Logical Reads: 11214



    ===================================
    S, what we should we learn from this?

    - The ROWNUM trick for 'windowing' result sets really *does* make things much faster (up to 400 times). This supports my original contention: *Never* return more data then you plan on using, ever.

    - When the query is simple, make sure you have an index that matches the ORDER BY.

    - I didn't show it, but I tried a solution using the ROW_NUMBER analytical function. It did not perform as well as the ROWNUM virtual field did.

    - Table-scanning a single table should not be much of an issue, if your actual query in question is as simple as your example.

    However, all this still does not help your original question. If your base query is complex, but the results are ordered, there is precious little that can be done. In the realm of what is possible, however, the ROWNUM trick is absolutely your best bet.

    Hope this helps,

    - Chris


    NOTE: All tests were run multiple times. I am on an isolated server - it is all mine. Nothing else is running on it. The cache is large enough to hold the entire target table and indexes, so no physical reads were necessary for any of the tests.

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