Oracle partition 3: Index Partition

Partition indexes are divided into local indexes and global indexes ). For a local index, each table partition corresponds to an index partition. When the table partition changes, the index maintenance is automatically performed by Oracle. For a global index, you can select whether to partition, and the index partition does not correspond to the table partition. During partition maintenance, the global index is usually invalded and must be rebuilt after the operation is completed. Oracle9i provides the update global indexes statement, which allows you to reconstruct global indexes while maintaining partitions. 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. The number of partitions is equivalent to the table's partition, the partitioning 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 does not 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. The global index is not. A partition index can point to N table partitions. At the same time, A table partition may also point to n index partitions. truncate, move, and shrink operations on a partition in a 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 (10000) tablespace P1,
Partition P2 values less than (20000) tablespace P2,
Partition P3 values less than (maxvalue) tablespace p3
);

Create or replace procedure proc1
As
Begin
For I in 100000
Loop
Execute immediate
'Insert into warecountd values (: X,: Y) 'using I, to_char (I + 1000000 );
End loop;
End;
/

SQL> exec proc1

PL/SQL procedure successfully completed.

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', 'hr') index_name from dual;

View the system's metadata of the index.
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl ('index', 'I _ id', 'hr') index_name from dual;

Index_name
--------------------------------------------------------------------------------

Create Index "HR". "I _id" on "HR". "test" ("ID ")
Pctfree 10 initrans 2 maxtrans 255
Storage (
Buffer_pool default) Local
(Partition "p1"
Pctfree 10 initrans 2 maxtrans 255
Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
Tablespace "p1 ",
Partition "p2"

Index_name
--------------------------------------------------------------------------------
Pctfree 10 initrans 2 maxtrans 255
Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
Tablespace "p2 ",
Partition "P3"
Pctfree 10 initrans 2 maxtrans 255
Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
Tablespace "P3 ")

Of course, you can also use the preceding statement to create an index or the following SQL statement to create an index:

SQL> drop index I _id;

Index dropped

SQL> Create Index "HR". "I _id" on "HR". "test" ("ID") Local
(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.

View the system's metadata of the index.
SQL> select dbms_metadata.get_ddl ('index', 'I _ data', 'hr') index_name from dual;

Index_name
--------------------------------------------------------------------------------

Create Index "HR". "I _data" on "HR". "test" ("data ")
Pctfree 10 initrans 2 maxtrans 255
Storage (
Buffer_pool default) Local
(Partition "p1"
Pctfree 10 initrans 2 maxtrans 255
Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
Tablespace "p1 ",
Partition "p2"

Index_name
--------------------------------------------------------------------------------
Pctfree 10 initrans 2 maxtrans 255
Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
Tablespace "p2 ",
Partition "P3"
Pctfree 10 initrans 2 maxtrans 255
Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
Tablespace "P3 ")

Of course, you can also use the preceding statement to create an index or the following SQL statement to create an index:

SQL> drop index I _data;

Create Index "HR ". "I _data" on "HR ". "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 partiti locali alignment
-------------------------------------------------------------------------------------
I _data Test Range
Local non_prefixed
I _id Test Range
Local prefixed

 

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 the data in one partition is truncated, you must rebulid Several partitions or even the entire index.
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 tables and index partitions, the cost is higher than the score zone. 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
Partition by range (ID)
(Partition P1 values less than (10000) tablespace P1,
Partition P2 values less than (20000) tablespace P2,
Partition P3 values less than (maxvalue) tablespace p3
);

Index created

 

By default, the global partition index is not automatically maintained in Oracle. First, check whether the Status column is usable.

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
I _id_global P3 usable

SQL> ALTER TABLE test drop partition P2; --- Note that the status of the drop P2 index has not changed.

Table altered.

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
I _id_global P3 usable

 

SQL> Create index I _id_global_data on test (data) Global
Partition by range (ID)
(Partition P1 values less than (10000) tablespace P1,
Partition P2 values less than (20000) tablespace P2,
Partition P3 values less than (maxvalue) tablespace p3
);

Error at line 2:
ORA-14038: Global partitioned index must be prefixed prompt: The global partition index must be prefixed

 

SQL> Create bitmap index I _bmp _id_global on test (ID) Global
Partition by range (ID)
(Partition P1 values less than (10000) tablespace P1,
Partition P2 values less than (20000) tablespace P2,
Partition P3 values less than (maxvalue) tablespace p3
);

Error at line 1:
ORA-25113: Global may not be used with a bitmap index --- tip: Global may not work with bitmap indexes

3. Global partition indexes cannot be rebuilt as a whole. Each partition must be rebuilt.

SQL> alter index I _id_global rebuild online nologging;
Alter index I _id_global rebuild online nologging
*
Error at line 1:
ORA-14086: A partitioned index may not be rebuilt as a whole --- the partition index cannot be rebuilt as a whole

In this case, you can query dba_ind_partitions or user_ind_partitions to find partition_name and then 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
I _id_global p3

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.

SQL> alter index I _id_global rebuild partition P3 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)