Drop Partition makes Index unstable [message #413959] |
Sun, 19 July 2009 15:49  |
rajesshh
Messages: 2 Registered: July 2009
|
Junior Member |
|
|
Hi,
I have Partition Table with local index.
When I drop a partition the index goes unstable.
1) Is there any way to drop the partition without making the index unstable?
2) When the partition is dropped entire index is unstable or just the index on the drop partition unstable?
3) If just drop partition index is unstable then how to rebuild it.
Thanks & Regards,
Rajesh
|
|
|
|
Re: Drop Partition makes Index unstable [message #413971 is a reply to message #413960] |
Sun, 19 July 2009 23:05   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
For locally partitioned indexes, try:
ALTER TABLE tname
DROP PARTITION pname
UPDATE INDEXES
For global / globally partitioned indexes, try:
ALTER TABLE tname
DROP PARTITION pname
UPDATE GLOBAL INDEXES
Ross Leishman
|
|
|
Re: Drop Partition makes Index unstable [message #414154 is a reply to message #413971] |
Mon, 20 July 2009 10:03  |
rajesshh
Messages: 2 Registered: July 2009
|
Junior Member |
|
|
Hi,
Here is the Table Structure
DLVRY_ID NUMBER(18) NOT NULL,
LOAD_TS DATE,
ELCTR_ADR_TYPE_CD CHAR(3 BYTE),
ELCTR_MSG_FRMT_DESC VARCHAR2(20 BYTE),
CHNL_TYPE_CD VARCHAR2(4 BYTE)
Table is partitioned on LOAD_TS field and DLVRY_ID is the PK.
So When the partition is drop, Primary key index is going unstable.
I know there is a query which will work only if the partition was created on DLVRY_ID and not load_ts
ALTER INDEX MSG_DLVRY_PK REBUILD PARTITION <partition name>;
Is there any way to rebuild the drop partition index for this?
Thanks,
Rajesh
|
|
|