Home » RDBMS Server » Server Administration » How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables?
How to modify the existing un-partitioned indexes to partitioned indexes on partitioned tables? [message #259807] Thu, 16 August 2007 10:22 Go to next message
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,

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to drop and recreate the indexes.

Regards
Michel
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 Go to previous messageGo to next message
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?

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 Go to previous message
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.
Previous Topic: ORA-16019.....................
Next Topic: instance recovery....
Goto Forum:
  


Current Time: Sun Dec 04 04:20:25 CST 2016

Total time taken to generate the page: 0.21525 seconds