| disabling the indexes at partition level (merged) [message #670295] |
Mon, 25 June 2018 03:10  |
 |
saipradyumn
Messages: 419
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
|
|
|
|
|
|
|
|
| disabling the indexes at partition level [message #670298 is a reply to message #670295] |
Mon, 25 June 2018 04:11  |
|
saipradyumn
Messages: 419
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
