oracle-Creating an index partition

Partitioning a large data volume index can also optimize the performance of the application system. In general, it is recommended that the index be partitioned if the data for the table that corresponds to the index is very large, such as millions of or even thousands data, and the index takes up a large amount of space.

The Oracle index partition is divided into local and global index partitions: The global index does not reflect the structure of the underlying table, so the partition can only be scoped, whereas a local index reflects the structure of the underlying table.

Local index partition

　　A local index partition is an index that is partitioned using the same partition key as the partition table, that is, the index partition uses the same columns as the table's partitions, and the local index has the following advantages:

If only one partition needs to be maintained, only one local index is affected

Support for partition Independence

Only local indexes can support loading and unloading of single partitions
Table partitions and their respective local indexes can be restored at the same time
Local indexes can be duplicated individually
Bitmap indexes are supported only by local indexes

Such as: Create a table partition, and then create a local index area based on the table partition
1. First create 3 table spaces, which are stored in 3 different disk partitions, respectively, Ts_1,ts_2,ts_3

2. Create a partitioned table Studentgrade that stores student scores, which has 3 partitions in the table space Ts_1,ts_2,ts_3

CREATE TABLE Studentgrade
(
ID number primary Key,
Name VARCHAR2 (10),
Subject VARCHAR2 (10),
Grade number
)
Partition by range (grade)
(
Partition Par_nopass values less than tablespace ts_1,
Partition Par_pass values less than tablespace ts_2,
Partition Par_good values less than (MaxValue) tablespace Ts_3
)
/

3. Create a local index partition from a table partition, as with a table partition, the index partition is also 3 partitions (P1,P2,P3)

Create INDEX Grade_index on Studentgrade (grade)
Local
(
Partition P1 tablespace Ts_1,
Partition P2 tablespace ts_2,
Partition P3 tablespace Ts_3
)
/

4. Finally, the user can view the index partition information by querying the Dba_ind_partitions view

Select Partition_name,tablespace_name from dba_ind_partitions where index_name= ' Grade_index ';

Global Index Area

A global index is a partitioned index that does not have the same partition key as the partition table. Global index Partitioning when many things occur in a partition and you want to ensure that data records in all partitions are unique

A global index partition can be used on a table regardless of whether the table is partitioned or not, and Global index partitioning cannot be used on cluster tables and bitmap indexes

For example, to create a global index of a range partition by listing the Saleprice of the books table as the index column and the partition key

Create index Index_saleprcie on books (Saleprice)

Global partition by Range (Saleprice)

(

Partition P1 values less than (30),

Partition P2 values less than (50),

Partition P3 values less than (MaxValue)

);

For example, to create a global index of a hash partition by listing the ISBN in the Books table as the index column and the partition key

Create index INDEX_ISBN on books (ISBN)

Global partition by hash (ISBN);

Manage Index partitions

Among the various operations in managing an index partition, the most common operations include removing the index partition and renaming the index partition

1. Deleting an index partition

In the Index_saleprice index of the books table, use the ALTER INDEX ... drop PARTITION statement to delete the index partition in it P2

ALTER index Index_saleprice drop partition p2;

The highest partition of the index cannot be dropped for the global index partition, or the system will prompt for an error

After removing several index partitions, if only one index partition is left, the partition needs to be rebuilt, and the rebuild partition can use ALTER index. Rebuild partition

For example: In the Index_saleprice index of the books table, delete the P2 and P1 index partitions, and then use the ALTER INDEX ... rebuild partition statement to rebuild the index partition P3

ALTER index Index_saleprice drop partition p2;

ALTER index index_saleprice drop partition p1;

ALTER INDEX INDEX_SALEPRICE rebuild partition p3;

Renaming an index partition

For example: In the Index_saleprice index, use the ALTER index ... rename partition to rename the index partition P3

ALTER index Index_saleprice rename partition p3 to p_new;

oracle-Creating an index partition