Re: Convert Partitioned Index to Regular Index

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: _at_OracleSK

> David Barbour <david.barbour1_at_gmail.com> 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_at_gmail.com mailto:martin.a.berger_at_gmail.com > 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
 

--
http://www.freelists.org/webpage/oracle-l