Home » SQL & PL/SQL » SQL & PL/SQL » Use of TEMP segment
Use of TEMP segment [message #203512] Wed, 15 November 2006 06:22 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
How we can minimise the use of temp segment in oracle??

Re: Use of TEMP segment [message #203515 is a reply to message #203512] Wed, 15 November 2006 06:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
write better sql?
Re: Use of TEMP segment [message #203516 is a reply to message #203512] Wed, 15 November 2006 06:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The temp segment is used for things like Sorts, Hash joins and index creation, so you could minimise this sort of activity.

Why do you feel you need to do this?
Re: Use of TEMP segment [message #203517 is a reply to message #203516] Wed, 15 November 2006 06:38 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
I need this as my code ,when executing giving error due to sortage of temp segment .
asically i want to know which operation should be avoided to minimise the use of TEMP segment??
Re: Use of TEMP segment [message #203519 is a reply to message #203517] Wed, 15 November 2006 06:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I feel that a better solution would be to increase the available Temp space.
Re: Use of TEMP segment [message #203525 is a reply to message #203519] Wed, 15 November 2006 07:01 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
I have already think about this .But it is not possible to increase the space of temp segment .
Any other sol~n ??
Re: Use of TEMP segment [message #203527 is a reply to message #203525] Wed, 15 November 2006 07:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
buy extra harddisks
Re: Use of TEMP segment [message #203532 is a reply to message #203527] Wed, 15 November 2006 07:17 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
No ,simply this not possible to bye a Harddisk.
I wan to know which operation should be avoided in oracle to minimise the use of temp segment??
Any pointer regrading the minimise the use of temp segment can help me.
Re: Use of TEMP segment [message #203538 is a reply to message #203532] Wed, 15 November 2006 07:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As we said before, write better code!.
>>which operation should be avoided
No hard rules here.
What are you using in your code?
You have not provided any useful information to startwith.
Post your code/version/plan/trace/something.
Else, this will be your wild goose chase.
Re: Use of TEMP segment [message #203618 is a reply to message #203538] Wed, 15 November 2006 19:25 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Things that will use TEMP space:
- ORDER BY
- DISTINCT/UNIQUE
- GROUP BY
- UNION
- MINUS
- INTERSECT
- Hash Joins
- Sort-Merge Joins
- IN sub-queries
- NOT IN subqueries that the CBO transforms into anti-joins
- EXISTS sub-queries that the CBO transforms into Hash or Sort-Merge semi-joins
- Analytic functions
- OR clauses that the CBO transforms into UNIONs
- CREATE INDEX

Did I miss anything?

Ross Leishman
Previous Topic: joining huge tables
Next Topic: Query - Urgent (Merged)
Goto Forum:
  


Current Time: Wed Dec 07 20:02:46 CST 2016

Total time taken to generate the page: 0.08949 seconds