Slow insert in GTT
From: Amir Gheibi <gheibia_at_gmail.com>
Date: Wed, 25 Feb 2009 15:49:51 +0800
Message-ID: <c906cd460902242349m41db10u71db56506d5cca19_at_mail.gmail.com>
Hi listers,
What could cause an Insert in a Global Temporary Table takes a lot of time? This is how I do the insert:
Date: Wed, 25 Feb 2009 15:49:51 +0800
Message-ID: <c906cd460902242349m41db10u71db56506d5cca19_at_mail.gmail.com>
Hi listers,
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.
Thanks.
Amir
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 25 2009 - 01:49:51 CST