Global and local partition Indexes

The partition index is a global partition index and a local partition index, and a global non-partition index (that is, a common index) is added. A total of three types of indexes are added. The organizational structure and application scenarios of these three indexes are discussed below.

1. Global Non-partitioned indexes can depend on normal tables or partitioned tables. Create index month_ix ON sales (sales_month); equivalent to create index month_ix ON sales (sales_month) GLOBAL;
2. the global partition Index uses a partition mechanism different from the underlying table, which means that the partition key of the index can be different from the partition key of the table, however, the index key prefix must contain the index partition key. That is, there is only "Global prefix Index", but not "Global Non-Prefix Index ". In this way, an index with the index partition key can be used for the unique and primary constraints of a table even if it does not contain the table partition key. Each segment represents an index partition. The key value in each index partition can point to any table partition. It can be dependent on common tables or partitioned tables. The number of index partitions is not equal to the number of table partitions. You can only partition indexes by range or hash (10 Gb. The last partition of the range partition of the global index must be maxvalue to ensure that all rows in the underlying table can be placed in this index. Create index month_ix ON sales (sales_month, sales_date) global partition by range (sales_month) (PARTITION pm1_ix values less than (2), PARTITION pm2_ix values less than (3 ), PARTITION pm3_ix values less than (4), PARTITION pm4_ix values less than (5), PARTITION pm12_ix values less than (MAXVALUE ));
When a global index is created, the global clause allows you to specify the index range value, which is the range of the index partition key. Sales_month of global partition index by range (sales_month) specifies the index PARTITION key, which can be different from the table PARTITION key. In this case, we set up the PARTITION key separately. At this time, sales (sales_month, sales_date) when specifying the index key, the prefix must contain the index partition key. All of this can be unrelated to the underlying table.
Use Cases: For data warehouses, such as the continuous deletion of old data and inbound (Sliding Window) of new data, global indexes are prone to failures, affecting performance. 3. The local partition index cannot be used to create this index for a common table. It depends on the partition key of the partition table, that is, the partition mechanism of the underlying table is used to create the index. With the creation of a one-to-one index partition for table partitions, entries in each index partition only point to one table partition. Create index partition ON dept (deptno) LOCAL; create index dinya_idx_t on dinya_test (item_id) local (partition idx_1 tablespace tbs1, partition idx_2 tablespace tbs2, partition idx_3 tablespace tbs3 ); the local partition index can be logically divided into: Local prefix index -- the table partition key is on the first column defined by the index. For example, to partition the range of the table field LOAD_DATE, when the index is created, LOAD_DATE is the first column of the index. Partial non-Prefix index-the index does not use the table partition key as the first column of its index field, or even does not contain the partition key.
What are the effects of partial prefix indexes and partial non-Prefix indexes on partition elimination? First, we need to understand what partition elimination is. A transaction can only consider specific partitions. Even if the physical media of other partitions is damaged and the tablespace of other partitions is offline, transactions can ignore and not scan them. Partition elimination type: partitions in the table are eliminated, and partitions in the index are eliminated. Partition elimination is mainly for availability, and when full table scan and full index scan occur, it is converted to only scan specific partitions to improve performance. Whether or not partitions can be eliminated depends on whether the predicates have partition keys. If the predicate contains the partition key, the index partition may be eliminated, or the table partition may be eliminated. If the predicate does not contain the partition key, the elimination of Shenma partitions is a luxury. Whether a local prefix index or a local Non-Prefix index is used only affects whether the index partition can be eliminated. Only partial prefix indexes can be used to eliminate index partitions. Partial non-Prefix indexes cannot be used to eliminate index partitions. (However, table partitions may still be eliminated, however, when cbo evaluates the index, it finds that the index partition is unavailable, such as the tablespace offline, which cannot be changed ). When the cbo evaluates the cost, it does not consider whether the partition index is available. It evaluates a path and finds that this path does not work. It cannot go back and report an error directly.
What is the SQL Execution performance of partial prefix indexes and partial non-Prefix indexes? If the index is used as the first step in the query plan, there is no difference in efficiency. Although prefix and non-Prefix indexes will affect whether partition elimination can be used, what is partition elimination? Improves availability and optimizes the performance of converting full table scan to single-partition full scan. Therefore, it does not matter if the index is taken as the first step and whether the partition is eliminated, so that the prefix does not matter.
How can I select a local prefix index or a local Non-Prefix index? How to choose, first of all, should be able to meet the needs. If you create an (B, a) index, but you always query where a = 3, it is not good to lead to a lot of skip scans. At this time, you should replace it with (a, B). If there is only a query like where a = 1 and B = 2, you may ask, are we building (a, B) Good or (B, a) good, check which field is highly selective and whether it is necessary to remove the index partition of a. If the density of B is large, it can be found from 1 to, and a can only be 1 and 2, in this case, we should better forward B. Therefore, if you want to add a field to the front, you need to meet the business requirements and eliminate the partitions of the comprehensive predicates, and select the field selection rate.
If you want to use the unique or primary key constraint for the partial index and unique constraint on the partition table fields, the global index is generally used to ensure uniqueness. This is a general practice. Because the local index only guarantees the uniqueness of the keys inside the partition, rather than cross-partition. If you really want to use a local index to ensure the uniqueness of the entire table, you have to add the partition key to the constraint,. If oracle allows a local index (without constraints), it is easy to ensure the uniqueness of the entire table. Therefore, all the update and insert operations must scan each partition, in this way, both availability and scalability are lost.
4. What are the three index options? Local indexes are used in OLAP systems. Global indexes are more common in OLTP systems. Availability: local indexes are more available. Even if an index partition has a problem, it does not affect others. However, global indexes may become a fault point. Once a problem occurs, the entire index is unavailable. Maintainability: the maintenance of local indexes is more flexible. DBA decides to move a table partition and only needs to re-build and maintain an index partition. For global indexes, reconstruction is required in many cases. SQL efficiency: because local indexes are partitioned with the table, the optimal execution plan can be involved.
View select * from DBA_IND_PARTITIONS where index_name = 'local _ noprefixed'; select * from DBA_PART_INDEXES where index_name = 'local _ noprefixed'; select * from region where name = 'local _ NOPREFIXED ';

Experiment: -- create a tablespace tbs1, tbs2, tbs3create tablespace tbs1 datafile '+ DATA6_MIDG'; create tablespace tbs2 datafile '+ DATA6_MIDG'; create tablespace tbs3 datafile '+ DATA6_MIDG '; SQL> -- create a range partition table create table t_part (a int, B int, data char (20) partition by range (a) (partition p1 values less than (2) tablespace tbs1, partition p2 values less than (3) tablespace tbs2); -- insert some data into t_part select mod (rownum-1, 2) + 1, rownum, 'x' from all_objects; commit; SQL> select * from t_part where rownum <= 10; -- create a local prefix index and a local non-Prefix index create index local_prefixed on t_part (a, B) local; create index local_noprefixed on t_part (B) local; create index local_prefixed on t_part (a, B) local (partition ind1 tablespace tbs3, partition ind2 tablespace tbs2 );
-- Collect statistical information for the table begin dbms_stats.gather_table_stats (user, 't_ part', cascade => TRUE); end; -- remove the tbs2 tablespace. In this case, you can verify that the index partition is eliminated, remove tbs1 to verify that table partitions are eliminated. Alter tablespace tbs3 offline; -- Use this command to test select * from t_part where a = 1 and B = 1; select * from t_part where B = 1; select/* + full (t_part) */* from t_part where a = 1 and B = 1;