Delete from table taking time

From: <vipin.singla_at_gmail.com>
Date: Thu, 26 Mar 2009 01:13:00 -0700 (PDT)
Message-ID: <f4b7eac4-0347-4ba5-8af2-e1eaf9479c29_at_v1g2000prd.googlegroups.com>



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? Received on Thu Mar 26 2009 - 03:13:00 CDT

Original text of this message