Shulou(Shulou.com)05/31 Report--

Editor to share with you about the Partition table partition table to delete partition data caused by index failure, I believe that most people do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

I. description

There is a small task is to delete some data, , first have a little fun. Because the data table to be deleted is the partition table I converted to before. The partition table is partitioned according to the creation time field, which is 1 partition per quarter. So now I want to delete the data before July 1, 2017 (the amount of data is about 10 million). You can delete the table partition data directly. If I were to use delete to delete so much data, I would have to write stored procedures and submit them in batches. It is such a simple truncate partition that causes subsequent business failures. Finally, the index of the table is invalid and restored after re-establishment. How sweaty!

2. 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 recordings))

4 (partition P0 values less than (TO_DATE ('2016-01-01-01-01))

5 partition p1 values less than (to_date ('2017-01-01-01-01)

Table created.

SQL > insert into TEST_PARTAS select t. Idret from act_test t. Createroomtime comp.

3483178 rows created.

SQL > commit

Commit complete.

SQL > EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS ('SAM','TEST_PARTAS')

PL/SQL procedure successfully completed.

two。 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.tableroomnamerepart.partitionroomnameretem.numcocorowsrewriting.blocksrecovert.intervalrecot.highlights value from USER_TAB_PARTITIONS t

TABLE_NAME PARTITION_ NUM_ROWS BLOCKS INT HIGH_VALUE

TEST_PARTAS P0 6046 NO TO_DATE ('2016-01-01 6046 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS P1 616290 36506 NO TO_DATE ('2017-01-01 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1611 44829 4030 YES TO_DATE ('2017-04-01 00 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1612 21706 3022 YES TO_DATE ('2017-07-01 00 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1613 172525 3022 YES TO_DATE ('2017-10-01 00 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1614 442435 2014 YES TO_DATE ('2018-01-01 00 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS SYS_P1615 3277 YES TO_DATE ('2018-04-01 00 SYYYY-MM-DD HH24:

MI:SS', 'NLS_CALENDAR=GREGORIAN')

7 rows selected.

3. Create primary keys and indexes

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.INDEXNAMEMagna T.TABLENAMEMagazine T.STATUS from user_indexes t where t.tableharmnamebooks testworthy parts'

INDEX_NAME TABLE_NAME STATUS

-

PK_ID TEST_PARTAS VALID

IND_ACCOUNT_ID TEST_PARTAS VALID

5. Delete p0 partition data with truncate without adding update index parameter

SQL > alter table test_partas truncate partition p0

Table truncated.

6. Check index status, status is not available

SQL > select T.INDEXNAMEMagna T.TABLENAMEMagazine T.STATUS from user_indexes t where t.tableharmnamebooks testworthy parts'

INDEX_NAME TABLE_NAME STATUS

-

PK_ID TEST_PARTAS UNUSABLE

IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

7. Re-index, 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 status of the index, and the index returns to its normal available state

SQL > select T.INDEXNAMEMagna T.TABLENAMEMagazine T.STATUS from user_indexes t where t.tableharmnamebooks testworthy parts'

INDEX_NAME TABLE_NAME STATUS

-

PK_ID TEST_PARTAS VALID

IND_ACCOUNT_ID TEST_PARTAS VALID

9. Delete p1 partition data with truncate and add update index parameters

SQL > alter table test_partas truncate partition p1 update indexes

Table truncated.

10. Check the index status, when the index is normally available

SQL > select T.INDEXNAMEMagna T.TABLENAMEMagazine T.STATUS from user_indexes t where t.tableharmnamebooks testworthy parts'

INDEX_NAME TABLE_NAME STATUS

-

PK_ID TEST_PARTAS VALID

IND_ACCOUNT_ID TEST_PARTAS VALID

III. Expansion

With this question, let's expand again, if drop partitions will also affect the index, the answer is yes, delete the partition, the index is still invalid.

SQL > alter table test_partas drop partition SYS_P1611

Table altered.

SQL > select T.INDEXNAMEMagna T.TABLENAMEMagazine T.STATUS from user_indexes t where t.tableharmnamebooks testworthy parts'

INDEX_NAME TABLE_NAME STATUS

-

PK_ID TEST_PARTAS UNUSABLE

IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

The above is all the contents of the article "what to do when the index fails when the Partition table partition table deletes the partition data". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.