Re: Avoiding generating redo logs

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 16 Sep 2006 05:51:30 -0700
Message-ID: <1158411086.860994@bubbleator.drizzle.com>

Jeremy wrote:
> Environment as in sig.
>
> I am a little puzzled about the logging_clause when creating a table. We
> are designing a web application which will have use , at times, a table
> to hold a "cache" for a user. For example, a query is executed which
> takes a few seconds. We store the results in the "cache" and then the
> user can sort and filter on that "cache". This cache I envisage as a
> table structure with its PK being the user_id of the user logged in. The
> data in this table is of no lasting value as it is just a redult of a
> query.
>
> I wanted to design this, if possible, so that there is no redo generated
> for DML performed on this table.

I am in full agreement with Sybrand and others here. Your business case around not generating redo starts with "I want" and that is not a business case.

My recommendation would be that you use global temporary tables to cache the data as in the following:

CREATE GLOBAL TEMPORARY TABLE test (

zip_code     VARCHAR2(5),
by_user      VARCHAR2(30),

entry_date DATE)
ON COMMIT PRESERVE ROWS; This is likely the most efficient structure for your purposes.

But I am left wondering why you think it desirable to let an end user sort and filter anything. Wouldn't it be far easier to just teach them how to use the WHERE and ORDER BY clauses correctly?

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Sat Sep 16 2006 - 07:51:30 CDT