Problem with ROWNUM and VIEW with compound query

From: Venkat <Venkat_member_at_newsguy.com>
Date: 12 Nov 2003 03:19:39 -0800
Message-ID: <bot50b01esc@drn.newsguy.com>

We have recently began to use the ROWNUM based implementation (sql below) for paging data in our web based application.

select *
  from ( select a.* , ROWNUM r
from ( select * from mytable order by id where stmt_date between DATE1 and DATE2 ) a

          where ROWNUM <= ENDING_REC_NUMBER
       )

 where r >= BEGINNING_RECORD_NUMBER  

Our idea was to phase in this method to replace our earlier method throughout the application. In the previous method, we execute an SQL first to load the primary keys wherever paging is needed. For each page we would run another SQL to retrieve the rows by applying the primary keys for that page in the WHERE condition. Among the things we didn't like about this is the need for replicating these keys for fail over.

• Our DB is Oracle 9i Release 2.
• The Configuration parameters are unchanged.
• The table in the query above in our case so far has been a simple view which has a two table join.
• The joined tables contain over 500,000 records
• The response time was around 150ms.
• The query returns a few hundred records and our page size is 20 rows.
• The explain plan would also show that proper indices are used and there are NO full table scans.

Recently, a functional change caused the view to be changed to a compound query. The new view looks like this

select * from a, b where a.id = b.id
UNION ALL
select * from c, d where c.id = d.id

The new query now takes several minutes to run. Upon looking at the explain plan, all the four tables (a, b, c and d) are accessed through full table scans. Simply removing the ROWNUM brings the query response time back to less 200ms and explain goes back to using the proper indices. Upon reading the Oracle documentation, it says complex view merging isn't possible when ROWNUM is used in the view. Though I still expected that the conditions would at least be pushed inside the view. I have tried the PUSH_PRED hint and force this to make it happen without success.

1. I appreciate any explanations, ideas and how one can go about this.
2. In this age of Web applications, does it make sense to have a simple syntax for getting a pageful of rows in Oracle?

Thank you,
Venkat. Received on Wed Nov 12 2003 - 05:19:39 CST