Slow insert in GTT
Date: Wed, 25 Feb 2009 15:49:51 +0800
What could cause an Insert in a Global Temporary Table takes a lot of time? This is how I do the insert:
Insert into TT (...)
Select .. From ...
The insert is being done in a SP.
The Temp table is created with the "on commit preserve rows" option and that's because the table is used later in the SP.
The select statement might returns 10,000 records.
I have used a couple of hints in the select statement and the "Append" hint
in the Insert (even though I don't like to use append as it locks the table)
but still have the issue.
I also understand that the PARALLEL hint will be ignored for inserts on GTTs. So I can't use that either.
Any Idea, please?
One more thing, the select statement is built upon a join between another temp table and a couple of permanent tables.