Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> PgSQL OFFSET option for the query.

PgSQL OFFSET option for the query.

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Tue, 04 Jul 2006 05:25:18 GMT
Message-ID: <pan.2006.07.04.05.25.17.884867@sbcglobal.net>


I'm trying to develop a form of pagination in PHP5 for Oracle and I'm having performance problems. My problems stem from using this logic:

select * from
(Query)
where rownum <= REQ_Page+1*ROWSPERPAGE;

The initial solution was to discard the first REQ_Pages*ROWSPERPAGE-1 rows and this is the problem. I still have to fetch all the rows. If the user requests the very last page, all rows will have to be fetched, all but the last page discarded and then shown in tabular format. The performance, of course, deteriorates with the number of pages and, if the user uses "next page" link to go through the page set, I will re-execute the query over and over again, and repeatedly discard all the rows but the ones on the desired pages. I'm extremely unhappy. PostgreSQL has "OFFSET" clause to the query which tells the query to start returning rows after row N. Is anybody aware of any equivalent in Oracle? I know about the analytic function row_number(), but I would have to provide the windowing clause and I want the pagination to be for a general query, dynamically entered. The problem here is that I generally do not know the query structure in advance and I cannot use the analytic function. Bidirectional cursors and cursor navigation have been improvement requests for years but they're still not supported by Oracle.

-- 
http://www.mgogala.com
Received on Tue Jul 04 2006 - 00:25:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US