Resolving index failure when Oracle Partition Table deletes partition data

Resolving index failure when Oracle Partition Table deletes partition data

I. Description

Today, we have a small task to delete some data. Haha, please be happy first. Because the data table to be deleted is the partition table that I previously converted. The partition table is partitioned according to the creation time field. The first quarter is one partition. So now I want to delete the data before December 31, July 1, 2017 (about 10 million of the data). You can simply delete the table partition data. If you use delete to delete so much data, I need to write the stored procedure and submit it in batches. This is a simple truncate partition that causes subsequent business faults. The index of the table is invalid. The index is restored after the table is re-created. Really sweat!
 
Ii. Experiment

1. Create an environment

SQL> create table TEST_PARTAS (id number (11), ACCOUNT_ID number (11), CTIME date)

2 partition by range (CTIME)
3 interval (NUMTOYMINTERVAL (3, 'month '))
4 (partition P0 values less than (TO_DATE ('1970-01-01 ', 'yyyy-mm-dd ')),
5 partition p1 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')));

Table created.

SQL> insert into TEST_PARTAS select t. id, t. account_id, t. create_time from act_test t;
3483178 rows created.

SQL> commit;
Commit complete.

SQL> EXEC SYS. DBMS_STATS.GATHER_TABLE_STATS ('Sam ', 'test _ PARTAS ');
PL/SQL procedure successfully completed.

2. Check partition tables and data

SQL> select count (*) from TEST_PARTAS;

COUNT (*)
----------
3483178

SQL> set lines 120 pages 200;
SQL> set long 9999999
SQL> col table_name for a15
SQL> col PARTITION_NAME for a10

SQL> select t. table_name, t. partition_name, t. num_rows, t. blocks, t. interval, t. high_value from USER_TAB_PARTITIONS t;

TABLE_NAME PARTITION _ NUM_ROWS blocks int HIGH_VALUE
--------------------------------------------------------------------------------------------------
TEST_PARTAS P0 2182116 6046 NO TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

TEST_PARTAS P1 616290 36506 NO TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

TEST_PARTAS SYS_P1611 44829 4030 YES TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

TEST_PARTAS SYS_P1612 21706 3022 YES TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

TEST_PARTAS SYS_P1613 172525 3022 YES TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

TEST_PARTAS SYS_P1614 442435 2014 YES TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

TEST_PARTAS SYS_P1615 3277 238 YES TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24:

MI: ss', 'nls _ CALENDAR = GREGORIAN ')

7 rows selected.

3. Create a primary key and an index

SQL> alter table TEST_PARTAS add constraint pk_id primary key (ID );

Table altered.

SQL> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID );
Index created.

4. Check the index status. The current status is available.

SQL> select T. INDEX_NAME, T. TABLE_NAME, T. STATUS from user_indexes t where t. table_name = 'test _ partas ';

INDEX_NAME TABLE_NAME STATUS
--------------------------------------------------------------------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

5. Use truncate to delete p0 partition data without the update index parameter.

SQL> alter table test_partas truncate partition p0;

Table truncated.

6. Check the index status. The status is unavailable.

SQL> select T. INDEX_NAME, T. TABLE_NAME, T. STATUS from user_indexes t where t. table_name = 'test _ partas ';

INDEX_NAME TABLE_NAME STATUS
--------------------------------------------------------------------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

7. re-create the index and add online to minimize the impact on the business.

SQL> alter index PK_ID rebuild online;

Index altered.

SQL> alter index IND_ACCOUNT_ID rebuild online;

Index altered.

8. Check the index status. At this time, the index returns to the normal available status.

SQL> select T. INDEX_NAME, T. TABLE_NAME, T. STATUS from user_indexes t where t. table_name = 'test _ partas ';

INDEX_NAME TABLE_NAME STATUS
--------------------------------------------------------------------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

9. Use truncate to delete p1 partition data and add the update index parameter.

SQL> alter table test_partas truncate partition p1 update indexes;

Table truncated.

10. Check the index status. At this time, the index is normal and available.

SQL> select T. INDEX_NAME, T. TABLE_NAME, T. STATUS from user_indexes t where t. table_name = 'test _ partas ';

INDEX_NAME TABLE_NAME STATUS
--------------------------------------------------------------------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

Iii. Expansion
Through this question, we can extend it again. If drop partition will affect the index as well, the answer is yes. If you delete the partition, the index will still be invalid.

SQL> alter table test_partas drop partition SYS_P1611;

Table altered.

SQL> select T. INDEX_NAME, T. TABLE_NAME, T. STATUS from user_indexes t where t. table_name = 'test _ partas ';

INDEX_NAME TABLE_NAME STATUS
--------------------------------------------------------------------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

Iv. Summary
A small mistake caused a big problem. Fortunately, this operation did not affect the core business table. Through mistakes, I also saw my lack of knowledge points. The road to the future is still far away. Cheer up and study hard. This allows you to reduce errors and improve efficiency in your subsequent DB career.

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/150984.htm