Oracle local partition index usage Summary

Indexes are often created to improve data access efficiency. However, each index reconstruction operation slows down when the data volume is large, affecting the processing efficiency. Therefore, for a large amount of data,

Indexes are often created to improve data access efficiency. However, each index reconstruction operation slows down when the data volume is large, affecting the processing efficiency. Therefore, for a large amount of data,

Indexes are often created to improve data access efficiency. However, each index reconstruction operation slows down when the data volume is large, affecting the processing efficiency. Therefore, for a large amount of data, it is often used to combine partitions and partition indexes. In this way, the historical partition index does not need to be rebuilt, and you only need to process the new partition index, the process record for testing the Oracle local partition index is as follows:

1. Create a partition table

Create tabletmp_partition_list (

Report_datevarchar (10 ),

User_cntnumber (10)

)

Partition bylist (report_date )(

Partition p_20151116 values ('20170901 '),

Partitionp_20151117 values ('20170901 '),

Partitionp_default values (default)

);

2. Insert data

Insert into tmp_partition_list (report_date, user_cnt) values ('20140901', 12 );

Insert into tmp_partition_list (report_date, user_cnt) values ('20140901', 13 );

Insert into tmp_partition_list (report_date, user_cnt) values ('20140901', 14 );

Insert into tmp_partition_list (report_date, user_cnt) values ('20140901', 15 );

The inserted data is automatically inserted into the specified partition. If the partition corresponding to this field does not exist, an error is returned. If there is a default partition, It is inserted into the default partition.

3. Partition split method

-- Split method of range Partition

Alter tabletmp_partition_list split partition p_default at ('000000')

(Partitionp_20151122, partition p_default );

-- Split method of list-type partitions

Alter tabletmp_partition_list

Split partitionp_default values ('20140901') (

Partitionp_20151122, partition p_default

);

4. Partition addition and deletion Methods

Alter tabletmp_partition_list add partition p_20151120 values ('20140901 ');

In the following two cases, an error is reported: The partition exists, or a default partition exists. If a default partition exists, the split partition can be used.

Alter tabletmp_partition_list drop partition p_20151120 );

5. How to create a local index

-- All partitions must be added during initial index creation. After each partition is added, the partition index is automatically allocated.

Create indexindex_tmp_partition_list on tmp_partition_list (report_date)

Local

(

Partition p_20151116,

Partition p_20151117,

Partition p_default

)

-- Prefix local partition index syntax is relatively simple

CREATE INDEXindex_tmp_partition_list ON tmp_partition_list (report_date) LOCAL;

Benefits of local indexes: deleting partitions does not affect other indexes.

6. The local partition index status changes.

-- The entire index is unavailable.

ALTER INDEXindex_tmp_partition_list UNUSABLE;

-- A single partition index is unavailable

ALTER INDEXindex_tmp_partition_list modify partition p_20151116 USABLE;

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

Alter indexindex_tmp_partition_list rebuild partition p_20151116;

-- View the status of the partition Index

Selectindex_name, partition_name, tablespace_name, status

Fromuser_ind_partitions

Wherelower (index_name) = 'index _ tmp_partition_list ';

7. Usage Policy

(1) Add a new partition for the current day

(2) import data

(3) This partition index rebuild