Re: Delete from table taking time

From: steph <stephan0h_at_yahoo.de>
Date: Thu, 26 Mar 2009 04:29:11 -0700 (PDT)
Message-ID: <2aed0ca1-ba5c-46f1-a722-f7a63bf065f3_at_b16g2000yqb.googlegroups.com>

On 26 Mrz., 09:13, vipin.sin..._at_gmail.com wrote:
> Hi Gurus
>
> I am having a problem of slowness in deleting the data from a global
> temporary table.
>
> The scenario is like
>
> I have a global temporary table which is populates in a procedure. The
> no of rows populated are approximately 0.5 million and its going to
> increase in future. The population of the table is done using the
> query
>
> Insert into TempTable Select xyz from permanenttable where xyz = 'abc'
>
> Now real problem comes when i want to empty the temporary table using
> command
> Delete from TempTable; in the procedure.
>
> It takes about 50-60 seconds to execute.
>
> I have tried with truncate also which is taking less than 1 sec. But I
> can't use truncate as it is auto commit statement.
>
> Any comments on this?

The reason why truncate is fast is that it doesn't bother with rollback. So, do you really need to be able to rollback your transaction? Received on Thu Mar 26 2009 - 06:29:11 CDT