paged Cursor

From: Rohrbacher, Ing. Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: 1997/12/10
Message-ID: <348EFF44.9C64F063@sbox.tu-graz.ac.at>

This is a multi-part message in MIME format. --------------925CB0B80AE0006F8981207A
Content-Type: multipart/alternative; boundary="------------9F9846ACD827AE5896786D07"

--------------9F9846ACD827AE5896786D07
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

I had, and will have the problem many times :

I implemented to possible solutions, each of has advantages and disadvantages.

solution 1 : Remember the SQL Statement ( for instance in an tmp table or for zero administration in the htmlpage itself   utilizing the whole page as a form and implementing the link as a form.submit in a simple javasrcipt.
  and run the sql Statement with dbms_sql.... Package if you happen to use a Oracle database.
  Give the query a query id e.g. a number ... On the HTML page implement the link or whatever you are using to call the next page with appropriate
parameters specifing the query id and the row where the page should start.

How to come to the next page :
Open then Cursor using dbms_sql or whatever tool you are using to fetch rows from database
Primitivly fetch till you have reached the start of the desired page and start generating the page from this point.

Advantages : Only sql statement is stored temporarilly. Possibility to change the given statement from page to page. Most import ORDER BY as you please.
More real data as in solution 2 because changed and updated rows are reflected from call to call.

Disadvantages :
  1.) Empty fetching from from first result set row to starting point of
  page can degrade performance if you happen to have a very complex statement and/or where large result set.   My expiriences are that the biggest part of response time is not fetching data, but getting the potential result set for   a Query. I did some measurements on a large Oracle database and found out that most time spend is between
  opening a cursor and fetching the first row. Time from the first to the last row was often a small part of the time before.

==> slower response time than solution 2

2.) Cursor needs to be executed each time. If you have a complex query this means very slow paging

solution 2 : Store a unique key kombination for a single row of the result set in a tmp table, ( either one table for   each query, or single table, sorting out queries with a query id )   Tmp table needs to have a column for a generated sort value, filling it with the value of rownum of the original cursor.   In the HTML page define a appropriate link with the sort value as parameter for other pages
  How to come to the next page :
  Write programm that is called with sort value ( that is the number of the row in the result set ) were the page should start   ( lets name it page_start_sort_value ) and query id   Select alle rows from tmp table where sort value is between page_start_sort_value and page_start_sort_value + rows per page and all other information. e.g. for your example with emp :

select ename, empno, sort_value from tmp, emp where tmp.sort_value between page_start_sort_value and ( page_start_sort_value + rows_per_page ) and tmp.empno = emp.empno

order by sort value

Advantages : Usually very fast pageing. Easy to handle. Only ONE executioin of original query.

Disadvantages : All results for each queries are stored. This can lead to enourmous tmp data depending on query traffic and content, but usually you can limit queries to a reasonable count of rows returned. E.g. in public library catalogs (OPAC ) result sets are often limited to a few hundred titels. Nobody likes to browse through 1000+ rows if he or she is looking for a single row. If a query returns more than 150 rows more than 75% of users start over with refined kriterias.

You can find an example of this kind of solution under UBTUG.BIS Opac V 1.1 - Eingabe

I hope I could give a advise.
Regards ,
Boris i.e Robo.

--------------9F9846ACD827AE5896786D07
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>

Hi,

  I had, and will have the problem many times :
  
  I implemented to possible solutions,   each of has
advantages and disadvantages.
  
  solution 1 :   Remember the SQL Statement  ( for
instance in an tmp table  or  for zero administration in the htmlpage itself
   utilizing the whole page as a form and implementing the link
as a form.submit in a simple javasrcipt.
   and run the sql Statement  with  dbms_sql.... 
Package  if you happen to use a Oracle database.
   Give  the query  a  query id  e.g. a number
...
  On the HTML page  implement  the link or whatever you
are using to call the next page with appropriate
  parameters specifing  the query id  and  the row
where  the page should start.
  
  How to come to the next page :
  Open then Cursor using  dbms_sql   or whatever
tool you are using to fetch rows from database
  Primitivly  fetch  till you have reached the start 
of  the desired page and start generating the page from this point.

 Advantages :  Only sql statement is stored temporarilly.
  Possibility to change the given statement  from page to
page. Most import  ORDER  BY as you please.
  More real data as in solution 2 because changed and updated rows
are reflected from call to call.

 Disadvantages :
   1.)  Empty fetching from from first result set row to starting
point of
   page  can degrade  performance if you happen to have
a very complex statement and/or where large result set.
   My expiriences are that  the biggest part of response 
time  is  not fetching data, but  getting the potential result set for
   a Query.  I did some measurements on a large Oracle database
and found out that   most time spend is between
   opening  a cursor   and fetching the first row. 
Time from the first to the last row was often a small part of the time before.

  ==>  slower response time than  solution 2

 2.) Cursor needs to be executed each time. If you have a complex
query this means very  slow paging
  
  solution 2 :  Store a unique key kombination for a single
row of the result set in a tmp table,  ( either one table for
   each query, or single table, sorting out queries with 
a query id )
   Tmp table needs to have a column for a generated  sort
value, filling it with the value of rownum of the original cursor.
   In the HTML page define a appropriate link with the sort value
as parameter for other pages
   How to come to the next page :
   Write programm that is called with  sort value ( that is
the number of the row in the result set ) were the page should start
   ( lets name it page_start_sort_value ) and query id
   Select alle  rows from tmp table where  sort value
is between page_start_sort_value and page_start_sort_value + rows  per page     and  all other information. e.g. for your example with emp :

   select  ename, empno, sort_value  from  
tmp, emp  where  tmp.sort_value between page_start_sort_value and ( page_start_sort_value + rows_per_page )   and  tmp.empno = emp.empno
    order by sort value
  

 Advantages : Usually very fast  pageing.  Easy to handle.
  Only ONE executioin of original query.

 Disadvantages :  All results for each queries are stored.
This can lead to enourmous tmp data depending on query traffic and content, but usually you can limit queries to a reasonable  count of rows  returned.  E.g. in public library catalogs (OPAC ) result
 sets are often  limited to a few hundred titels.  Nobody
likes to browse through 1000+  rows if he or she is looking for a single row.
 If a query returns more than 150 rows  more than 75% of users 
start over with refined kriterias.

You can find an example of this kind  of solution under  <A HREF="http://cisc.tu-graz.ac.at:8000/rb/owa/opac.eingabe_v1">UBTUG.BIS
Opac V 1.1 - Eingabe</A>

I hope I could give a advise.
 Regards ,
   Boris      i.e  Robo.</HTML>

--------------9F9846ACD827AE5896786D07--

--------------925CB0B80AE0006F8981207A
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Boris Rohrbacher
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Boris Rohrbacher
n:              Rohrbacher;Boris
org:            Zentraler Informatikdienst der Technischen Universität Graz 
adr:            Steyrergasse 30 ;;oder Griesplatz 36/1;GRAZ;Styria;A-8027;Austria
email;internet: rohbo_at_sbox.tu-graz.ac.at
title:          freier Mitarbeiter 
tel;work:       +43-316-873-6391
tel;fax:        +43-316-873-7766
tel;home:       +43-316-971221

x-mozilla-cpt: ;0
x-mozilla-html: FALSE

version:        2.1
end:            vcard

--------------925CB0B80AE0006F8981207A-- Received on Wed Dec 10 1997 - 00:00:00 CST