Re: Delete from table taking time

From: Tim X <>
Date: Sat, 28 Mar 2009 17:18:33 +1100
Message-ID: <> writes:

> 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?

Yes, why are you deleteing from a temporary global table?

Data in such a table is temporary anyway, so why delete it?

A temp global table is either transaction based or session based. It transaction based, the data in the table is lost after the transaction is committed. If session based, the data is lost once the session is ended.

Could your need to delete be an indication your TGT is incorrectly defined to be session based when it should be transaction based or vice versa?


tcross (at) rapttech dot com dot au
Received on Sat Mar 28 2009 - 01:18:33 CDT

Original text of this message