Re: Problem with ROWNUM and VIEW with compound query

i'd recommend going back to your original technique -- returning the PK's for the rowset

could you elaborate on the problem of 'the need for replicating these keys for fail over'?

apparently you're storing the user's set of keys in the database -- have you thought of simply storing them in hidden fields or a JavaScript array in the HTML streamed back to browser? i've found this to be a very effective way of paging rows on the web (i can send a presentation that includes examples, if you'd like)

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Venkat" <Venkat_member_at_newsguy.com> wrote in message
news:bot50b01esc_at_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.

>