Home » SQL & PL/SQL » SQL & PL/SQL » Creating Global vs Local Indexes for Partitioned Table (ORACLE 19c)
Creating Global vs Local Indexes for Partitioned Table [message #689553] |
Tue, 13 February 2024 06:30  |
 |
oracle_search
Messages: 3 Registered: February 2024
|
Junior Member |
|
|
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, before migration we need to all constrains and indexes from table and
post conversion, we need to recreate primary key and all other indexes.
And primary key as Global index and other column level indexes are as Local index.
Is my understanding is correct?
Also I have doubt in creating global and local indexes, there were two types of index creation I got from internet, not sure which one i need to follow.
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);
Local 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 options are correct for creating global and local indexes.
And which one is best to moving existing constraints and indexes.
Please suggest.
|
|
|
|
|
Re: Creating Global vs Local Indexes for Partitioned Table [message #689558 is a reply to message #689555] |
Wed, 14 February 2024 00:30   |
 |
oracle_search
Messages: 3 Registered: February 2024
|
Junior Member |
|
|
I am getting below error, while execute the partition, with index.
ALTER TABLE CTA_TP MODIFY
PARTITION BY LIST (SZSUBPARTITIONCODE)
(PARTITION P0_TEST VALUES ('0'),
PARTITION P1_TEST VALUES ('1'),
PARTITION P2_TEST VALUES ('2'),
PARTITION P3_TEST VALUES ('3'),
PARTITION P4_TEST VALUES ('4'),
PARTITION P5_TEST VALUES ('5'),
PARTITION P6_TEST VALUES ('6'),
PARTITION P7_TEST VALUES ('7'),
PARTITION P8_TEST VALUES ('8'),
PARTITION P9_TEST VALUES ('9'),
PARTITION P10_TEST VALUES ('10'),
PARTITION P11_TEST VALUES ('11'),
PARTITION P12_TEST VALUES ('12'),
PARTITION P13_TEST VALUES ('13'),
PARTITION P14_TEST VALUES ('14'),
PARTITION P15_TEST VALUES ('15'),
PARTITION P16_TEST VALUES ('16'),
PARTITION P17_TEST VALUES ('17'),
PARTITION P18_TEST VALUES ('18'),
PARTITION P19_TEST VALUES ('19'),
PARTITION P_DEFAULT_TEST VALUES (DEFAULT)
)
ONLINE
UPDATE INDEXES
(
PK_TEST_TEST GLOBAL,
UK_SZLEGACYAGREEMENTNO_TEST LOCAL,
IDX_TEST_CASE_TEST LOCAL,
IDX_TEST_CUST_TEST LOCAL,
TCTA_PERF_IDX04_TEST LOCAL)
/
ORA-01418: specified index does not exist
But all the indexes are available in user_indexes table.
PK_CTA_TEST
UK_SZLEGACYAGREEMENTNO_TEST
IDX_CTA_CASE_TEST
IDX_CTA_CUST_TEST
TCTA_PERF_IDX04_TEST
Why i am getting this error, any suggestions?
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 02:13:03 CST 2025
|