Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

Oracle training Weblogic Book
Easy Oracle Jumpstart
Oracle training & performance tuning books
Burleson Consulting Remote DB Administration
Reply to this topicStart new topic
> Create index on a huge table, How to create an index on a huge table without tablespace problems
Jayme Jeffman
post Dec 28 2016, 08:45 AM
Post #1


Group: Members
Posts: 10
Joined: 2-September 15
From: Porto Alegre, Brazil
Member No.: 50,831


I have a table with millions of records.

How to create an index without having temp tablespace problems ?

Thank you very much.

Kind regards.

Jayme Jeffman

Database Programmer
Sul Engenharia e Sistemas Ltda
Go to the top of the page
+Quote Post
post Dec 28 2016, 12:01 PM
Post #2

Advanced Member

Group: Members
Posts: 13,566
Joined: 26-January 04
Member No.: 13

Hi Jayme,

>> How to create an index without having temp tablespace problems ?

Oracle must sort the rows for the index, and this must be done in TEMP or PGA.

You can set a large parallel degree, making the operation have more PGA (one for each process).

Open up a 100 gig PGA and sort it in RAM, 300x faster:


Parallel processing up to 16 threads on a 4 CPU server. As discussed in MOSC thread 460157.996, a supported way to increase the maximum PGA memory per single SQL query is to increase the degree of parallelism.

You can also set a super-large PGA (during slow processing times):


Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:


Lo-Fi Version Time is now: 18th October 2017 - 12:51 AM