OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » index creation (oracle 10g unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

index creation [message #470354]

Mon, 09 August 2010 11:24

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

Hi,

is there any way to reduce the index creation time.

in my case one index creation took 5 minute and there are 5 indexes , so it took 25 minutes.

Report message to a moderator

Re: index creation [message #470355 is a reply to message #470354]

Mon, 09 August 2010 11:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>is there any way to reduce the index creation time.
doing it on an empty table will be faster

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: index creation [message #470361 is a reply to message #470354]

Mon, 09 August 2010 11:40

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You can try using parallel/nologging options.

Report message to a moderator

Re: index creation [message #470362 is a reply to message #470354]

Mon, 09 August 2010 11:40

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Create them in parallel: 5 indexes = 5 minutes (about) and you can even create each index in parallel (see SQL Reference for syntax).

Regards
Michel

[Updated on: Mon, 09 August 2010 11:41]

Report message to a moderator

Re: index creation [message #470374 is a reply to message #470362]

Mon, 09 August 2010 12:20

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

can not do in parallel, because index creation is goin on inside the procedure and first we populate the table with 13423455 records then we create index and in index creation it took 25 minutes.

Report message to a moderator

Re: index creation [message #470378 is a reply to message #470374]

Mon, 09 August 2010 12:33

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Search for "execute PL/SQL in parallel" you'll find many answers, here, on AskTom and on the web.

By the way, don't you see any inconsistency in:
Quote: