Home » SQL & PL/SQL » SQL & PL/SQL » Drop Partition makes Index unstable (10.2)
Drop Partition makes Index unstable [message #413959] Sun, 19 July 2009 15:49 Go to next message
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 #413960 is a reply to message #413959] Sun, 19 July 2009 15:53 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>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?
No
>2) When the partition is dropped entire index is unstable or just the index on the drop partition unstable?
Just local index
>3) If just drop partition index is unstable then how to rebuild it.
ALTER INDEX

Your answers are just a few clicks beyond the URL below.
http://tahiti.oracle.com
Let us know when you have found your answer.
Re: Drop Partition makes Index unstable [message #413971 is a reply to message #413960] Sun, 19 July 2009 23:05 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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
Previous Topic: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2)
Next Topic: BLOB file attachment
Goto Forum:
  


Current Time: Sun Dec 04 04:56:40 CST 2016

Total time taken to generate the page: 0.07058 seconds