n records at a time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: n records at a time

  1. #1
    Join Date
    Feb 2001
    Posts
    1

    Thumbs up

    How can I read the first n records with a select query, and then the next n an then the next n and so on ?

  2. #2
    Join Date
    Jan 2001
    Posts
    515

    Talking In PL/SQL or Not?

    Do you want to do this in PL/SQL or just at the command prompt?

    If you do it in PL/SQL you can use C1%ROWCOUNT. if at the prompt you might be able to use ROWNUM.

  3. #3
    Join Date
    Feb 2001
    Posts
    180

    Lightbulb Inline view

    Try an inline view:

    SELECT * FROM
    (SELECT rownum rnm , t.*
    FROM table t)
    WHERE rnm >= &&part * 10
    AND rnm < (&&part + 1 ) * 10
    Regards
    Ben de Boer

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Here is a link to a previous discussion on the topic.

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5480[/url]

    Yes, you want to use ROWNUM, but not really sure what bensr was trying to show.

    Here is the example from the thread:

    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

    HTH,

    - Chris

  5. #5
    Join Date
    Feb 2001
    Posts
    125

    n records at a time

    If you want select particular column
    then it is very easy

    see my example

    select rownum , dname from dept
    having rownum between 1 and 5 group by rownum, dname;

    abvoe will select from rownumber 1 to 5

    select rownum , dname from dept
    having rownum between 6 and 10 group by rownum, dname
    above will select from rowno 6 to 10


    thanks
    Pitamber Soni

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    PSoni, there are a couple of issues with your solution. Granted, it is very easy to do. The fundamental problem, however, is that it is unable to handle an ORDER BY, which is the general use of a 'scrolling window' query such as he has requested. Of course, he did not actually mention that he needed the ORDER BY - it was assumed.

    However, let's assume that a sort is not required. The next issue is performance, which was discussed at length in the link I provided.

    My query without the ORDER BY, is:

    SELECT
    ---*
    FROM
    ---(
    ---SELECT
    ------A.*,
    ------ROWNUM---RN
    ---FROM
    ------(
    ------SELECT
    ---------*
    ------FROM
    ---------TABLE_X
    ------)---A
    ---WHERE ROWNUM <= 10
    ---)
    WHERE
    ---RN >= 6

    This give us:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 VIEW
    2 1 COUNT (STOPKEY)
    3 2 TABLE ACCESS (FULL) OF 'TABLE_X'


    Now, your query is:

    SELECT
    ---ROWNUM ,
    ---COL2
    FROM
    ---TABLE_X
    GROUP BY
    ---ROWNUM,
    ---COL2
    HAVING
    ---ROWNUM BETWEEN 6 AND 10

    ...which give us:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 FILTER
    2 1 SORT (GROUP BY)
    3 2 COUNT
    4 3 TABLE ACCESS (FULL) OF 'TABLE_X'


    The differences of note are:
    - Your solution does not make use of the STOPKEY option. That means that your entire result set if processed, whereas mine can stop when it hits the 10th record.
    - The reason your entire result must be processed is the overhead of the GROUP BY. This accounts for the extra SORT step.

    As in the discussion in the original post on this topic, this doesn't make much of a difference in the 12 record example I use. When you have thousands or millions of records in a table with possibly thousands of records being returned in the actual result set that you are trying to 'break up' or 'scroll through', this makes all the difference in the world.

    Hope this helps.

    - Chris

  7. #7
    Join Date
    Feb 2001
    Posts
    125


    Thanks for your kind suggestion
    Pitamber Soni

    [Edited by PSoni on 02-26-2001 at 07:00 AM]

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