[Oracle] Local VS Global partition Index

[Oracle] Local VS Global partition index in Oracle, the index and table can also be partitioned. There are two types of partition indexes: Local and Global ). 1. The Local index is created using the LOCAL keyword. The partition boundary is the same as that of the table (that is, there is an index partition associated with each table partition ), the following is an example of a local partition index: [SQL] create table sales_par 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) as select * from sales; -- create a local partition index create salesindex _idx1 on sales_par (product, year) local; yes It can be seen that the statement for creating a local partition index is very simple and you do not need to specify the partition boundary because its partition boundary is the same as that of the table. The 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 starts with the partition key, it is called the prefix Local index. 3. If the column of the Local index does not start with the partition key or does not contain the partition key column, it is called a non-Prefix index. 4. Both prefix and non-Prefix indexes 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. Index partition elimination if the local partition index contains the partition key and the predicate condition in the SQL statement contains the partition key, the execution plan usually only needs to access one or a few index partitions, this feature is called Partition Elimination (Partition Elimination). Partition Elimination can effectively reduce scan data blocks and improve query performance, for example, [SQL] -- Query 1: select * from sales_par where product = 'cpu 'and year = 2011; -- Query 2: select * from sales_par where product = 'cpu'; in the preceding example, the predicate condition of query 1 contains the partition key. Therefore, you can use partitions to reduce the number of scanned partitions (in this example, you only need to scan the partition p_2011 ); the predicate condition of query 2 does not contain the partition key, so partitions cannot be eliminated. In addition to partition elimination, the local partition index also has the advantage of better table availability. After you DROP or MERGE a table partition, oracle automatically performs the same operation on the corresponding index partition without rebuild. That is, the maintenance operation can be performed on the independent partition. 2. The Global index is created using the GLOBAL keyword. The Partition boundaries of the index do not necessarily match the Partition boundaries of the table, and the partition keys of the table and the index can be different. The following is an example of a global partition index: [SQL] create index sales_idx2 on sales (year) global partition by range (year) (partition p_2010 values less than (2011 ), partition p_2012 values less than (2013); in the preceding example, although the table and index partition keys are the same, their partition boundaries are different, so they are global partition indexes. The following are the features of the global index. 1. The partition key and number of partitions of the global index may be different from the partition key and number of partitions of the table. The partition mechanism of the table and the global index is different. 2. The global index can be partitioned or non-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 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. 7. the table uses column a as a partition, and column B as a partial partition index. If Column B is used for query in the where condition, oracle scans all partitions of the table and index at a high cost, in this case, you can use B for global partition index. The following is a global index: