Partition index note (3) -- global partition Index

Partition index note (3) -- global partition index (2) -- local partition Index http://www.bkjia.com/database/201308/238634.html A global partition index contains keys from multiple table partitions in an index partition. The partition key of a global partition index is a value of different or specified range in the partition table. When creating a global partition index, you must define the range and value of the partition key. The global index can only be a B-tree index. By default, Oracle does not maintain global partition indexes. If a partition is intercepted, added, split, or deleted, the GLOBAL partition index must be rebuilt unless the update global indexes clause of the alter table command is specified when the TABLE is modified. 1. Type (1) index with a prefix. Generally, global index with a prefix does not pass through the peer partition in the underlying table. There is no limit on the peer partitions of an index. However, when Oracle generates a query plan or performs partition maintenance, it does not take full advantage of the Peer partitions. If an index is a peer-to-peer partition, it must be created as a local index, so that Oracle can maintain this index and use it to delete unnecessary partitions, each partition contains an index entry pointing to the row of multiple table partitions. Tip: if a global index will be a peer-to-peer partition, you must create it as a local index, so that Oracle can maintain this index and use it to delete unnecessary partitions. (2) for an index without a prefix so far (10gR2), Oracle does not support a global index without a prefix. 2. note: (1) 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 its first few columns. (2) global indexes can be attached to partitioned tables or non-partitioned tables. (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, you need to rebulid Several partitions or even the entire index. (4) global indexes are mostly used in oltp systems. (5) global partition indexes are only supported by the range or hash partition after 10 Gb. (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, in exchange for high availability by consuming a certain amount of resources. (7) The table uses column a as a partition, and the index uses Column B as a partial partition index. If Column B is used for query in the where condition, oracle scans all tables and index partitions, the cost is higher than the score zone. In this case, you can use B for global partition index. 3. Example 1 global index: global index is supported for all partition types:

sql> create index ix_custaddr_ global_id on custaddr(id) global;

 

The index has been created. Example 2: global partition index. Only Range partitions and Hash partitions are supported. (1) create two test partition tables:

sql> create table pdba (id number, time date) partition by range (time)  (  partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),  partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),  partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),  partition p4 values less than (maxvalue)  );

 

The table has been created.

SQL> create table Thash  (  id number primary key,  item_id number(8) not null  )  partition by hash(id)  (  partition part_01,  partition part_02, partition part_03 );

 

The table has been created. (2) Example 2: global partition Index

SQL> create index i_id_global on PDBA(id) global  partition by range(id)  (partition p1 values less than (200),  partition p2 values less than (maxvalue)  );

 

The index has been created. -This is a partitioned index with a prefix.

SQL> create index i_time_global on PDBA(id) global  partition by range(time)  (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),  partition p2 values less than (maxvalue)  );partition by range(time)                       *

 

Row 2nd error: ORA-14038: The GLOBAL partition index must be prefixed

SQL> create index i_time_global on PDBA(time) global  partition by range(time)  (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),  partition p2 values less than (maxvalue)  );

 

The index has been created. -- Partitioned index with a prefix

SQL>  select  index_name,table_name,partitioning_type,locality,ALIGNMENT  fromuser_part_indexes where table_name='PDBA';index_name                        table_name partition locali alignment------------------------------ ---------- --------- ------ ------------i_id_global                          pdba              range          global prefixedi_time_global                      pdba              range          global prefixedSQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL  2              PARTITION BY HASH (id)  3              (PARTITION p1,  4                PARTITION p2,  5                PARTITION p3,  6                PARTITION p4);

 

The index has been created. As long as the index Bootstrap column contains a partition key, it is a partition index with a prefix.