Introduction to Oracle local VS Global Partitioning index

In Oracle, indexes and tables can be partitioned as well. There are two types of partitioned indexes, local partitioned indexes (locals), and global partition indexes (globally).

1. Local Index

The local partitioning index is created with a local keyword whose partition boundaries are the same as the table (that is, there is an index partition associated with each table partition), and the following is an example of a native partitioning index:

CREATE TABLE Sales_par  
partitioned by Range (year)  
(partition p_2009 values less than () partition  
  Values less than (),  
  partition p_2011 values less than (a),  
  partition p_2012 values less than (2013)  
) As  
select * from sales;  
--Creates the local partition index create  
index sales_idx1 on Sales_par (product,year) locally;

As you can see, the statement to create a local partitioned index is very simple, and you do not need to specify a partition boundary because it has the same partition boundaries as the table. The schematic diagram is as follows:

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 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. Both prefix and non-prefix indexes can support the elimination of index partitions, provided that the criteria for the query contain index partitioning keys.

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.

Index partition elimination

If the local partition index contains the partitioning key and the predicate condition in the SQL statement contains the partitioning key, the execution plan typically requires only access to one or a few index partitions, which is called Partition elimination (Partition elimination), which effectively reduces the number of scanned blocks and improves query performance. Such as:

--Query 1:  
select * from sales_par where product = ' CPUs ' and year =;  
--Query 2:  
select * from sales_par where product = ' CPU ';

In the example above, the predicate condition for query 1 contains the partitioning key, so you can use partition elimination to reduce the number of partitions scanned (in this case, only scan the partition p_2011), while the predicate condition for Query 2 does not contain the partitioning key, so partition elimination cannot be exploited.

In addition to partition elimination, the local partitioning index also has the advantage of better table availability, and when a drop or merge operation is performed on a table partition, Oracle automatically does the same for the corresponding index partition and does not need rebuild, that is, the maintenance operation can be performed in a separate partition.