n records at a time

[marius]

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 ?

[lesstjm]

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.

[bensr]

Try an inline view:

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

[chrisrlong]

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

[PSoni]

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

[chrisrlong]

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

[PSoni]

Thanks for your kind suggestion
Pitamber Soni

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