Tables can be divided into range, hash, list, and table partitions, and the indexes on them are different from the indexes on the normal table, and Oracle has 2 categories of indexes on the partition table, namely
Local index and global index, the following is a summary of the characteristics and limitations of these 2 indexes.
Locally indexed Local Index
1.
Local index must be a partitioned index, the partitioning key is equal to the table partition key, the partition number is equal to the table partition number, in a word, the local index's partitioning mechanism and the table partition mechanism are same.
2. If the indexed column of a local index starts with a partitioning key, it is called a prefix local index.
3. If the locally indexed column does not start with a partitioning key or does not contain a partitioning key column, it is called a non-prefix index.
4.
Local indexes can only be attached to the partition table.
5. Both prefix and non-prefix indexes can support the elimination of index partitions, provided that the criteria for the query contain index partitioning keys.
6. Local indexes only support uniqueness within the partition and cannot support uniqueness on the table, so if you want to use local indexes to uniquely constrain a table, you must include the partitioning key column in the constraint.
7. The local partitioning index is for a single partition, each partitioned index points to only one table partition; Global indexes Otherwise, a partitioned index can point to n table partitions, while a table partition may point to n index partitions, make truncate or move,shrink to a partition in the partition table, May affect n Global index partitions, which is why local partitioning indexes are more available.
8.
Bitmap indexes can only be local partition indexes.
9.
Local indexes are applied to the data Warehouse environment more than once.
Global index
1.
The number of partition keys and partitions in the global index and the number of partition keys and partitions in the table may be different, the partitioning mechanism for tables and global indexes is different.
2.
Global indexes can be partitioned or not partitioned, and the global index must be a prefix index, that is, the index column of the global index must be the index partitioning key as its first columns.
3.
Global indexes can be attached to partitioned tables, or they can be attached to non partitioned tables.
4. The index entry for a global partitioning index may point to several partitions, so for a global partitioned index, you need to rebulid several partitions or even the entire index, even if only the data in one partition is truncated.
5.
Global indexes are more widely used in OLTP systems.
6. The global partitioning index is only scoped or hashed, and the hash partition is supported after 10g.
7.oracle9i when you do move or truncate the partition table, you can update the global partitioning index synchronously with the update global indexes statement, with a certain amount of resources in exchange for high availability.
8. The table uses a column as the partition, the index uses B to make the local partition index, if the Where condition uses B to inquire, then Oracle scans all tables and the index partition, the cost is higher than the partition, at this time may consider uses B to do the global Partitioning index.
Partitioned Index Dictionaries
dba_part_indexes Summary statistics for partitioned indexes to tell which partitioned indexes are on each table, and the type of partition index (LOCAL/GLOBAL)
dba_ind_partitionsPartition-level statistics for each partition index
dba_indexes/dba_part_indexesYou can get what non-partitioned indexes are on each table
Index rebuild
Alter index Idx_name rebuild partition index_partition_name [online nologging]
Each partition index needs to be done rebuild, can choose online (do not lock the table) when rebuilding, or do not generate the log when the index is nologging, speed up.
Alter index rebuild Idx_name [online nologging]
For non-partitioned indexes, only the entire index can be rebuilt
Partition Index Instance
Copy Code code as follows:
--1, Building 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, building global Partitioning 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, build local partition index
CREATE INDEX idx_pt_c2 on P_tab (C2) (PARTITION p1,partition p2,partition p3,partition P4);
--4, building the Global Partitioning index (on the same column as the partition Table partitioning rule)
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, partitioned index data dictionary view
SELECT * from User_ind_partitions;
SELECT * from User_part_indexes;
