Home » RDBMS Server » Performance Tuning » How to calculate Index creation time (Oracle 11gr2 Linux)
How to calculate Index creation time [message #649781] Wed, 06 April 2016 04:35 Go to next message
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
Re: How to calculate Index creation time [message #649782 is a reply to message #649781] Wed, 06 April 2016 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends on the hardware, on decent hardware it should take seconds.
Re: How to calculate Index creation time [message #649783 is a reply to message #649781] Wed, 06 April 2016 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: How to calculate Index creation time [message #649784 is a reply to message #649783] Wed, 06 April 2016 04:53 Go to previous messageGo to next message
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 ?
Re: How to calculate Index creation time [message #649786 is a reply to message #649784] Wed, 06 April 2016 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: How to calculate Index creation time [message #649787 is a reply to message #649786] Wed, 06 April 2016 04:56 Go to previous messageGo to next message
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?
Re: How to calculate Index creation time [message #649789 is a reply to message #649787] Wed, 06 April 2016 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: How to calculate Index creation time [message #649792 is a reply to message #649790] Wed, 06 April 2016 05:21 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Ok let me test on development, Thanks
Re: How to calculate Index creation time [message #649795 is a reply to message #649792] Wed, 06 April 2016 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: How to calculate Index creation time [message #649804 is a reply to message #649781] Wed, 06 April 2016 10:44 Go to previous messageGo to next message
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
Re: How to calculate Index creation time [message #649856 is a reply to message #649781] Sat, 09 April 2016 13:22 Go to previous message
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).
Previous Topic: stale (local) index statistics on partitioned table
Next Topic: Taking more time to Drop interval Partitions
Goto Forum:
  


Current Time: Thu Mar 28 19:12:18 CDT 2024