Re: how does truncate table work?

Thank you very much for your help and for detailed explanation.It is great.

Kind Regards,
hope

quarkman <quarkman_at_myrealbox.com> wrote in message news:<oprt5cfenwzkogxn_at_haydn>...
> On 18 Aug 2003 23:00:56 -0700, utkanbir <hopehope_123_at_yahoo.com> wrote:
>
> > Dear Gurus,
> >
> > I am new with oracle database but strong experience with informix . In
> > one of the oracle training which i have recently involved , the
> > lecturer asked the difference between delete and truncate table.
> > Although i did not exactly know , i can guess that , truncate table
> > runs fast because instead of deleting each records , it can invalidate
> > the table by cahnging a flag in data file headers or etc. ( thinking
> > of informix architecture) But the lecturer said truncate table also
> > deleted records but did not generate redo log data thats why it was
> > faster.
> >
> > Is it possible to be very fast for example deleting 100000000 rows
> > without creating redo log entry. I had better drop & create it?
> >
> > I will be appreciate if someone comments about this.
> >
> > Kind Regards,
> > hope
> >
>
> To understand what truncate does, you first need to understand a more
> fundamental problem: how does Oracle know when to stop scanning a table for
> one more row? You might have one row in block 1, and one more row in block
> 1,000,000, with totally empty space in between... yet Oracle's got to know
> to keep on scanning to the millionth block to pick up that last row. And
> then it's got to know that it's pointless reading the 1,000,001th block.
>
> Enter the High Water Mark. It is a set of bytes stored in the very first
> block of the table (the "segment header block"). It contains an address for
> the block that ever once was used to store the last row of data in it. As
> you insert into a table, you fill up newer and newer blocks, and the HWM is
> adjusted to record this progress (minor detail: in fact, for performance
> reasons, the HWM is only adjusted in increments of 5, so as you enter a new
> row in block 6, the HWM is adjusted to show that data has gotten up to
> block 10).
>
> The HWM is adjusted when you insert. But it is never adjusted downwards
> when you do deletes. So even if you insert all 10 blocks full of data, and
> then delete every single row, the HWM still records block 10 as the last
> block that ever contained data.
>
> Now, when you read a table from beginning to end, you actually consult the
> segment header block, find out the block address of the HWM, and scan all
> the way to that block. When you reach it, you stop scanning. In other
> words, anything beyond the point of the HWM is invisible to table scans,
> because the scan never reads beyond the HWM.
>
> All a truncate does is to adjust the HWM so that it reads 'block zero'.
> That's one tiny change to just a few bytes in the segment header block, so
> it takes practically no time at all (and is auto-committed, by the way, so
> there's no going back). When you now select everything from the table, you
> consult the segment header block, discover the HWM is at block 0, and...
> that's it. No need to scan anything, because you've already read block 0.
> Therefore, even though the other blocks of the table still contrain all the
> rows that they did before, they might just as well not do so, because you
> can never read them. The HWM mechanism prevents you from going beyond the
> first block of the table, so the rows are *effectively* gone for good. But
> it all happened really quickly because the change in the HWM itself is just
> an update of a few bytes.
>
> The modification of the HWM itself does generate redo (it's a piece of
> regular DML under the hood, and all normal DML generates redo). But we're
> talking relatively trivial amounts, because the before and after image of a
> few bytes is just a few bytes, plus Oracle's usual overhead.
>
> A drop is also a piece of DML -on the data dictionary tables. So a drop
> generates some redo as well, and probably more than a truncate, because
> lots of data dictionary tables may be involved in recording the existence
> of a table. Re-creating the table also generates some redo. And it's a bit
> of a pain to do, because although you might think you've re-created
> something called "EMP", as far as the database is concerned, that's a
> completely new object compared with the old "EMP" table. So no permissions
> are carried over to the new table< meaning that although I might have had
> select rights on the old EMP table, you'll have to re-grant me that
> permission before I can select from the new one. If all you were after was
> a quick way of efectively deleting lots of rows, truncate is a much better
> way of going, therefore, than a drop and a re-create.
>
> Regards
> HJR
Received on Tue Aug 19 2003 - 09:18:00 CDT