Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how does truncate table work?

Re: how does truncate table work?

From: VNS <vagishsinha_at_gmail.com>
Date: 22 Dec 2004 07:56:10 -0800
Message-ID: <1103730970.280268.298950@f14g2000cwb.googlegroups.com>


Very well explained. I was aware that truncate is better option than delete or drop and recreate although it does not physically deletes the records but you explained why.

Regards,
Vagish

quarkman wrote:
> 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 Wed Dec 22 2004 - 09:56:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US