RE: Nologging clause
Date: Wed, 1 Apr 2009 14:33:59 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F146CB3AC_at_AAPQMAILBX02V.proque.st>
Well, right, without the APPEND hint, it's just a conventional insert, and will look at blocks on the freelist, (or pointed to by bitmaps as having space available). Then you have the same problem, when you update a block that already has some rows in it, it's GOT to be protected by redo.
-Mark
From: Jared Still [mailto:jkstill_at_gmail.com]
Sent: Wednesday, April 01, 2009 2:32 PM
To: Bobak, Mark
Cc: wjwagman_at_ucdavis.edu; Greg Rahn; oracle-l_at_freelists.org
Subject: Re: Nologging clause
On Wed, Apr 1, 2009 at 11:20 AM, Bobak, Mark <Mark.Bobak_at_proquest.com<mailto:Mark.Bobak_at_proquest.com>> wrote: Bill,
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.
Which also explains why no-logging INSERTs must include the APPEND hint.
Jared
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 01 2009 - 13:33:59 CDT