OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » index performance (oracle,oracle 11g,OEL5)

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

index performance [message #621457]

Wed, 13 August 2014 08:25

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

Report message to a moderator

Re: index performance [message #621458 is a reply to message #621457]

Wed, 13 August 2014 08:30

BlackSwan
Messages: 26757
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/

Report message to a moderator

Re: index performance [message #621459 is a reply to message #621457]

Wed, 13 August 2014 08:32

Michel Cadot
Messages: 67640
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).

Report message to a moderator

Re: index performance [message #621462 is a reply to message #621459]

Wed, 13 August 2014 08:55

gazzag
Messages: 1091
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

Report message to a moderator

Re: index performance [message #621464 is a reply to message #621462]

Wed, 13 August 2014 09:48

Michel Cadot
Messages: 67640
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 18 16:48:32 CST 2021