Re: Avoiding generating redo logs

On Sat, 16 Sep 2006 09:19:58 +0100, Jeremy <jeremy0505_at_gmail.com> 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.
>

Sounds like a recipe for disaster.

>I wanted to design this, if possible, so that there is no redo generated
>for DML performed on this table.
>

You can't. There is an underdocumented option to turn of logging *completely*, but this will corrupt your database.

>The NOLOGGING clause used when creating a table appears from the doc I
>read to state that its creation won't be recorded in the redo logs and
>that subsequent "direct loader (sql loader) and direct path INSERT
>operations against the object" will not be logged. Does this mean that a
>regular
>
> insert into mycache(id,val) values (1,'fred');
>
>would be logged?

It sure does just mean that. And an INSERT /*+ APPEND */ would turn all indexes invalid
>
>I cannot use a GLOBAL TEMPORARY table as the data will not be retained
>outside of the session (as ours is a web app over http a new session is
>started for every new screen displayed).

Sounds like you *can* use a global temporary table. In a temporary table you can destroy your data over a commit.

>
>Also wondering if there are differences between 9i and 10g (this will go
>out on 9i first).

9i is dead after July 2007. I think you should reconsider. Apart from that, any new major release of Oracle comes with a manual called 'New Features Manual' and several upgrade courses.
>
>Thanks for any pointers.

--
Sybrand Bakker, Senior Oracle DBA