<pre name="code" class="sql"><pre name="code" class="sql">SQL> select index_name,partition_name,user_stats,status from dba_ind_partitions where index_name='IDX1_T_PM_RANKINFO' and partition_name='RANKINFO_20140101';

INDEX_NAME            PARTITION_NAME            USE STATUS
------------------------------ ------------------------------ --- --------
IDX1_T_PM_RANKINFO          RANKINFO_20140101                NO  USABLE

STATUS :USABLE 表示可用


SQL> select index_name,index_type,partitioned,status from dba_indexes where index_name='IDX1_T_PM_RANKINFO';

INDEX_NAME            INDEX_TYPE       PAR STATUS
------------------------------ --------------------------- --- --------
IDX1_T_PM_RANKINFO          NORMAL           YES  N/A



STATUS: Indicates whether a nonpartitioned index is VALID or UNUSABLE

create index IDX1_T_PM_RANKINFO on T_PM_RANKINFO (MGR_CODE) local;



SQL>  explain plan for select * from T_PM_RANKINFO where data_date='20140101' and mgr_code='57120';

Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------
Plan hash value: 1878632139

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows   | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |     8 |  1176 |     9   (0)| 00:00:01 | |  |
|   1 |  PARTITION LIST SINGLE         |        |     8 |  1176 |     9   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_RANKINFO  |     8 |  1176 |     9   (0)| 00:00:01 |   367 |   367 |
|*  3 |    INDEX RANGE SCAN         | IDX1_T_PM_RANKINFO |     8 |   |     1   (0)| 00:00:01 |   367 |   367 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("MGR_CODE"='57120')

15 rows selected.


删除分区 :


alter table T_PM_RANKINFO truncate  partition RANKINFO_20140101；
SQL> select index_name,partition_name,user_stats,status from dba_ind_partitions where index_name='IDX1_T_PM_RANKINFO' and partition_name='RANKINFO_20140101';

INDEX_NAME            PARTITION_NAME            USE STATUS
------------------------------ ------------------------------ --- --------
IDX1_T_PM_RANKINFO          RANKINFO_20140101         NO  USABLE


分区索引仍旧有效
SQL> select index_name,index_type,partitioned,status from dba_indexes where index_name='IDX1_T_PM_RANKINFO';

INDEX_NAME            INDEX_TYPE       PAR STATUS
------------------------------ --------------------------- --- --------
IDX1_T_PM_RANKINFO          NORMAL           YES N/A

STATUS:

回插数据:

SQL> explain plan for select * from T_PM_RANKINFO where data_date='20140101' and mgr_code='57120';

Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------
Plan hash value: 1878632139

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows   | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |     8 |  1176 |     9   (0)| 00:00:01 | |  |
|   1 |  PARTITION LIST SINGLE         |        |     8 |  1176 |     9   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_RANKINFO  |     8 |  1176 |     9   (0)| 00:00:01 |   367 |   367 |
|*  3 |    INDEX RANGE SCAN         | IDX1_T_PM_RANKINFO |     8 |   |     1   (0)| 00:00:01 |   367 |   367 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("MGR_CODE"='57120')

15 rows selected.

结论:truncate 分区 分区索引没有影响