HI,
GUYS I WANT TO DO PAGING IN ORACLE QUERY

SUPPOSE I HAVE TABLE WHICH GIVES OUTPUT OF 100 RECORDS
AND I HAVE A PAGESIZE OF 20 RECORDS..

I Want MY Select query output of 100 records when supply the page no say 2
i wnat 20 records of page 2 ..



now the issue how to select the folloing output in my query

1>how to get the total recordcount of the select query----eg:100
2>how to get the total no of pages eg:100/20=5 ''say 20 pagesize
3>how to select only 20 records on page say i pass page 2

i was using ASP side paging....
but what happens with that i have select all the records
and then filter the records at front end..

i have report which has 20,000 records but as i have pagesize as 500

for showing 500 records on say 3 page i have to fetch 20,000
records and then using asp i get 500 records to show
but that creating lot of load on server ....
i wantt to avoid it..

guys will give your inputs....
may be ypou must have come across such issues
regds,
Navin Mahindroo

On 5 Jul 2003 00:08:41 -0700, navinsm2@rediffmail.com (Navin) wrote:

>guys will give your inputs....

Buy a tutorial or read a manual.
and NEVER EVER fetch records to the client which you are not going to
use. Almost anything can be done at the server side. If you don't do
that, you will be much liked by your end-users


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

Lets say you want to 'page' the query

select *
from t
where x = :host_var
order by y

You want the rows 1 to 20, then 21 to 40, and so on:

1/ If your query is 'fast', not using to much resources, you can write your
query as

select *
from ( select a.*, rownum r
from ( select *
from t
where x = :host_var
order by y ) a
where rownum <= :HigerBound )
where r >= :LowerBound

This query gets all rows between lowerbound and higerboud.

This works only with Oracle 8i and up: the 'order by' in inline views is not
supported in older versions.

2/ If your query has performance issues, you can think about creating a hits
table :

create table hits
(sesid number, seqno number, rid rowid,
primary key(sesid,seqno)
);

You populate this table with something like:

insert /*+ APPEND */ into hits(sesid,seqno,rid)
as select userenv('sessionid'),rownum,rid
from (select t.rowid rid
from t
where x = :host_var
order by y )
/

or with PL/SQL :

seq := 0;
for y in ( select t.rowid rid from t where x = :host_var order by y )
loop
seq := seq + 1;
insert into hits values ( userenv('sessionid'), seq, y.rid );
end loop;

You need to pass that userenv('sseionid') from page to page as a hidden
variable to identify the result set.

Then, use something like

select * from t, hits
where hits.seqno between :lowBound and :highBound
and hits.sess_id = :theSessionId
order by hits.sess_id, hits.seqno

(remove nospan from my reply adres)

"Navin" <navinsm2@rediffmail.com> wrote in message
news:5dc7f532.0307042308.7442dd92@posting.google.c om...
> HI,
> GUYS I WANT TO DO PAGING IN ORACLE QUERY
>
> SUPPOSE I HAVE TABLE WHICH GIVES OUTPUT OF 100 RECORDS
> AND I HAVE A PAGESIZE OF 20 RECORDS..
>
> I Want MY Select query output of 100 records when supply the page no say
2
> i wnat 20 records of page 2 ..
>
>
>
> now the issue how to select the folloing output in my query
>
> 1>how to get the total recordcount of the select query----eg:100
> 2>how to get the total no of pages eg:100/20=5 ''say 20 pagesize
> 3>how to select only 20 records on page say i pass page 2
>
> i was using ASP side paging....
> but what happens with that i have select all the records
> and then filter the records at front end..
>
> i have report which has 20,000 records but as i have pagesize as 500
>
> for showing 500 records on say 3 page i have to fetch 20,000
> records and then using asp i get 500 records to show
> but that creating lot of load on server ....
> i wantt to avoid it..
>
> guys will give your inputs....
> may be ypou must have come across such issues
> regds,
> Navin Mahindroo