RE: Nologging clause
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <wjwagman@xxxxxxxxxxx>, "'Greg Rahn'" <greg@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 1 Apr 2009 12:56:38 -0400
Well, not really. But if you were to use partitioning for deletes and you
could tolerate a wide enough time range to partition by, and if the
expiration time is immutable (unless you want row movement (ugh!) and known
at insert time, then you could unhook obsolete partitions, so undo scales
with dictionary activity rather than number of rows.
Depending on your operational timing requirements you might also be able to
minimize logging with an overall UNION ALL view of several time based tables
and then do a copy-keeping unexpired ctas to a new table and truncate or
drop the old. Even if you need tight time window deletion on expiration, you
could just toggle a status byte then and only suffer the logging for that
byte plus the dictionary cost of the subsequent truncate or drop, plus any
dictionary changes needed as you rotate through component objects over time.
Unless someone knows something that is not coming to my mind, deleting an
individual row has to log and the only proxies that don't log scaling with
the number of row-bytes removed are truncate and drop (and swap partition,
but that doesn't really get rid of the rows until a drop or truncate of what
you unhooked.)
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of William Wagman
Sent: Wednesday, April 01, 2009 11:58 AM
To: Greg Rahn
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Nologging clause
Greg,
Again, thank you. The next question I have is in regards to deletes. Is it
possible to do a direct path delete (does that even make sense)? As I
mentioned this will be a repository for CAS tickets and they will be deleted
at their expiration time. Is it possible to avoid generation of redo on a
delete as well?
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
-----Original Message-----
From: Greg Rahn [mailto:greg@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, March 31, 2009 5:27 PM
To: William Wagman
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Nologging clause
This is correct. The [NO]LOGGING only changes direct path operations.
That is, inserts with the /*+ APPEND */ hint.
So if you want NOLOGGING you need either:
- NO ARCHIVE LOG mode
- ARCHIVE LOG mode, NOLOGGING, and APPEND hint
On Tue, Mar 31, 2009 at 5:04 PM, William Wagman <wjwagman@xxxxxxxxxxx>
wrote:
> It appears that he is saying that if a table is set to nologging and my
insert mode is append then even if archive logging is on no redo will be
generated. I'm still not sure I am understanding this correctly but I take
that to mean
>
> SQL> insert /*+ APPEND */ into <table>;
>
> Then no redo will be generated. Is that a correct understanding?
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
