Home » RDBMS Server » Performance Tuning » Temp Size (10.2.0.3)  () 1 Vote
Temp Size [message #417085] Thu, 06 August 2009 00:24 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hello All,

I have one table which is 70 GB. Now i have to insert data into a new table from this table and from 12 other tables(much smaller, around 1 gb or even less) by left join. There is no cross join.
However the temp tablespace has grown too large(316 gb). Since temp tablespace has consumed so much of space, there is actually no space left for the new table to be created. Is there any way by which i could reduce the tempspace consumption, other than ctas. CTas is going to be the last option.

Thank You :
Re: Temp Size [message #417089 is a reply to message #417085] Thu, 06 August 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
CTas is going to be the last option.

Why? It is most often the best option.

Regards
Michel
Re: Temp Size [message #417092 is a reply to message #417089] Thu, 06 August 2009 00:51 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
CTas is going to be the last option because on doing CTAS sometimes the data types/size get changed( esp when the column has no value populated). Now this table on performing union with other table creates problem.
Although the data type can be cast to the actual data type while performing ctas, it is going to be a tedious task for there are so many other table with hundreads of columns.

So ctas has become the last option for me.
Re: Temp Size [message #417097 is a reply to message #417092] Thu, 06 August 2009 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
CTAS sometimes the data types/size get changed( esp when the column has no value populated).

You just have to define the type in the select part using CAST.

Quote:
this table on performing union with other table creates problem.

Do not use UNION but first CTAS and then several INSERT SELECT

Quote:
Although the data type can be cast to the actual data type while performing ctas, it is going to be a tedious task for there are so many other table with hundreads of columnS

Ah, the lazyness of developer!
You just have to define the type on the FIRST select as well you have to define the type on the CREATE TABLE.

Regards
Michel
Re: Temp Size [message #417138 is a reply to message #417097] Thu, 06 August 2009 04:52 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Thank You Michel.

It is pretty much sure that such a big temp size is due to 12 joins. If we replace those joins with inline views does it temp size?

Thank You
Re: Temp Size [message #417143 is a reply to message #417138] Thu, 06 August 2009 05:00 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Views are just the same thing as inline queries, so it does not change anything.

Regards
Michel
Previous Topic: A high value of Rollback per Transaction (97,74%)
Next Topic: Block Corruption
Goto Forum:
  


Current Time: Thu Dec 08 14:34:30 CST 2016

Total time taken to generate the page: 0.10782 seconds