Re: truncate /*+append*/

On Fri, 03 Mar 2006 11:06:00 +0100, Holger Baer wrote:

> According to the expert, no undo will be generated *IF USING NOLOGGING* option.
> Try to read and understand what you cite.
>
> Regards,
> Holger

Actually, redo is not a problem here. Undo WILL BE generated, but the question is just what the undo is with /*+ APPEND */ hint. When /*+ APPEND */ is specified, that means: take a bunch of COMPLETELY EMPTY blocks and put them behind the current high watermark. Then, fill them with the desired contents. It's the same as the /*+ LET_THERE_BE_LIGHT */ hint. That means that the "old value" in those blocks, the value that undo mechanism is trying to preserve for the case of possible rollback, doesn't exist. So, just what is the "generated undo" here? Very simple: it's the range of blocks. If you roll your transaction back, your high watermark should not be bumped up and those blocks will remain empty until something else uses them.
The trick with the direct insert and undo is that the "old value" doesn't exist, so there will not be massive undo generation, period. As for calling upon the authority of experts, I disagree with that as a matter of principle. I suspect that Tom Kyte also may be proven wrong from time to time as he's probably human, just like the rest of us. Tom Kyte is a great and extremely knowledgeable guy, but checking works best for me. This is nothing personal: I even check things that Jonathan Lewis says, although he is definitely more then a mere human.

-- 
http://www.mgogala.com