OraFAQ Forum: SQL & PL/SQL » Creating Global vs Local Indexes for Partitioned Table

Home » SQL & PL/SQL » SQL & PL/SQL » Creating Global vs Local Indexes for Partitioned Table (ORACLE 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: Creating Global vs Local Indexes for Partitioned Table [message #689555 is a reply to message #689553]

Tue, 13 February 2024 08:49

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

For your question, I think you may be over-complicating it. For example:

orclz>
orclz> create table emp as select * from scott.emp;

Table created.

orclz> create index pk_emp on emp(empno);

Index created.

orclz> create index en_emp on emp(ename);

Index created.

orclz> alter table emp modify partition by list(deptno) automatic (partition p10 values(10))
  2  online
  3  update indexes
  4  (pk_emp global,
  5  en_emp local) ;

Table altered.

orclz>
orclz>

[Updated on: Tue, 13 February 2024 08:49]

Report message to a moderator

Re: Creating Global vs Local Indexes for Partitioned Table [message #689557 is a reply to message #689555]

Tue, 13 February 2024 23:30

oracle_search
Messages: 3
Registered: February 2024

Junior Member

Thanks for your response John watson, yeh I was confused with to many things, now I have got clarity.

Report message to a moderator

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?

Report message to a moderator

Re: Creating Global vs Local Indexes for Partitioned Table [message #689560 is a reply to message #689558]

Wed, 14 February 2024 02:35

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Please use [code] tags in your posts. I asked you to do this before. It is not polite to ignore the forum rules, to which you have already been directed..

As for your problem, PK_TEST_TEST is not the same as PK_CTA_TEST

Report message to a moderator

Previous Topic:	Querying table permissions to see which users have them
Next Topic:	Xmlattribute not returning tag when the value is null

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 13:57:23 CDT 2024