does "truncate table" affect the performace of the associated indexes?

Cantonese Boy

unread,

Dec 21, 2001, 2:07:34 AM12/21/01

to

Hi,

I have a table which needed to be truncated and re-import data
everyday. Do I needed to rebuild the associated indexes to
get a better perfermance after each "truncate"?
(Will there any performance problem
in the indexes tablespace)

Thanks for any help

W.

Mark D Powell

unread,

Dec 21, 2001, 9:16:53 AM12/21/01

to

Cantonese Boy <wayn...@yahoo.com> wrote in message news:<3C22DFB6...@yahoo.com>...

Wayne, When you truncate the table the indexes are also truncated.
When you reload Oracle updates the indexes. As a general rule you are
good to go though you might want to consider updating the statistics
immediately after the reload.

-- Mark D Powell --

John Darrah

unread,

Dec 21, 2001, 11:15:52 AM12/21/01

to

You may also see better overall performance if you drop the indexes prior to
importing the data and recreate them after the load has finished. This way you
can use nologging and parallel systax to speed things up.

"Mark D Powell" <mark....@eds.com> wrote in message
news:178d2795.01122...@posting.google.com...

--
Posted from d225s229.hotbank.com [63.83.225.229]
via Mailgate.ORG Server - http://www.Mailgate.ORG

Ushkalo Igor

unread,

Dec 21, 2001, 1:14:15 PM12/21/01

to

Mark D Powell wrote:

> Cantonese Boy <wayn...@yahoo.com> wrote in message news:<3C22DFB6...@yahoo.com>...

> Wayne, When you truncate the table the indexes are also truncated.
> When you reload Oracle updates the indexes. As a general rule you are
> good to go though you might want to consider updating the statistics
> immediately after the reload.

I suggest drop indexes before loading, recreate
after loading and analyze table compute statistics.
It will be nice but maybe more complicated B-)

-- Best regards, Igor Ushkalo (igorus!) - igorus(at)mail.ru,
ICQ #19972198

krishnananda

unread,

Dec 21, 2001, 2:53:44 AM12/21/01

to

yah u need to rebuild the indexes after every truncate since the index nodes
will still contain the entries of ur data truncated

"Cantonese Boy" <wayn...@yahoo.com> wrote in message
news:3C22DFB6...@yahoo.com...

Bass Chorng

unread,

Dec 27, 2001, 3:07:31 PM12/27/01

to

Cantonese Boy <wayn...@yahoo.com> wrote in message news:<3C22DFB6...@yahoo.com>...

If you reload the table everyday, I suggest that you truncate
with "reuse storage". This can make a big difference depending
on the size of the table.

1. This will make your truncate immediate. Otherwise oracle would
have to de-allocate all the extents (default) and that can take
long time.

2. This will avoid enqueue contention to other sessions.

Oracle holds ST enqueue when allocating or de-allocating extents.
There is only 1 ST enqueue in the entire database.

This way, you also avoid enqueue contention when you load the data.

-Bass Chorng

Mark D Powell

unread,

Jan 2, 2002, 12:53:25 PM1/2/02

to

"krishnananda" <krishn...@vwebsol.com> wrote in message news:<9vvt9g$h6b$1...@news.vsnl.net.in>...

krishnananda, when you truncate the table the high-water mark is also
reset for the indexes and all extents except the initial extent are
returned to free space (unless reuse storage is specified). Oracle
does not clean the old, but no longer used blocks, until the blocks
are re-allocated to the same or a new object.