[Oracle] exploring partition 2-partition Index

[Oracle] explore partition 2-partition index [Oracle] explore one of the partitions-partition pruning http://www.bkjia.com/database/201308/237367.html may be the same for many beginners and I thought at first as long as the index created on the partition table is a partition index, in fact, there is no inevitable relationship between whether the index is partitioned and whether the table is partitioned. The table partition index can be partitioned or not, even a table can be partitioned without a partition index (but rarely defined as this). Therefore, the partition index is much more complex than the partition table. Partition indexes are mainly divided into local and global partition indexes. Local indexes are divided into prefix indexes and non-Prefix indexes. This article mainly discusses their differences. Local partition index the local partition index is the same as the partition key, partition method, and partition method of the base table of the index, as shown in: Local partition index has the following basic features: 1. the local index must be a partition index. The partition key is equivalent to the table's partition key, and the number of partitions is equivalent to the table's partition. In short, the partition mechanism of the Local index is the same as that of the table. 2. If the index column of the Local index takes the partition key as the first column, it is called the local prefix partition index. 3. If the index column of the Local index is not the first column with the partition key or does not contain the partition key column, it is called a local Non-Prefix partition index. 4. Both prefix and non-Prefix support the elimination of index partitions, provided that the query conditions contain the index partition key. 5. The local index only supports the uniqueness in the partition and does not support the uniqueness of the table. Therefore, if you want to use the local index to restrict the uniqueness of the table, the constraint must include the partition key column. 6. the local partition index is for a single partition. Each partition index points to only one table partition, while the global index does not. One partition index can point to n table partitions. At the same time, one table partition, it may also point to n INDEX partitions. truncate, move, and shrink operations on a partition in the Partition Table may affect n global index partitions, the local partition index has higher availability. 7. Bitmap indexes can only be local partition indexes. 8. Local indexes are mostly used in OLAP environments. Local prefix partition index local prefix partition index refers to the partition key that is the first column of the index column. Assume that the partition table is defined as follows:

[sql] create table t  ( id number,    year number,    month number  ) partitioned by range (year)    ( partition p_2009 values less than (2010)      partition p_2010 values less than (2011),      partition p_2011 values less than (2012),      partition p_2012 values less than (2013)    )    

 

The following two indexes are prefix partition indexes:

[sql] create index i1 on t(year) local;  create index i2 on t(year,month) local;  

 

Local non-Prefix partition index local non-Prefix index means that the index column does not contain the partition key, or the partition key is not in the first column of the index column. The following two indexes are local non-Prefix partition indexes:

[sql] create index i3 on (id,year) local;  create index i4 on (month) local;  

 

The global partition index is opposite to the local index, that is, the index partition is not exactly the same as the table partition. It has the following features: 1. the partition key, number of partitions, and number of partitions of the global index may be different. The partition mechanism of the table and the global index is different. 2. The global index can be partitioned or not partitioned. The global index must be a prefix index, that is, the index column of the global index must use the index partition key as the first few. 3. The index entries of the global partition index may point to several partitions. Therefore, for a global partition index, even if only data in one partition is truncated, multiple partitions or even the entire index must be rebulid. 4. Global partition indexes are mostly used in OLTP systems. 5. The global partition index is only supported by the range or hash partition. The hash partition is 10 Gb later. 6. When moving or truncate the partition table after oracle9i, you can use the update global indexes statement to synchronously update the global partition index, which consumes a certain amount of resources in exchange for high availability. The following example defines a non-partitioned global index on a partitioned table:

[sql] create index i5 on t(id);  create index i6 on t(id) global;  

 

The effects of the above two statements are the same. That is to say, a non-partitioned global index is actually a common index. The following example defines a global partition index, its partition key and table are different:

[sql] create index i7 on t (month)  global partition by range (month)  ( partition p_before_july values less than (7),    partition p_after_july values less than (13)  );  

 

It is as follows: Local partition VS global index from the above we can know that because the local partition index is the same as the table partition, it has good maintainability, such as deleting a partition in a table, the local partition index is automatically maintained without human intervention. However, its index access performance decreases because it divides the index into multiple partitions, as a result, each index access needs to traverse all partitions (of course, if the global partition index is also partitioned, the performance will also deteriorate ). Therefore, it is suitable for OLAP systems. Global indexes are the opposite, with poor maintainability. For example, if you delete a partition in a table, the index is invalid and requires rebuild. However, the index access performance is better than that of the local partition index. Therefore, it is suitable for OLTP systems.