Shulou(Shulou.com)06/01 Report--

This article introduces when the global and local partition indexes in the partition table will fail and how to deal with final, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The maintenance of the local index in the partitioned table will be carried out automatically when oracle operates the table partitioning. It should be noted that the global index. When the alter table of the table where the global index is located involves the following operations, it will lead to the index invalidation and need to be re-established:

 ADD PARTITION | SUBPARTITION

 COALESCE PARTITION | SUBPARTITION

 DROP PARTITION | SUBPARTITION

 EXCHANGE PARTITION | SUBPARTITION

 MERGE PARTITION | SUBPARTITION

 MOVE PARTITION | SUBPARTITION

 SPLIT PARTITION | SUBPARTITION

 TRUNCATE PARTITION | SUBPARTITION

Therefore, it is recommended that users append the update indexes clause, oracle, after the above operation sql statement

That is, the global index is automatically maintained. Of course, you need to note that there is a balance in the middle. You need to balance the operation of ddl.

Which is less time or re-indexing to determine whether the updateindexes clause needs to be appended.

The update indexes clause is not specified when alter table add partition is executed:

a. If it is a range/list partition, its local index and global index will not be affected

b. If it is a hash partition, the local index and global index of the newly added partition and the partition with data movement will be

Set to unuseable and need to be recompiled.

If you do not specify a update indexes clause when executing drop partition, it will result in glocal

The invalidation of the index, for the local index, the corresponding index partition will be deleted when the partition is deleted, and

Its partitioned local index is not affected.

When executing split partition/subpartition, if the update indexes child is not specified

Sentence, both local and global indexes will be invalidated. But if you split partition/

Subpartition is an empty partition, or does not trigger any data movement or change, so even if you do not add

Update indexes also does not affect the index. Of course, just to be on the safe side, I suggest you finish the execution.

Query the data dictionary to confirm the status of the current index.

Here's a simple test:

1. Create a Range partition table:

CREATE TABLE DFMS.TEST04

PARTITION BY RANGE (OBJECT_ID)

(

PARTITION P1 VALUES LESS THAN (2000)

TABLESPACE LOG_DATA

PARTITION P2 VALUES LESS THAN (8000)

TABLESPACE LOG_DATA

PARTITION P3 VALUES LESS THAN (20000)

TABLESPACE LOG_DATA

PARTITION P4 VALUES LESS THAN (40000)

TABLESPACE LOG_DATA

PARTITION PMAX VALUES LESS THAN (MAXVALUE)

TABLESPACE LOG_DATA

)

AS

SELECT * FROM DBA_OBJECTS

two。 Set up a competition and generate global index:

Alter table DFMS.TEST04 add constraint pk_id primary key (object_id)

Create a local index:

CREATE INDEX DFMS.IDX1_TEST04 ON DFMS.TEST04

(OBJECT_NAME) LOCAL

3. We look at the status of global index through the dba_indexes view and find that it is valid:

Select index_name, status, last_analyzed,partitioned

From dba_indexes where index_name='PK_ID'

The status viewed by the local index local index through dba_indexes is N _ A, which needs to be checked through the

Dba_ind_partitions to check, you can see that each index partition is USABLE state.

You can see the overall state of the local partition index through DBA_PART_INDEXES.

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

Select * from dba_indexes where index_name='IDX1_TEST04'

Select * from DBA_PART_INDEXES where index_name='IDX1_TEST04'

4. Because of the existence of maxvalue, we first test the impact of split on global and local indexes.

4.1 data in all new partitions

Alter table test04 split partition pmax at (80000) into

(partition p5 tablespace log_data

Partition pmax tablespace log_data)

We can see from table's script that pmax is divided into two parts: P5 and pmax:

....

PARTITION P5 VALUES LESS THAN (80000)

PARTITION PMAX VALUES LESS THAN (MAXVALUE)

.

Obviously, because the number of rows of select max (object_id) from TEST04 is 101769

The later old partition that conforms to less than 80000 remains in the first partition, p5, and all the others exist.

There is a second partition (the new pmax partition).

We check the status of global index and local index:

Select index_name, status, last_analyzed,partitioned

From dba_indexes where index_name='PK_ID'

This obviously triggers the movement of the data, and the global index index state becomes UNUSABLE.

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

Because there is data in the partition (in this case, p5) from the new split, the data in the original pmax is split into

