Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
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


Member
**

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



Hello,

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
burleson
post Dec 28 2016, 12:01 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,439
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:

http://www.dba-oracle.com/t_oracle_sorting.htm

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):

http://www.dba-oracle.com/art_so_undocumen..._parameters.htm






--------------------
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: 27th June 2017 - 05:33 AM