oracle 分区表删除分区或是删除分区表数据时,导致索引失效

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('2016-01-01','yyyy-mm-dd')),

  5 partition p1 values less than (to_date('2017-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.

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(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

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

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

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

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

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

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

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

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

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

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

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

                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

7 rows selected.

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.

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

SQL> alter table test_partas truncate partition p0;

Table truncated.

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

SQL> alter index PK_ID rebuild online;

Index altered.

SQL> alter index IND_ACCOUNT_ID rebuild online;

Index 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 VALID

IND_ACCOUNT_ID TEST_PARTAS VALID

SQL> alter table test_partas truncate partition p1 update indexes;

Table truncated.

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