Oracle's partitioned indexes and global indexes

A partitioned index is a separate index on all of the zones, which is automatically maintained, which does not affect the use of other partitioned indexes of the index when a drop or truncate a partition, that is, the index does not fail, is easier to maintain, but has a slight impact on query performance.

To establish a partitioned index:

Create index IDX_TA_C2 on TA (C2) local (partition p1,partition p2,partition p3,partition);

or CREATE index idx_ta_c2 on TA (C2) local;

Also in the Create unique index idx_ta_c2 on TA (C2), the system reports ORA-14039 errors because the partition column of the TA table is C1 and does not support creating a PK primary key or a primary key column on a partitioned table that does not contain a partition column. It is not possible to create a unique constraint.

Partitioned indexes only support uniqueness within a partition and cannot support uniqueness on a table, so if you want to use a local index to make a table unique constraint, you must include the partitioning key column in the constraint

ALTER TABLE TEST ADD CONSTRAINT pk_test PRIMARY KEY (seq_id,partition_id) USING INDEX Local;

A global index is the creation of an index on the entire table, and it can create its own partitions, which can be different from the partitions of the partitioned table, that is, it is a separate index.

To establish a global index:

Create index IDX_TA_C3 on Ta (C3) global;

An index ALTER index IDX_XX rebuild can be created when a partition is drop or truncate, or through ALTER TABLE TABLE_NAME DROP PARTITION Partition_name update Global indexes, but it takes a long time to rebuild the index if the amount of data is large.

You can see whether the index is valid by querying the user_indexes, user_part_indexes, and user_ind_partitions views.

Create index IDX_TA_C3 on Ta (C3);

More Wonderful content: http://www.bianceng.cn/database/Oracle/

Or divide the global index into multiple zones (note that the partitions of the partitioned table are different):

Create index idx_ta_c4 on TA (c4) global partition by Range (C4) (partition ip1 values less than (10000), partition IP2 values Less than (20000), partition IP3 values less than (MaxValue));

Note the boot columns on the global index are consistent with the range, otherwise there will be a ORA-14038 error. If you cannot write this: Create index IDX_TA_C4 on TA (c1) Global partition by range (C4) partition ip1 values less than (10000), partition IP2 Values less than (20000), partition IP3 values less than (MaxValue));

Oracle automatically creates global indexes on primary keys

If you want to make a partition index unavailable, you can use the following script:

ALTER index IDX_TAB1 MODIFY PARTITION "IND Partition name" unusable

If you want to create a partitioned index on a column on a primary key, you cannot create it unless the primary key includes the partitioning key and the primary key is built on two or above.

Avoid using global indexes for easy maintenance when you delete a table's partitions frequently and the data is updated more frequently.

Export sub-partitions: Exp test1/test1 TABLES=TA:P1 file=part_1.dmp

Partitions used view:

User_tab_partitions,

User_part_indexes,

User_part_tables,

User_partial_drop_tabs,

User_part_indexes

User_part_key_columns

This article is from "Richard's notes-accumulate micro Cheng" blog, please be sure to keep this source http://zxf261.blog.51cto.com/701797/886336