In the new partition P5 and the new pmax, it is found that the local index corresponding to p1 and p2, p3 and p4 is still USABLE.

The local indexes corresponding to the new P5 and the new pmax are both UNUSABLE.

OK, we rebuild the local partition index corresponding to global index and p5meme Pmax:

Alter index PK_ID rebuild online

Then the query finds that global index becomes valid:

Select index_name, status, last_analyzed,partitioned

From dba_indexes where index_name='PK_ID'

Alter index IDX1_TEST04 rebuild partition p5 online

Alter index IDX1_TEST04 rebuild partition pmax online

Query after execution:

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

You can see that both the index partition P5 and the pmax state become USABLE.

4.2 one of the new partitions has no data

The number of rows of TEST04 is 101769, so we set the new partition of p6 to 110000, then the pmax partition

There is obviously no data.

Alter table test04 split partition pmax at (110000) into

(partition p6 tablespace log_data, partition pmax tablespace log_data)

Look at global index and local index to see that the global index and each local index partition is

USABLE, this is because the data movement is not triggered.

Select index_name, status, last_analyzed,partitioned from dba_indexes where index_name='PK_ID'

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

Note: in the case of split pmax partition, the new partition name can be picked up (not necessarily containing pmax), such as the above

P6 can be used for p7, except that p6 will follow less than 110000, while the second partition, p7, is still less

Than maxvalue.

And because there is no change in the table space, if the non-empty partition storage properties are different from the original storage properties, data movement will occur and the index will fail.

5. Test the impact of drop partition on global and local indexes.

Delete the last pmax partition of the test04 table that has no data.

Alter table test04 drop partition pmax

Because the deleted partition does not have data, it does not involve data changes, so for global and local

It doesn't matter.

Suppose we want to delete the part that has data, leaving neither the partition nor the data, then the local index

Will not be affected, global index will fail.

Alter table test04 drop partition p6

Query

Select * from dba_indexes where index_name='PK_ID'

The global index fails and the state changes to UNUSABLE.

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

The state of the local index (other partitions) remains unchanged, which is USABLE.

6. Test the impact of add partition on global and local indexes.

Alter table test04 add partition p6 values less than (120000)

Query status:

Select * from dba_indexes where index_name='PK_ID'

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

It is found that Range partitions and adding partitions have no effect on global and local indexes.

Similarly, by testing list partitions, you can see that adding partitions has no effect on global and local indexes.

This is mainly because the movement of the data is not triggered.

For Hash partitions, due to the occurrence of balanced data distribution in add parittion, the data

It will be moved, so both the local partition index and the global index will be set to UNUSABLE, which requires rebuild.

Here's a simple test:

CREATE TABLE DFMS.TEST05

PARTITION BY HASH (OBJECT_ID)

PARTITIONS 8

STORE IN (LOG_DATA)

AS SELECT * FROM DBA_OBJECTS

Join global and local index.

Alter table DFMS.TEST05 add constraint pk_test05_id primary key (object_id)

CREATE INDEX DFMS.IDX1_TEST05 ON DFMS.TEST05 (OBJECT_NAME) LOCAL

Add a new partition:

Alter table test05 add partition

Query

Select * from dba_indexes where index_name='PK_TEST05_ID'

Select * from dba_ind_partitions where index_name='IDX1_TEST05'

Found that global index is the UNUSABLE state, the first and last in the local partition index

The local partition index of the partition is UNUSABLE state, and the other is USABLE. Obviously, because the data is from the first place

Two partitions were split into new hasn partitions, so the data in both partitions was moved

This leads to the invalidation of the local partition index because of the movement of data rows, and of course global index

Become a failure state (UNUSABLE).

7. Test the impact of truncate partition on global and local indexes.

Truncate partition, like truncate table, truncates data directly from the header. Do not refer to

If the update indexes clause is specified, the truncate partition will also cause the partition of the table

The global index is invalid. The syntax is very simple:

Alter table tbname truncate partition/subpartition ptname

Alter table test04 truncate partition p6

Query

Select * from dba_indexes where index_name='PK_ID'

Select * from dba_ind_partitions where index_name='IDX1_TEST04'

It is found that the global index index is invalid and the local partition index state is USABLE.

8. Other operations such as Merge Partitions,Exchange Partitions and coalesce partitions

Wait for less use, no testing is done here.

In fact, in short, if data movement occurs, then the index must need to pay attention.

On the partition table when the global and local partition index will fail and how to deal with final to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.