Oracle Partition (2) Partitioning index

Maybe a lot of beginners like me, initially thought that as long as the index created on the partition table is the partitioned index, in fact, whether the index partition and whether the table partition is not necessarily a relationship, table partitioning index can be partitioned or not partitioned, or even the table can be partitioned without partition index (but it is rarely defined), Therefore, the partitioning index is much more complicated than the score area table.

Partitioning index is mainly divided into local and global partitioning index, and local index is divided into prefix index and non prefix index, this paper mainly discusses their differences.

Local partition Index

The local partitioned index refers to the same partitioning key, partitioning, and partitioning of the base table, as shown in the following illustration:

The local partitioning index has the following basic characteristics:

1. The local index must be the partition index, the partitioning key is equal to the table partition key, the partition number is equal to the table partition said, in short, the local index's partitioning mechanism and the table partition mechanism is identical.

2. If the indexed column of the local index is the first column with the partitioning key, it is called the local prefix partitioning index.

3. If the indexed columns of the local index are not in the first column of the partitioning key, or if they do not contain the partitioning key columns, they are called local non-prefix partitioned indexes.

4. Both prefix and non-prefix can support index partition elimination, provided that the criteria for the query contain the index partitioning key.

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

6. The local partitioning index is for a single partition, each partitioned index points to only one table partition, but 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.

7. Bitmap indexes can only be local partition indexes.

8. Local indexes are more widely used in OLAP environments.

Local prefix Partitioning index

The local prefix partitioning index refers to the first column of the indexed column as the partitioning key, assuming that the partition table is defined as follows:

CREATE TABLE T  
(ID number, year number,  
  month number  
) partitioned by range (year)    
(partition p_2009 Values less than    
  partition p_2010 values less than (a),    
  partition p_2011 values less than (a),    
  p Artition p_2012 values less Than (2013)    
)

The following two indexes are prefixed by the partitioning index:

CREATE index i1 on T [year] local;  
CREATE index i2 on T (year,month) local;

Local non-prefix partitioned index

The local non-prefix index means that the indexed column does not contain the partitioning key, or the partition key is not in the first column of the indexed column, and the following two indexes are local non-prefix partitioned indexes:

CREATE INDEX i3 on (id,year) local;  
Create INDEX I4 on (month) local;