Re: Delete from table taking time

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Thu, 26 Mar 2009 09:04:54 -0500
Message-ID: <sWLyl.24743$yr3.22132_at_nlpi068.nbdc.sbc.com>

vipin.singla_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?
>
>
>

Why in the world do you need to read .5M rows into a GTT and then throw it away? Fix your process to be more selective in the data retrieved and reduce this overhead. This will only end badly for you in the future. Received on Thu Mar 26 2009 - 09:04:54 CDT