How to understand the order of primary key local index, unique local index and partition index

This article shows you how to understand the order of primary key local index, unique local index and partition index. the content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

CREATE TABLE TEST1 (hid number,hid2 number,hchar1 char (50))

PARTITION BY RANGE (hid) INTERVAL (10)

(PARTITION p01 VALUES LESS THAN (5))

The primary key without USING INDEX LOCAL defaults to the global index

ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST PRIMARY KEY (hid)-- Global index

ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST PRIMARY KEY (hid) USING INDEX LOCAL

The primary key is a non-partitioned field

ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST PRIMARY KEY (hid2)-normal execution

ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST PRIMARY KEY (hid2) USING INDEX LOCAL-error ORA-14039: partitioned columns must form a subset of keyword columns of the UNIQUE index

ALTER TABLE TEST1 ADD CONSTRAINT PK_TEST PRIMARY KEY (hid2,hid) USING INDEX LOCAL-normal execution

Unique index is a non-partitioned field

Create unique index pk_ID on TEST1 (HID2) local-- error ORA-14039: partition columns must form a subset of keyword columns of the UNIQUE index

Create unique index pk_ID on TEST1 (HID2,HID) local-normal execution

Create unique index pk_ID on TEST1 (HID2)-normal execution

Alter table TEST1 add constraint pk_ID2 primary key (HID2) using index pk_ID-normal execution

Create index pk_ID99 on TEST1 (HID2) local-normal execution

Alter table TEST1 add constraint pk_ID99_2 primary key (HID2) using index pk_ID99-error ORA-14196: the specified index cannot be used to enforce constraints

If a non-partitioned field creates a primary key, a partitioned field must be added when creating the primary key local index

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (primary key field, partition field) USING INDEX LOCAL

The primary key to do the local index must contain the field of the partitioning key. This is related to how oracle ensures that the record is unique. If unique index key does not have partition_key, then each row of records inserted only ensures that it is unique in the partition it enters, and does not guarantee whether the record is unique on the whole table, which violates the meaning of the primary key. If you bind with the partition field, you don't need to scan all index partitions to verify that the record is unique on the entire table.

For range partitions, the values of non-partition fields in a single partition are not in order, and the values of partition fields in a single partition are not in order (for example, the first partition is 1-100, the order may be 7, 1, 6, and 6, and the second partition, 101-200, may be in the order of 199136103178, but relative to the two partitions, the range of values of the second partition and the first partition is sequential.

The partition index of the partition field is sequential because the start of the first data partition to the subsequent data partition is range-ordered, although the data within the individual data partition is not sequential

A non-partitioned field is just that the indexes within a partitioned index are sequential, which does not mean that the partitioned index is sequential.

The above is how to understand the order of primary key local index, unique local index and partition index. have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.