Re: Problem with ROWNUM and VIEW with compound query

Dear Stock:

Thanks for your reply. In our application, we keep the user state in in the Application Server session. To serve the lookup, once we get the 500 or so keys, we put them in the HTTP session (which causes it to be replicated to the other App Servers so that failover is possible). I am aware that there are ways in which I can reduce the data replicated by simply storing the page number as opposed to all the keys.

I was thinking that, rather than putting all this logic in the application codes, simple technic looking using the ROWNUM is far simpler to code and understand.

Thanks for the offer to send examples. I would appreciate a copy of it as it would give another solution to consider (My e-mail id is newsguy-venkatp -> sneakemail(dot)com).

Thanks,
Venkat.  

In article <xaCdnYA-ert2uS-iRVn-gw_at_comcast.com>, mcstock says...
>
>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.
>>
>
>
Received on Tue Nov 18 2003 - 04:52:01 CST