Re: Convert Partitioned Index to Regular Index
- From: Stefan Koehler <contact@xxxxxxxx>
- To: david.barbour1@xxxxxxxxx, Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Wed, 28 Dec 2016 10:06:19 +0100 (CET)
Hey David,
We are running ECC 6.0 with Kernel 7.01 SP11
The limitation is not SAP kernel related, but SAP basis package related as the
DDIC can not handle your current structure. It would be supported
starting with SAP basis 7.01 SP15.
I did send the correct DDL, have checked it again, and it does indicate
'PRIMARY KEY' in the table create, and we also have 3453 SAP-supplied tables
with primary keys.
I did not doubt that your table DDL includes a PRIMARY KEY constraint, but this
is very uncommon as SAP works with "NOT NULL" constraints and a
separate UNIQUE index for primary key - so no need for a PK constraint. You can
see that this definition is twofold based on your UNIQUE index name
("ARFCSSTATE0" vs. "ARFCSSTATE~0"). Are you sure that the PK constraint (and
possible custom defined foreign key constraints?) is not causing your
upgrade issue? The other point was your index - you said that you are using
global hash-partitioned indexes, but your index DDL did not include the
"PARTITION BY HASH" clause.
I like the simplicity of Martin's suggestion, and Brian Pardy from this list
has also responded privately with a method he used in a similar
situation.
Yes, the simplicity (of the 12.1 "feature simulation") is nice, but you have a
primary key constraint on it. Let me demo the issue (your PK constraint
was added after the unique index creation based on your index name - so my DDLs
look a little bit different than your DBMS_METADATA.GET_DDL to get the
same situation).
CREATE TABLE "ARFCSSTATE"
( "ARFCIPID" VARCHAR2(8) DEFAULT ' ',
"ARFCPID" VARCHAR2(4) DEFAULT ' ',
"ARFCTIME" VARCHAR2(8) DEFAULT ' ',
"ARFCTIDCNT" VARCHAR2(4) DEFAULT ' ',
"ARFCDEST" VARCHAR2(32) DEFAULT ' ',
"ARFCLUWCNT" VARCHAR2(8) DEFAULT ' ',
"ARFCSTATE" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,
"ARFCFNAM" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRETURN" VARCHAR2(1) DEFAULT ' ' NOT NULL ENABLE,
"ARFCUZEIT" VARCHAR2(6) DEFAULT '000000' NOT NULL ENABLE,
"ARFCDATUM" VARCHAR2(8) DEFAULT '00000000' NOT NULL ENABLE,
"ARFCUSER" VARCHAR2(12) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRETRYS" VARCHAR2(4) DEFAULT '0000' NOT NULL ENABLE,
"ARFCTCODE" VARCHAR2(20) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRHOST" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,
"ARFCMSG" VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRESERV" VARCHAR2(255) DEFAULT ' ' NOT NULL ENABLE,
"HASH" RAW(40));
CREATE UNIQUE INDEX "ARFCSSTATE~0" ON "ARFCSSTATE" ("ARFCIPID", "ARFCPID",
"ARFCTIME", "ARFCTIDCNT", "ARFCDEST", "ARFCLUWCNT") COMPRESS 2;
ALTER TABLE ARFCSSTATE ADD CONSTRAINT ARFCSSTATE0 PRIMARY KEY ("ARFCIPID",
"ARFCPID", "ARFCTIME", "ARFCTIDCNT", "ARFCDEST", "ARFCLUWCNT");
CREATE UNIQUE INDEX "ARFCSSTATE~0T" ON "ARFCSSTATE" ("ARFCIPID", "ARFCPID",
"ARFCTIME", "ARFCTIDCNT", "ARFCDEST", "ARFCLUWCNT",1) COMPRESS 2;
DROP INDEX "ARFCSSTATE~0";
ORA-02429: cannot drop index used for enforcement of unique/primary key
ALTER TABLE ARFCSSTATE MODIFY PRIMARY KEY USING INDEX "ARFCSSTATE~0T";
ORA-14196: Specified index cannot be used to enforce the constraint.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage:
http://www.soocs.de
Twitter: @OracleSK
David Barbour <david.barbour1@xxxxxxxxx> hat am 27. Dezember 2016 um 19:12
geschrieben:
We are running ECC 6.0 with Kernel 7.01 SP11. Several years back we
converted this database to RAC and ran into some index-related performance
problems. One of the options recommended by SAP was to convert specific
indexes to global hash partitioned, which wasn't supported using BRTools
with our then (and current) Service Pack level. So we did it directly in the
database, which solved our problem, but as you have correctly deduced,
this has created an issue for the upgrade.
I did send the correct DDL, have checked it again, and it does indicate
'PRIMARY KEY' in the table create, and we also have 3453 SAP-supplied
tables with primary keys.
I like the simplicity of Martin's suggestion, and Brian Pardy from this list
has also responded privately with a method he used in a similar
situation. I think both have direct application to the current situation.
Thank you all.
On Tue, Dec 27, 2016 at 9:20 AM, Martin Berger <martin.a.berger@xxxxxxxxx
mailto:martin.a.berger@xxxxxxxxx ;> wrote:
> > If you want to simulate the 12.1 feature with different index on
the same set of columns, I suggest to create a intermediate index with
> > the same columns followed by a "1" (or any other small constant).
This index should be as useful as the others, then the original index
can be dropped and created according to your needs.
When everything runs smooth the intermediate index can be removed.
the space usage should be similar to dbms_redefinition.
hth
Martin
--
//www.freelists.org/webpage/oracle-l
Other related posts:
