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

From: Bass Chorng <bchorng_at_yahoo.com>
Date: 27 Dec 2001 12:07:31 -0800
Message-ID: <bd9a9a76.0112271207.3ac4f2e3@posting.google.com>

Cantonese Boy <waynewan_at_yahoo.com> wrote in message news:<3C22DFB6.BC06AED_at_yahoo.com>...
> 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.

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.

This will make your truncate immediate. Otherwise oracle would have to de-allocate all the extents (default) and that can take long time.
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 Received on Thu Dec 27 2001 - 14:07:31 CST