Oracle 10g - Creating Index faster

Hi,

Im running Oracle 10g on Solaris 10 and I was wondering if there is a way to create an index faster than it is right now....

Im running this script but its taking too long:

CREATE UNIQUE INDEX IX_AK1_Dp_SKey ON Dp_SKey
(DpSC_Id ASC, DpSA_Id ASC, DpSD_Id ASC, DpSK_Index ASC, DpSK_Type ASC, DpSK_Vers
PCTFREE 10
INITRANS 4
MAXTRANS 255
TABLESPACE D_INDEX02
PARALLEL;

Note that Im using the PARALLEL option.. maybe this will make it go faster or it will make no difference?

alter session force parallel ddl;

====

The table to be indexed -  Dp_SKey - have 686176534 rows...

Its taking 90 min at the moment

Tks,
Joao
joaotellesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
Fast or slow index creation depends on many other things right not just the parallel.

for example, sort_area_size, PARALLEL (DEGREE <integer>), block size, logging or no logging etc and so on....

just read through these to get a better understanding.

http://mehrajdba.wordpress.com/2009/01/30/how-to-make-an-index-creation-faster/
http://psoug.org/snippet/INDEXES-Parallel-Index_807.htm
http://www.akadia.com/services/ora_parallel_processing.html
http://hemantoracledba.blogspot.sg/2011/08/create-index-parallel.html

Have you tried the index creation in the DEV or UAT ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Another option is to not create a unique index.  You will save yourself the uniqueness check.  Then after the index is created,  you create a unique constraint that only has to do a scan of the index to determine if it is unique or not.

CREATE INDEX IX_AK1_Dp_SKey ON Dp_SKey
(DpSC_Id ASC, DpSA_Id ASC, DpSD_Id ASC, DpSK_Index ASC, DpSK_Type ASC, DpSK_Vers)
PCTFREE 10
INITRANS 4
MAXTRANS 255
TABLESPACE D_INDEX02
PARALLEL;
alter table dp_skey add constraint UQ_AK1_Dp_SKey unique(DpSC_Id ASC, DpSA_Id ASC, DpSD_Id ASC, DpSK_Index ASC, DpSK_Type ASC, DpSK_Vers);

This separates the constraint from the index.  It can also ease maintenance nightmares where you can drop the constraint for maintenance, but leave the index.  That way the constraint can be replaced without having to rebuild the index.  Oracle recommended this approach in the past, however they didn't document it well.  They also recommend the same thing with primary keys.  You can enforce a primary key constraint using a non-unique index as well.  It comes with the same ease of maintenance and also it can use a combined index to enforce the primary key.  If your primary key is f1 and you have an index on (f1,f3), then that combined index can be used to enforce the primary key and you don't have 2 indexes to maintain.
0
joaotellesAuthor Commented:
tks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.