Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Creating Global / Local Indexes for Partitioned Table

Ramesh_85Feb 12 2024 — edited Feb 12 2024

Hi All,

I have a doubt, we are going to migrate the non partitioned table to partition table.

Going to use List partition.

The table having one primary key, and 5 normal indexes

My understanding is to, post conversion, we need to drop primary key and all other indexes.

And need to create primary key as Global index and other normal indexes are Local index.

Is my understaning is right?

Also doubt in what type of index need to create? Global index or Global partitioned index or Local index.

Global Index:

CREATE INDEX IDX_ZIP_T1 ON CTA_PARTITIONTEST (SZZIP) GLOBAL;

or

CREATE INDEX item_idx
on CTA_PARTITIONTEST (SZZIP)
GLOBAL
(PARTITION city_idx1),
(PARTITION city_idx1,
(PARTITION city_idx1),
(PARTITION city_idx1),
(PARTITION city_idx1);

Logal index:

CREATE INDEX IDX_ZIP_T1 ON CTA_PARTITIONTEST (SZZIP) LOCAL;

or

CREATE INDEX item_idx
on CTA_PARTITIONTEST (SZZIP)
LOCAL
(PARTITION city_idx1),
(PARTITION city_idx1,
(PARTITION city_idx1),
(PARTITION city_idx1),
(PARTITION city_idx1);

Which of the above is correct for creating global and local indexes.

And which is best for moving existing table indexes to Global / Local..

Please suggest.

Comments
Post Details
Added on Feb 12 2024
1 comment
69 views