Re: Avoiding generating redo logs

In article <ulkog4a9w.fsf_at_rcn.com>, Galen Boyer says...
> On Mon, 18 Sep 2006, jeremy0505_at_gmail.com wrote:
> > In article <uejua5rr0.fsf_at_rcn.com>, Galen Boyer says...
> >
> >> Why do you need to worry about redo?
> >>
> >>
> > Perhaps I don't - see other responses.
>
> I would look at materialized views myself, because, that is really what
> you are describing (ie, I want to have a query not have to execute
> everytime, but instead, have it stored so I can get the results quicker)
> An MV helps solve that in a most impressive fashion.
>

This may help actually, yes - however when the user requests the data to be refreshed, it needs to be up-to-date rather than based on the latest snapshot (am I right that MVs are periodically refreshed rather than instantaneously with each transaction that may affect the content?).

> But, If you really needed to use a global temp to get away from the redo,
> then you could do something like the following pseudo-code:
>
> IF (p_clientpackage.count_global(client_id) < 1)
> BEGIN
> p_clientpackage.insert_global;
> END
>
> p_clientpackage.get_from_global;
>
>
> This would mean you execute the operation for each client, at most, the
> number of connections you have in the pool. It would also need some
> boundary questions asked, like, what if the query could return 0
> rows...

No problem, thanks for the pointer.

>
> Of course, your global temp would need to be "ON COMMIT PRESERVE ROWS".
>
>

As I understand it, this allows the rows to be preserved across transactions NOT across sessions - and as mentioned, a new session is necessarily created for each user-interaction (apache/mod_plsql).

cheers

-- 

jeremy