Re: Truncating table gives better performance

Everytime you do a full table scan, Server process reads until the high water mark. Your table dont have any index, so every select on that it means a Full table scan.

When you execute a normal delete, the high water mark dont change the last position (i.e. reads all blocks used and unused). But when you execute a truncate table, the high water mark is reseted.

Regards
Alexis

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
Received on Tue Apr 30 2002 - 11:09:01 CDT