Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how does truncate table work?
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 - 04:53:28 CDT