Oracle partition index-Comparison of local indexes and global indexes

Based on oracle10gR2, the partition index is divided into local index and global index ). The local index can be divided into indexes with a prefix.

Based on oracle 10gR2, the partition indexes are divided into local indexes and global indexes ). The local index can be divided into indexes with a prefix.

This article is based on Oracle 10gR2

Partition indexes are divided into local indexes and global 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. The following describes the differences between local indexes and global indexes;

I. Local index 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 a word, 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 cannot 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 the data warehouse environment. 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; --- some useless information is deleted.

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;

Ii. Global index features:

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 the index uses Column B as a partial partition index. If Column B is used for query in the where condition, oracle scans all the tables and index partitions, resulting in a higher cost score, in this case, you can use B for global partition index.

Global index: Unlike 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. The partition table test created above is used 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

4. Several views on partition Indexes
Dba_ind_partitions describes the partitions and statistical information of each partition index.
SUMMARY statistics of the dba_part_indexes partition index. You can find out which partition indexes and types of partition indexes are available on each table (local/global)
Dba_indexes minus dba_part_indexes (minus operation) can be used to obtain the non-partition indexes of each table.