Partition Index Summary

Partition indexes are classified into global indexes and local indexes. Local indexes can also be divided into prefix indexes and nonprefix indexes. Currently, global indexes only support indexes with a prefix. Both the B-tree index and the bitmap index can be partitioned, but the HASH index cannot be partitioned. Bitmap indexes must be local indexes. Generally, LOCAL indexes are more convenient to use.

Partition indexes are classified into global indexes and local indexes. Local indexes can also be divided into prefix indexes and nonprefix indexes. Currently, global indexes only support indexes with a prefix. Both the B-tree index and the bitmap index can be partitioned, but the HASH index cannot be partitioned. Bitmap indexes must be local indexes. Generally, LOCAL indexes are more convenient to use.

Partitioned indexes are classified into global indexes and local indexes.

Local indexes can be divided into prefix indexes and nonprefix indexes. Currently, global indexes only support indexes with a prefix. Both the B-tree index and the bitmap index can be partitioned, but the HASH index cannot be partitioned. Bitmap indexes must be local indexes.

Generally, LOCAL indexes are easier to use and have a low maintenance cost. In addition, LOCAL indexes are created based on partitions, similar to creating indexes within a sub-table, in this way, the overhead is mainly divided into partitions, which are well managed and widely used in OLAP systems. The relative GLOBAL index is a GLOBAL index, you can adjust the category of a partition based on the actual situation, instead of defining the partition structure one by one. The maintenance cost is higher, and it is used more in the OLTP environment.

I. Local index: After a partition table is created, if you need to create an index on the table, and the partition mechanism of the index is the same as that of the table, such an index is called a local partition index. Local indexes are automatically managed by ORACLE. They are divided into prefix local indexes and non-Prefix local indexes. What is a local index with a prefix? A local index with a prefix contains the partition key and uses it as the index of the boot column. What is a local index without a prefix? A local index without a prefix does not use the leading column of the partition key as the index of the leading column of the index. The following is an example:

Create table test (id number, data varchar2 (100 ))
Partition by RANGE (id)
(
Partition p1 values less than (1000) tablespace p1,
Partition p2 values less than (2000) tablespace p2,
Partition p3 values less than (maxvalue) tablespace p3
);

Create index I _id on test (id) local; Because id is the partition key, a local index with a prefix is created.

SQL> select dbms_metadata.get_ddl ('index', 'I _ id', 'robinson') index_name FROM DUAL; ------ some useless information is removed.

INDEX_NAME

--------------------------------------------------------------------------------

Create index "ROBINSON". "I _ID" ON "ROBINSON". "TEST" ("ID") LOCAL

(PARTITION "P1" TABLESPACE "P1", PARTITION "P2" TABLESPACE "P2", PARTITION "P3" TABLESPACE "P3 ");

You can also create it as follows:

SQL> drop index I _id;

Index dropped

SQL> create index "ROBINSON". "I _ID" ON "ROBINSON". "TEST" ("ID") LOCAL
2 (PARTITION "P1" TABLESPACE "P1", PARTITION "P2" TABLESPACE "P2", PARTITION "P3" TABLESPACE "P3 ");

Index created

Create index I _data on test (data) local; because data is not a partition key, a local index without a prefix is created.

SQL> select dbms_metadata.get_ddl ('index', 'I _ data', 'robinson') index_name from dual; --- I deleted some useless information.

INDEX_NAME
--------------------------------------------------------------------------------

Create index "ROBINSON". "I _DATA" ON "ROBINSON". "TEST" ("DATA") LOCAL
(PARTITION "P1" TABLESPACE "P1", PARTITION "P2" TABLESPACE "P2", PARTITION "P3" TABLESPACE "P3 ");

The user_part_indexes view can also prove that the created index has a prefix and no prefix.

SQL> select index_name, table_name, partitioning_type, locality, ALIGNMENT from user_part_indexes;

INDEX_NAME TABLE_NAME PARTITIONING_TYPE LOCALITY ALIGNMENT
-------------------------------------------------------------------------------------------------
I _DATA TEST RANGE LOCAL NON_PREFIXED
I _ID TEST RANGE LOCAL PREFIXED

