Re: Truncating table gives better performance

Anamika wrote:

> I have a table which does not have indexes setup, but is used as a
> temporary table by some sql procs. The table itself does not grow beyond a
> thousand rows.
> Recently, noticed that one sql proc was taking too much time to compute
> and doing a tkprof showed that it spent time using this table.
> Just for kicks, did a 'delete' of all rows on the table, but no
> improvement in time taken.
> But a 'truncate' on the table showed tremendrous improvement in time taken
> ?
> Looking to find the root cause of this behaviour.
> Appreciate feedback.
>
> TIA,
> -A

The highwater mark kept growing and growing forcing Oracle to read more and more blocks. A table with 1000 rows of data does not need an index ... but if used as you are using it ... needs to be truncated.

I would certainly explore using a global temporary table instead of what you are doing ... or ... dropping the temp table entirely as it is likely not really necessary.

Daniel Morgan Received on Tue Apr 30 2002 - 14:48:19 CDT