Re: PAGING IN ORACLE

From: Luc Gyselinck <Luc.Gyselinck_at_nospampandora.be>
Date: Sat, 05 Jul 2003 10:15:55 GMT
Message-ID: <vzxNa.6932$7h.12290@afrodite.telenet-ops.be>

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_at_rediffmail.com> wrote in message news:5dc7f532.0307042308.7442dd92_at_posting.google.com...
> 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
Received on Sat Jul 05 2003 - 05:15:55 CDT