Home » RDBMS Server » Server Administration » index performance (oracle,oracle 11g,OEL5)
index performance [message #621457] Wed, 13 August 2014 08:25 Go to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

Hi,

I want to create monthly partitions on product table on pdate column . Pdate column already contain index so my question is whats the best practice for performance perspective regarding type of index.Did existing index need to be recreated after partition creation for performance improvement.

Thanks
Re: index performance [message #621458 is a reply to message #621457] Wed, 13 August 2014 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
LOCAL or GLOBAL index & why that type?


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: index performance [message #621459 is a reply to message #621457] Wed, 13 August 2014 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Did existing index need to be recreated after partition creation for performance improvement.


As you cannot partitioned a non-partitioned table but have to recreate it, yes you have to recreate the indexes as well.

Note: It is better to locally partitioned your index(es).

Re: index performance [message #621462 is a reply to message #621459] Wed, 13 August 2014 08:55 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
You can partition an existing table using the DBMS_REDEFINITION package. An example of which is here.

HTH
-g
Re: index performance [message #621464 is a reply to message #621462] Wed, 13 August 2014 09:48 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No you neither partition it, you create a new table which is partitioned and copy the data in it.
And so you have (re)create the index(es) on the new table.

Previous Topic: wt is the diff b/w exclusive Lock And Dead Lock?
Next Topic: status of the v$session
Goto Forum:
  


Current Time: Thu Mar 28 16:37:40 CDT 2024