Home » RDBMS Server » Performance Tuning » index creation (oracle 10g unix)
index creation [message #470354] Mon, 09 August 2010 11:24 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
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:
one index creation took 5 minute

Quote:
we create index and in index creation it took 25 minutes.


Regards
Michel

Report message to a moderator

Re: index creation [message #470379 is a reply to message #470378] Mon, 09 August 2010 12:35 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
There are 5 indexes and each took 5 minutes.

Report message to a moderator

Re: index creation [message #470380 is a reply to message #470379] Mon, 09 August 2010 12:39 Go to previous message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So investigate the ways Mahesh Rajendran and I gave you.

Regards
Michel

Report message to a moderator

Previous Topic: Table Partitioned with Primary key
Next Topic: Slow Update
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 05 05:18:07 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.