truncate /*+append*/
in [Oracle]
|
From: Mladen Gogala on 3 Mar 2006 09:12 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
From: cpiodumpv on 3 Mar 2006 09:53 Excellent response if there is undo I couldnt' wrap my mind around what the undo was. I agree. In the future I should not quote experts but prove these things to myself. Regards
From: thomas.kyte on 3 Mar 2006 11:14 Mladen Gogala wrote: > 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 > that is not what I've said - insert /*+ APPEND */ will bypass UNDO generation for the table data (but not any indexes on said table). It will bypass redo generation for the table (but never the indexes) IF the database is noarchivelog OR nologging has been set on the table, else full logging is performed for the table data (then there is that force logging that can affect this as well at the database level) undo to protect the dictionary (and redo) entries that are made to advance the high water mark will of course be generated. > 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 indeed. > time as he's probably human, just like the rest of us. Tom Kyte is a great I am. One of my mantras is "Question Authority", use it in most all of my seminars as the THEME in fact. > 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. hmmm ;) > > > -- > http://www.mgogala.com
From: Holger Baer on 3 Mar 2006 11:32 thomas.kyte(a)oracle.com wrote: > Mladen Gogala wrote: > >>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 >> > > that is not what I've said - insert /*+ APPEND */ will bypass UNDO > generation for the table data (but not any indexes on said table). > > It will bypass redo generation for the table (but never the indexes) IF > the database is noarchivelog OR nologging has been set on the table, > else full logging is performed for the table data (then there is that > force logging that can affect this as well at the database level) > > undo to protect the dictionary (and redo) entries that are made to > advance the high water mark will of course be generated. > Sorry for misquoting you, obviously I should follow the same advice that I give. I was preparing a more concise follow-up, but since its not finished yet, and examples are missing too, this seems to be something for the weekend to investigate (so that I'll be able to remember that correctly). Thanks for the well deserved correction Holger
From: Jonathan Lewis on 3 Mar 2006 19:46 "Mladen Gogala" <gogala(a)sbcglobal.net> wrote in message news:pan.2006.03.03.14.12.22.656863(a)sbcglobal.net... > nothing personal: I even check things that Jonathan Lewis says, although > he is definitely more then a mere human. > I can confirm that I am, in fact, a mere human; and I am delighted that you check things that I say, because I do try to make it possible for people to check what I say. -- Regards Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Oracle Error: Cusor invalidated due to Parallel Execution Next: Oracle export error |