Home » RDBMS Server » Server Administration » disabling the indexes at partition level (merged) (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
disabling the indexes at partition level (merged) [message #670295] Mon, 25 June 2018 03:10 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

We have LIST Partitioned table on the branch column.Valid values for this column are HYD, DELHI.
We have LOCAL Partitioned Indexes on this table .

There are some bulk insertion into this table,through the one procedure.
In order to improve the performance we want to disable the index &
after inserting the bulk records again go for the enabling the indexes.

But here the problem is the
Indexes disabling is at the table level& Indexes enabling is at Partition level .

Because of this issue, unnecessary other branches partitioned indexes are also disabling.




--Partitioned table Creation
DROP TABLE TRXN_ABC_WRK;
CREATE TABLE "TRXN_ABC_WRK"
  (
    "BRANCH"      VARCHAR2(5 CHAR),
    "REF_NUM"     VARCHAR2(20 CHAR),
    "VERSION"     NUMBER,
    "REFERENCE"   VARCHAR2(20 CHAR),
    "MODULE_CODE" VARCHAR2(2 CHAR),
    "EVENTCODE"   VARCHAR2(4 CHAR)
  )
  PARTITION BY LIST ( "BRANCH")(PARTITION "HYD" VALUES('HYD'));

--Partition  Index Creation
CREATE INDEX "IDX_TRXN_ABC_WRK" ON "QFXMAIN"."TRXN_ABC_WRK"
  ("BRANCH","REF_NUM","VERSION","EVENTCODE")  LOCAL;

--Adding  new partition  to the table .
ALTER TABLE TRXN_ABC_WRK  ADD PARTITION  DELHI VALUES  ('DELHI'); 

--Disabling the indexes
ALTER INDEX  IDX_TRXN_ABC_WRK  UNUSABLE ;  

--All branches are in DISABLE State
SELECT  UIP.STATUS , UIP.*
FROM SYS.USER_IND_PARTITIONS  UIP WHERE  INDEX_NAME='IDX_TRXN_ABC_WRK';

--Enabling at  Partition Level
ALTER INDEX  IDX_TRXN_ABC_WRK  REBUILD  PARTITION  HYD;
ALTER INDEX  IDX_TRXN_ABC_WRK  REBUILD  PARTITION  DELHI;




If I try to disable at Partition level the following error message is coming


ALTER INDEX  IDX_TRXN_ABC_WRK  unusable  PARTITION  HYD;

Error Message  :


4048. 00000 -  "a partition maintenance operation may not be combined with other operations"
*Cause:    ALTER TABLE or ALTER INDEX statement attempted to combine
           a partition maintenance operation (e.g. MOVE PARTITION) with some
           other operation (e.g. ADD PARTITION or PCTFREE which is illegal
*Action:   Ensure that a partition maintenance operation is the sole
           operation specified in ALTER TABLE or ALTER INDEX statement;
           operations other than those dealing with partitions,
           default attributes of partitioned tables/indices or
           specifying that a table be renamed (ALTER TABLE RENAME) may be
           combined at will


Is there any way to disable the INDEXES at the PARTITION level?

Thanks
SaiPradyumn







Re: disabling the indexes at partition level [message #670296 is a reply to message #670295] Mon, 25 June 2018 03:33 Go to previous messageGo to next message
gazzag
Messages: 1091
Registered: November 2010
Location: Bedwas, UK
Senior Member
You say "improve performance" but the indexes have to be rebuilt at the end. You still pay the price, effectively.
Re: disabling the indexes at partition level [message #670297 is a reply to message #670295] Mon, 25 June 2018 03:50 Go to previous messageGo to next message
John Watson
Messages: 8458
Registered: January 2010
Location: Global Village
Senior Member
alter index ... modify partition ... unusable;
disabling the indexes at partition level [message #670298 is a reply to message #670295] Mon, 25 June 2018 04:11 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

We have LIST Partitioned table on the branch column.Valid values for this column are HYD, DELHI.
We have LOCAL Partitioned Indexes on this table .

There are some bulk insertion into this table,through the one procedure.
In order to improve the performance we want to disable the index &
after inserting the bulk records again go for the enabling the indexes.

But here the problem is the
Indexes disabling is at the table level& Indexes enabling is at Partition level .

Because of this issue, unnecessary other branches partitioned indexes are also disabling.



--Partitioned table Creation
DROP TABLE TRXN_ABC_WRK;
CREATE TABLE "TRXN_ABC_WRK"
  (
    "BRANCH"      VARCHAR2(5 CHAR),
    "REF_NUM"     VARCHAR2(20 CHAR),
    "VERSION"     NUMBER,
    "REFERENCE"   VARCHAR2(20 CHAR),
    "MODULE_CODE" VARCHAR2(2 CHAR),
    "EVENTCODE"   VARCHAR2(4 CHAR)
  )
  PARTITION BY LIST ( "BRANCH")(PARTITION "HYD" VALUES('HYD'));

--Partition  Index Creation
CREATE INDEX "IDX_TRXN_ABC_WRK" ON "QFXMAIN"."TRXN_ABC_WRK"
  ("BRANCH","REF_NUM","VERSION","EVENTCODE")  LOCAL;

--Adding  new partition  to the table .
ALTER TABLE TRXN_ABC_WRK  ADD PARTITION  DELHI VALUES  ('DELHI'); 

--Disabling the indexes
ALTER INDEX  IDX_TRXN_ABC_WRK  UNUSABLE ;  

--All branches are in DISABLE State
SELECT  UIP.STATUS , UIP.*
FROM SYS.USER_IND_PARTITIONS  UIP WHERE  INDEX_NAME='IDX_TRXN_ABC_WRK';

--Enabling at  Partition Level
ALTER INDEX  IDX_TRXN_ABC_WRK  REBUILD  PARTITION  HYD;
ALTER INDEX  IDX_TRXN_ABC_WRK  REBUILD  PARTITION  DELHI;



If I try to disable the indexes at Partition level the following error message is coming


ALTER INDEX  IDX_TRXN_ABC_WRK  unusable  PARTITION  HYD;

Error Message  :


4048. 00000 -  "a partition maintenance operation may not be combined with other operations"
*Cause:    ALTER TABLE or ALTER INDEX statement attempted to combine
           a partition maintenance operation (e.g. MOVE PARTITION) with some
           other operation (e.g. ADD PARTITION or PCTFREE which is illegal
*Action:   Ensure that a partition maintenance operation is the sole
           operation specified in ALTER TABLE or ALTER INDEX statement;
           operations other than those dealing with partitions,
           default attributes of partitioned tables/indices or
           specifying that a table be renamed (ALTER TABLE RENAME) may be
           combined at will

Is there any way to disable the INDEXES at the PARTITION level?

Thanks
SaiPradyumn





Re: disabling the indexes at partition level [message #670299 is a reply to message #670298] Mon, 25 June 2018 04:15 Go to previous messageGo to next message
John Watson
Messages: 8458
Registered: January 2010
Location: Global Village
Senior Member
THere is no need to double post. I've already shown you the correct syntax.
Re: disabling the indexes at partition level [message #670300 is a reply to message #670299] Mon, 25 June 2018 04:34 Go to previous message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi John Watson ,
Thank you very much for your quick response and appropriate solution.
Its worked fine .

sorry for double posting

Thanks
Sai Pradyumn
Previous Topic: ORA-29701: unable to connect to Cluster Synchronization Service
Next Topic: update machine.config
Goto Forum:
  


Current Time: Sun Jan 24 10:31:43 CST 2021