| Rebuild non-partitioned PK index to partitioned? [message #63424] |
Mon, 04 October 2004 09:41  |
Michael
Messages: 61
Registered: October 1999
|
Member |
|
|
Is it possible to rebuild a non-partitioned index to become partitioned? A previous dba created 3 partitioned tables and primary keys for them, but the indexes for the pks are not partitioned. I would like to rebuild them to be local partitioned indexes, but in this case I would rather not drop and recreate them, because there are foreign key constraints pointing to these pks (so I would have to drop the fks first then add them back later!)
If this is possible, what would the syntax look like? I have rebuilt normal indexes many times, but this would be a first for me.
Michael
|
|
|
|
|
|
| Re: Rebuild non-partitioned PK index to partitioned? [message #63433 is a reply to message #63424] |
Mon, 04 October 2004 13:40  |
Maurice
Messages: 8
Registered: February 2002
|
Junior Member |
|
|
As far as I know you may do it like this:
1) execute an "alter table tbl_name alter constraint FK_xyz disable validate" for each FK
2)drop the index
3)recreate local indexes
4)execute an "alter table ... enable validate" for each FK
FYI:
a local unique/pk indexe needs to have the partition column included in the index
|
|
|
|
| Re: Rebuild non-partitioned PK index to partitioned? [message #63452 is a reply to message #63425] |
Wed, 06 October 2004 13:09 |
TANMAIYEE
Messages: 5
Registered: September 2004
|
Junior Member |
|
|
|
This is common for Range and Composite partitining (range/hash) with tables having sequence driven PKs and the partining needs to be done for historical "date" (or some other - or on some combination of columns) columns. Since the partition-key is not same as your pk.. one need to go for global index of Pk.. There was work around to bring the partition key into PK, but that will not help always as the FK to this table may not have all columns
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
