RE: Nologging clause

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 1 Apr 2009 12:56:38 -0400
Message-ID: <017D9A95EFD04D9BAD5BFB7E8164D59B_at_rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman
Sent: Wednesday, April 01, 2009 11:58 AM To: Greg Rahn
Cc: oracle-l_at_freelists.org
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_at_ucdavis.edu
(530) 754-6208

-----Original Message-----
From: Greg Rahn [mailto:greg_at_structureddata.org] Sent: Tuesday, March 31, 2009 5:27 PM
To: William Wagman
Cc: oracle-l_at_freelists.org
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_at_ucdavis.edu> 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
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 01 2009 - 11:56:38 CDT