In depth Explanation of oracle Partition Index

Tables can be partitioned according to range, hash and list. After the table is partitioned, the indexes on it are different from those on ordinary tables. oracle divides the indexes on partitioned tables into two categories, namely local indexes and global indexes. The characteristics and limitations of these two indexes are summarized below.

Local index local index
1. Local index 1 is definitely a partition index, the partition key is equivalent to the partition key of the table, and the number of partitions is equivalent to the number of partitions of the table. In one sentence, the partition mechanism of local index and the partition mechanism of the table are similar.
2. If the index column of a local index begins with a partitioning key, it is called a prefix local index.
3. A local index is called a non-prefix index if the column does not begin with a partitioning key or does not contain a partitioning key column.
4. Local indexes can only be attached to partitioned tables.
5. Both prefix and non-prefix indexes can support index partition elimination, provided that the query conditions include index partition keys.
6. Local indexes only support uniqueness within partitions, but cannot support uniqueness on tables. Therefore, if local indexes are used to constrain uniqueness on tables, partition key columns must be included in the constraints.
7. Local partition indexes are for a single partition, and each partition index only points to one table partition; Global index is not the case. One partition index can point to n table partitions, and at the same time, one table partition may also point to n index partitions. Doing truncate, move, shrink, etc. to a partition in a partition table may affect n global index partitions. Because of this, local partition index has higher availability.
8. Bitmap indexes can only be locally partitioned indexes.
9. Local indexing is mostly used in data warehouse environment.

Global Index global index
1. The partition key and number of partitions of the global index and the partition key and number of partitions of the table may be different, and the partition mechanism of the table and the global index is different.
2. The global index can be partitioned or unpartitioned. The global index must be prefix index, that is, the index column of the global index must take the index partition key as its first few columns.
3. Global indexes can be attached to partitioned tables; It can also be attached to a non-partitioned table.
4. The index entry of a global partitioned index may point to several partitions. Therefore, for a global partitioned index, even if the data in only one partition is truncated, several partitions or even the whole index of rebulid are required.
5. Global index is mostly used in oltp system.
6. Global partitioned indexes are only partitioned by scope or hash, and hash partitions are supported after 10g.
7. After oracle9i, when doing move or truncate for partition table, you can update the global partition index synchronously with update global indexes statement, and consume 1 resource in exchange for high availability.
8. Tables are partitioned by a columns, and indexes are partitioned by b. If b is used for query in where conditions, oracle will scan all partitions of tables and indexes, and the cost will be higher than partitions. At this time, b can be considered as global partition index.

Partition index dictionary
DBA_PART_INDEXES Summary statistics for partitioned indexes, which partitioned indexes are on each table, and the type of partitioned index (local/global)
Dba_ind_partitions Partition-level statistics for each partition index
Dba_indexes/dba_part_indexes You can get which non-partitioned indexes are on each table

Index reconstruction
Alter index idx_name rebuild partition index_partition_name [online nologging]
You need to do rebuild for each partition index, and you can choose online (no locking table) when rebuilding, or nologging does not generate logs when establishing indexes, so as to speed up the speed.
Alter index rebuild idx_name [online nologging]
For non-partitioned indexes, only the entire index can be rebuilt
Partitioned index instance

--1 Build partition table   
CREATE TABLE P_TAB(  
C1 INT,  
C2 VARCHAR2(16),  
C3 VARCHAR2(64),  
C4 INT ,  
CONSTRAINT PK_PT PRIMARY KEY (C1)  
)  
PARTITION BY RANGE(C1)(  
PARTITION P1 VALUES LESS THAN (10000000),  
PARTITION P2 VALUES LESS THAN (20000000),  
PARTITION P3 VALUES LESS THAN (30000000),  
PARTITION P4 VALUES LESS THAN (MAXVALUE)  
);  
--2 Build a global partition index   
CREATE INDEX IDX_PT_C4 ON P_TAB(C4) GLOBAL PARTITION BY RANGE(C4)  
(  
PARTITION IP1 VALUES LESS THAN(10000),  
PARTITION IP2 VALUES LESS THAN(20000),  
PARTITION IP3 VALUES LESS THAN(MAXVALUE)  
);  
--3 To build a local partition index   
CREATE INDEX IDX_PT_C2 ON P_TAB(C2) LOCAL (PARTITION P1,PARTITION P2,PARTITION P3,PARTITION P4);  
--4 Build a global partition index ( On the same column as the partition rule of the partitioned table )  
CREATE INDEX IDX_PT_C1  
ON P_TAB(C1)  
GLOBAL PARTITION BY RANGE (C1)  
(  
PARTITION IP01 VALUES LESS THAN (10000000),  
PARTITION IP02 VALUES LESS THAN (20000000),  
PARTITION IP03 VALUES LESS THAN (30000000),  
PARTITION IP04 VALUES LESS THAN (MAXVALUE)  
);  
--5 Partition index data dictionary view   
SELECT * FROM USER_IND_PARTITIONS;  
SELECT * FROM USER_PART_INDEXES;