Ii. Global index: different from the local partition index, the partition mechanism of the global partition index is different from that of the table. The global partition index can only be a B-tree index. So far (10gR2), oracle only supports global indexes with a prefix. In addition, oracle will not automatically maintain the global partition index. After we modify the table partition, if the modified statement is executed without the update global indexes, the index will be unavailable. Take the created partition table test as an example to explain the global partition index.

SQL> drop index I _id;

Index dropped

SQL> create index I _id_global on test (id) global
2 partition by range (id)
3 (partition p1 values less than (2000) tablespace p1,
4 partition p2 values less than (maxvalue) tablespace p2
5 );

Index created

SQL> alter table test drop partition p3;

Table altered

By default, the global partition index is not automatically maintained in ORACLE. Check the status column,

SQL> select INDEX_NAME, PARTITION_NAME, STATUS from user_ind_partitions where index_name = 'I _ ID_GLOBAL ';

INDEX_NAME PARTITION_NAME STATUS
--------------------------------------------------------------------
I _ID_GLOBAL P1 USABLE
I _ID_GLOBAL P2 USABLE

SQL> create index I _id_global on test (data) global
2 partition by range (id)
3 (partition p1 values less than (2000) tablespace p1,
4 partition p2 values less than (maxvalue) tablespace p2
5 );

Create index I _id_global on test (data) global
Partition by range (id)
(Partition p1 values less than (2000) tablespace p1,
Partition p2 values less than (maxvalue) tablespace p2
)

ORA-14038: The GLOBAL partition index must be prefixed

SQL> create bitmap index I _id_global on test (id) global
2 partition by range (id)
3 (partition p1 values less than (2000) tablespace p1,
4 partition p2 values less than (maxvalue) tablespace p2
5 );

Create bitmap index I _id_global on test (id) global
Partition by range (id)
(Partition p1 values less than (2000) tablespace p1,
Partition p2 values less than (maxvalue) tablespace p2
)

ORA-25113: GLOBAL may not work with bitmap indexes

3. The partition index cannot be rebuilt as a whole. It must be rebuilt for each partition.

SQL> alter index I _id_global rebuild online nologging;

Alter index I _id_global rebuild online nologging

ORA-14086: partition indexes cannot be rebuilt as a whole

In this case, you can query dba_ind_partitions or user_ind_partitions, find partition_name, and recreate each partition.

SQL> select index_name, partition_name from user_ind_partitions where index_name = 'I _ ID_GLOBAL ';

INDEX_NAME PARTITION_NAME
------------------------------------------------------------
I _ID_GLOBAL P1
I _ID_GLOBAL P2

SQL> alter index I _id_global rebuild partition p1 online nologging;

Index altered

SQL> alter index I _id_global rebuild partition p2 online nologging;

Index altered

Iv. Differences between creating a partitioned index and a common index

1. Create a Common Index

Create index no_part_idx on p_list (sale_date );

2. Create a local partition Index

Create index local_part_idx on p_list (sale_date) local;

3. Create a global partition Index

Create index glo_part_idx on p_list (sale_date) global partition by range (sale_date )(

PARTITION p1 VALUES less than ('20140901') TABLESPACE system,

PARTITION p2 VALUES less than ('20140901') TABLESPACE system,

PARTITION pmax VALUES less than (maxvalue) TABLESPACE system );

Iv. Failed index deletion and deletion:

For partition index deletion, the local index cannot specify the partition name. You must delete the partition index separately. The partition corresponding to the local index is deleted along with the deletion of the data Partition. Globalpartition index can specify a partition name to delete a partition. Note that if the partition is not empty, the index partition at the higher level will be set to UNUSABLE.

Alter index gidx_range_exampel_id drop partition part_01 this sentence will cause the part_02 state to UNUSABLE

The local partition index status changes.

-- The entire index is unavailable.

Alter index index_tmp_partition_list UNUSABLE;

-- A single partition index is unavailable

Alter index index_tmp_partition_list modify partition p_20151116 USABLE;

-- After the partition is unavailable, you need to re-create the index.

Alter index index_tmp_partition_list rebuild partition p_20151116;

-- View the status of the partition Index

Select index_name, partition_name, tablespace_name, status

Fromuser_ind_partitions wherelower (index_name) = 'index _ tmp_partition_list'

Reference: http://blog.csdn.net/robinson1988/article/details/5276924