Why create/destroy the index everytime? You can have a permanent index on the temporary table. No problem. Might be benificial. On Wed, Feb 25, 2009 at 6:26 PM, Amir Gheibi <gheibia@xxxxxxxxx> wrote: > This isn't a batch program.. this temp table ( ANOTHER_TEMP_TBL TT) is > filled inside a stored procedure with about 400,000 records and then used in > the same SP in multiple places.. this is one of them.. 400,000 record. Isn't > that too big to create an Index everytime and destroy it? > > > On Wed, Feb 25, 2009 at 7:22 PM, hrishy <hrishys@xxxxxxxxxxx> wrote: > >> Hi >> >> If this is a batch program. >> You can also create a index on that table ANOTHER_TEMP_TBL TT on TT.FKafter >> the dataload and drop it onse the insert into the main temporary table >> is done. >> >> regards >> Hrishy >> >> >> --- On Wed, 25/2/09, Amir Gheibi <gheibia@xxxxxxxxx> wrote: >> >> > From: Amir Gheibi <gheibia@xxxxxxxxx> >> > Subject: Re: Slow insert in GTT >> > To: hrishys@xxxxxxxxxxx >> > Cc: toon.koppelaars@xxxxxxxxxxx, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx> >> > Date: Wednesday, 25 February, 2009, 11:03 AM >> > The problem is that the Select statement itself is using >> > another temp table >> > which will not have any value until runtime. So in order to >> > know whether my >> > changes (either in the select command or the table >> > structure) can resolve >> > the issue or not, I have to make the change and then run >> > the application and >> > trace the SQL commands. Then analyze the outcome. Tedious! >> > So far I only >> > tried a couple of hints. I don't want to make any index >> > on the Temp Table >> > (the one that is used in the select statement) cause the >> > table is gonna have >> > 400,000 records in runtime. >> > I put together in one file (attached) a couple of snippets >> > from the Sql >> > Trace files ... first one is the original slow Insert and >> > the second one is >> > the one in which I used a hint.. (both trace files were >> > translated using >> > TKPROF).. >> > The Second one actually caused Oracle to break with the >> > "ORA-01013" error >> > (user requested cancel of current operation) which means >> > the connection >> > reached its timeout and Oracle stopped processing.. So the >> > hint was a bad >> > idea.. >> > >> > Any idea and help is appreciated... >> > >> > ~ Amir >> > >> > >> > >> > On Wed, Feb 25, 2009 at 4:35 PM, hrishy >> > <hrishys@xxxxxxxxxxx> wrote: >> > >> > > I am willing to bet that your select statment that >> > feeds into tho GTT is >> > > the culprit and you might try running the sql select >> > statement alone and see >> > > how slow or fast it is >> > > >> > > regards >> > > Hrishy >> > > >> > > >> > > --- On Wed, 25/2/09, Toon Koppelaars >> > <toon.koppelaars@xxxxxxxxxxx> wrote: >> > > >> > > > From: Toon Koppelaars >> > <toon.koppelaars@xxxxxxxxxxx> >> > > > Subject: Re: Slow insert in GTT >> > > > To: gheibia@xxxxxxxxx >> > > > Cc: "ORACLE-L" >> > <oracle-l@xxxxxxxxxxxxx> >> > > > Date: Wednesday, 25 February, 2009, 8:10 AM >> > > > I would suggest creating a SQL trace file. >> > > > And start your analysis from there. >> > > > On Wed, Feb 25, 2009 at 8:49 AM, Amir Gheibi >> > > > <gheibia@xxxxxxxxx> wrote: >> > > > >> > > > > 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 >> > > > > >> > > > >> > > > >> > > > >> > > > -- >> > > > Toon Koppelaars >> > > > RuleGen BV >> > > > +31-615907269 >> > > > Toon.Koppelaars@xxxxxxxxxxx >> > > > www.RuleGen.com >> > > > >> > > > (co)Author: "Applied Mathematics for >> > Database >> > > > Professionals" >> > > >> > > >> > > >> > > >> >> >> >> > -- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com (co)Author: "Applied Mathematics for Database Professionals"