Friday , October 4 2024

Partition Table – Primary Key

Global indexes become corrupted when a partition is dropped from the partition table and in this article we will learn how to fix these indexes as LOCAL indexes again.

Global indexes (for example Primary Key) that exist in a partition table are corrupted and become UNUSABLE when a partition is dropped from the partition table.

In this case, these indexes need to be rearranged as LOCAL indexes.

The steps are as follows.

1. When creating an index, the larger the table, the longer the process will take.

Therefore, we can list and delete unnecessary parititions.

Below are listed the partitions of the CHANNEL_STAT table that are older than 15 days.

These partitions can be easily deleted with the output of 5 columns.

2. We check the index status (where index is PRIMARY KEY).

3. The unnecessary partitions we obtained with the first query are deleted.

4. We check the index status again. After the partition deletion process done in the third step, PK indexes will be UNUSABLE.

This status is checked.

5. The PK indexes that cause the problem are deleted. We cannot add a new PK Index without doing this.

Also, we cannot delete the PK index with the “DROP INDEX <index_name>” sql code.

If we try to delete, we will get an error.

6. Then these indexes are recreated in accordance with the PARTITION table.

One of these columns is the ID column and the other is the column we use for the PARTITION process.

The CREATE SCRIPT of the table to be partitioned is given at the bottom.

7. Index statuses are checked again. If the work has been completed correctly, their status will appear as “N/A”.

8. The create script of the table being worked on is as follows.

Loading

About Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories