Re: Convert Partitioned Index to Regular Index

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> 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
>
> 2016-12-26 16:03 GMT+01:00 Stefan Koehler <contact_at_soocs.de>:
>
>> Hey David,
>> i am a little bit confused by the table and index definition.
>>
>> 1) "CONSTRAINT "ARFCSSTATE0" PRIMARY KEY ("ARFCIPID", "ARFCPID",
>> "ARFCTIME", "ARFCTIDCNT", "ARFCDEST", "ARFCLUWCNT")" - SAP does not work
>> with PK
>> constraints. SAP works with "NOT NULL" constraints plus unique indexes.
>>
>> 2) I can see no HASH clause for your index. Are you sure that you have
>> posted the right DDLs?
>>
>> What is your SAP release? SAPnote #742243 describes the necessary SAP
>> basis packages to create partitioned indexes independently of the table -
>> maybe
>> this is your issue during upgrade?
>>
>>
>> > Is dbms_redefinition the way to go with this? Or is there
>> another/different/better approach?
>>
>> Yes, as you are still running on 11.2. Create a DDL file with help of
>> BR*Tools, modify it accordingly and then use it as template for brspace. If
>> you
>> would be already on 12.1, you could create the non-partitioned index in
>> parallel existence of the global hash-partitioned one and just switch by
>> setting the visibility - but this is not possible with your current
>> Oracle release.
>>
>> 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 26. Dezember 2016 um
>> 14:50 geschrieben:
>> >
>> > Good Morning. Also Merry Christmas and Happy New Year for those to
>> whom it may apply.
>> >
>> > Running Oracle EE 11.2.0.3 on RHEL 6.8
>> >
>> > We are upgrading our SAP system and there are several tables that are
>> not partitioned but do have one or more global hash-partitioned indexes.
>> >
>> > The upgrade process does some work during an 'uptime' phase, but it's
>> stopping when it reaches these indexes. In order to proceed with the
>> > upgrade, we need to convert them back to regular indexes. Because of
>> the activity on these indexes, we need to keep the table/indexes available.
>> >
>> > Is dbms_redefinition the way to go with this? Or is there
>> another/different/better approach?
>> >
>> > Here are the current DDL statements for one of the tables involved
>> with the associated primary key separately described. The index has 6
>> > partitions.
>>
>>

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