OraFAQ Forum: Server Administration » How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables?

Home » RDBMS Server » Server Administration » How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables?

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

How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables? [message #259807]

Thu, 16 August 2007 10:22

doddiv
Messages: 11
Registered: May 2006

Junior Member

We have some main tables partitioned with created date field as date range with separate tablespaces and data files for each of these partitions. But the indexes on these tables are not partitioned and the indexes are gone to some other tablespaces. Of these indexes, some are for primary keys, some are for unique keys and some are for foreign keys. Of these foreign keys, some references with in the partition tables and some references to lookup tables. The lookup tables are not partitioned. Now we have four years data on all these tables. Is there any way to modify the existing indexes to partitioned indexes? When doing so can the existing partitions be used for the index partitioning? If it is possible what is the best way with minimum down time to perform this task. Can the foreign key indexes to lookup tables are also be possible for partitioning? I appreciate in advance for any help. With regards,

Report message to a moderator

Re: How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables? [message #259828 is a reply to message #259807]

Thu, 16 August 2007 11:19

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have to drop and recreate the indexes.

Regards
Michel

Report message to a moderator

Re: How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables? [message #259857 is a reply to message #259807]

Thu, 16 August 2007 12:56

doddiv
Messages: 11
Registered: May 2006

Junior Member

Thank you for the suggestion. I am not clear on some points. Is it better to create these indexes as local? What about foreign key indexes that are referencing to lookup tables?
When I drop the index on the existing primary key/foreign key then while recreating the indexes do I need to run ALTER commands again to specify the primary key/foreign key?

Report message to a moderator

Re: How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables? [message #259903 is a reply to message #259857]

Thu, 16 August 2007 16:41

andrew again
Messages: 2577
Registered: March 2000

Senior Member

As I recall local indexes are great for when you plan to swap partitions in an out, but they can't enforce uniqueness across the whole table. If your partitioning matches you UK structure then it may be a moot point. There is lots of info on asktom.oracle.com on the topic.

Report message to a moderator

Previous Topic:	ORA-16019.....................
Next Topic:	instance recovery....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 20:40:45 CDT 2024