OraFAQ Forum: Performance Tuning » How to calculate Index creation time

Home » RDBMS Server » Performance Tuning » How to calculate Index creation time (Oracle 11gr2 Linux)

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

How to calculate Index creation time [message #649781]

Wed, 06 April 2016 04:35

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

Can anyone please help me with approximate time to create an Index on table with 1 Million records?

[As I have totally 60 tables with huge data. (Most of tables with more than 10 Million re[cords)]

Thanks in advance.
Regards,
Hitesh

Report message to a moderator

Re: How to calculate Index creation time [message #649782 is a reply to message #649781]

Wed, 06 April 2016 04:45

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Depends on the hardware, on decent hardware it should take seconds.

Report message to a moderator

Re: How to calculate Index creation time [message #649783 is a reply to message #649781]

Wed, 06 April 2016 04:52

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

Senior Member
Account Moderator

10 millions records is not huge. I use this number for some tests on my laptop.

Report message to a moderator

Re: How to calculate Index creation time [message #649784 is a reply to message #649783]

Wed, 06 April 2016 04:53

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Thanks for quick reply, so can I approximately take 10 Millions will take around 5 minutes ?

Report message to a moderator

Re: How to calculate Index creation time [message #649786 is a reply to message #649784]

Wed, 06 April 2016 04:54

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

Senior Member
Account Moderator

No you can't.
You have to make a test on the target system.

Report message to a moderator

Re: How to calculate Index creation time [message #649787 is a reply to message #649786]

Wed, 06 April 2016 04:56

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Ok, As I only have to provide them approximate time. So need to know the average time it takes for 10 million records in general?

Report message to a moderator

Re: How to calculate Index creation time [message #649789 is a reply to message #649787]

Wed, 06 April 2016 04:59

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

Senior Member
Account Moderator

How much time it takes in general to go from New-York to Washington?

[Updated on: Wed, 06 April 2016 04:59]

Report message to a moderator

Re: How to calculate Index creation time [message #649790 is a reply to message #649789]

Wed, 06 April 2016 05:05

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

As I said above this depends on the hardware more than anything. If you've got fast hardware it'll be fast, if not it'll be slow. So there's no generic average time to be given.

Report message to a moderator

Re: How to calculate Index creation time [message #649792 is a reply to message #649790]

Wed, 06 April 2016 05:21

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Ok let me test on development, Thanks

Report message to a moderator

Re: How to calculate Index creation time [message #649795 is a reply to message #649792]

Wed, 06 April 2016 06:47

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

Senior Member
Account Moderator

Which gives you nothing if you have not the same hardware and concurrent workload than in production.

Report message to a moderator

Re: How to calculate Index creation time [message #649804 is a reply to message #649781]

Wed, 06 April 2016 10:44

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

The only way to know the answer is to actually run the commands in your environment, on your hardware, with your configuration, with your data.

Cheers,
Brian

Report message to a moderator

Re: How to calculate Index creation time [message #649856 is a reply to message #649781]

Sat, 09 April 2016 13:22

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Creation time depends on the creation method. If you use pdml with the appropriate degree, it should be fast. If not it is slower. You can use explain plan for create index command and dbms_xplan.display to estimate the size of the index (but not the creation time, s. above).

Report message to a moderator

Previous Topic:	stale (local) index statistics on partitioned table
Next Topic:	Taking more time to Drop interval Partitions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 06:23:28 CST 2024