RE: Nologging clause

I think I already answered that in my follow-up to Greg's reply.

No, updates and deletes will *always* log. If you think about how direct path works, and why it's restricted to the operations it's restricted to, it sort of makes sense. Without getting into all the gory details, direct path can do nologging and get away with it, cause when it's doing inserts, it's doing them into brand new, never before used blocks, that do *not* already contain data. Therefore, rollback is simply a de-allocation of those blocks from the segment. In the case of 'delete from tab1 where col_a = 32;', well, what if the affected block has rows w/ col_a values of 30-39 in it? You're *only* deleting the row where col_a = 32. So, the remaining data in the block *must* be protected by redo! If you do a direct path insert, do a million inserts, all those rows are guaranteed to be inserted into freshly allocated empty blocks, so, "rollback" consists of simply de-allocating those blocks. There are no other rows in the block that would get caught up in the roll  back and lost.

If you want to efficiently delete, there are a few options.

The two that come to mind are:

partitioning (drop partition is an atomic DDL operation, that's not proportional to the number of rows and will only log data dictionary updates) but this may entail application redesign, and depending on your deletion criteria, may not be applicable in all circumstances.

CTAS, where you can do something like this: create table keep_this_data NOLOGGING as select * from big_pile_o_data where (predicates-that-specify-the-rows-to-keep); truncate table big_pile_o_data;
insert /*+ append */ into big_pile_o_data select * from keep_this_data; drop table keep_this_data;

Consider that, in the CTAS example, because nologging operations are so much more efficient than conventional deletes, the above scenario should work well even when the number of rows you're deleting is a relatively small percentage of the total. 20% deletes would almost certainly benefit, and even as little as 10% deletes, perhaps less, would probably still benefit from this method.

Note that all the above operations will do minimal logging, and should be quite efficient.

Hope that helps,

-Mark

-----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