SQL> create table xue.test_pp(id number,name varchar2(10)) partition by range (id) (partition pa values less than (10),partition pb values less than (20),partition pc values less than (30));
Table created.
SQL> insert into xue.test_pp values (1,’a');
1 row created.
SQL> insert into xue.test_pp values (11,’aa’);
1 row created.
SQL> insert into xue.test_pp values (22,’bb’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xue.test_pp;
        ID NAME  
———- ———-    
         1 a    
        11 aa    
        22 bb

SQL> alter table xue.test_pp add constraint u_pp unique (id);
Table altered.
SQL> select index_name,status,partitioned from dba_indexes where table_name=’TEST_PP’;  
U_PP                           VALID    NO
普通DML不会导致索引失效：    
SQL> insert into  xue.test_pp values (23,’cc’);
1 row created.
SQL> commit;
Commit complete.
SQL> select index_name,status,partitioned from dba_indexes where table_name=’TEST_PP’;  
U_PP                           VALID    NO
SQL> update xue.test_pp set name=’bc’ where id=23;
1 row updated.
SQL> commit;
Commit complete.
SQL> select index_name,status,partitioned from dba_indexes where table_name=’TEST_PP’;  
U_PP                           VALID    NO
SQL> delete from xue.test_pp where id=23;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select index_name,status,partitioned from dba_indexes where table_name=’TEST_PP’;  
U_PP                           VALID    NO
tuncate 一个分区则导致索引无效：    
SQL> select * from xue.test_pp;    
         1 a    
        11 aa    
        22 bb
SQL> alter table xue.test_pp truncate partition pc;
Table truncated.
SQL> select * from xue.test_pp;  
         1 a    
        11 aa
SQL> select index_name,status,partitioned from dba_indexes where table_name=’TEST_PP’;  
U_PP                           UNUSABLE NO
在这种情况下做INSERT/DELETE操作会报错：    
SQL> insert into xue.test_pp values(23,’dd’);    
insert into xue.test_pp values(23,’dd’)    
*    
ERROR at line 1:    
ORA-01502: index ‘XUE.U_PP’ or partition of such index is in unusable state
即使是插入到没有TRUNCATE的分区也报错：  
SQL> insert into xue.test_pp values(12,’dd’);    
insert into xue.test_pp values(12,’dd’)    
*    
ERROR at line 1:    
ORA-01502: index ‘XUE.U_PP’ or partition of such index is in unusable state
–DELETE报错：  
SQL> delete from xue.test_pp where id=11;    
delete from xue.test_pp where id=11    
*    
ERROR at line 1:    
ORA-01502: index ‘XUE.U_PP’ or partition of such index is in unusable state

不过做UPDATE不会报错：  
SQL> update xue.test_pp set name=’bc’ where id=11;
1 row updated.
创建一个local索引：    
SQL> create unique index xue.UUU on xue.test_pp(id) local;    
create unique index xue.UUU on xue.test_pp(id) local    
                                           *    
ERROR at line 1:    
ORA-01408: such column list already indexed
SQL> create unique index xue.UUU on xue.test_pp(id,name) local;
Index created.
SQL> select index_name,status,partitioned from dba_indexes where table_name=’TEST_PP’;  
UUU                            N/A      YES    
U_PP                           UNUSABLE NO
SQL> select index_name,status from dba_ind_partitions where INDEX_name=’UUU’;    
UUU                            USABLE    
UUU                            USABLE    
UUU                            USABLE
在全局索引、本地索引共存的情况下仍然报错：  
SQL> delete from xue.test_pp where id=11;    
delete from xue.test_pp where id=11    
*    
ERROR at line 1:    
ORA-01502: index ‘XUE.U_PP’ or partition of such index is in unusable state
删除原来的索引后就不再报错：    
SQL> alter table xue.test_pp disable constraint u_pp;
Table altered.
SQL> delete from xue.test_pp where id=11;
1 row deleted.
在这种情况下，观察语句的执行计划：    
update xue.test_pp set name=’cce’ where id=13;
select * from v$sqlarea where sql_text like ‘%test_pp%’
select * from table(dbms_xplan.display_cursor(‘12jr4j8vzx5pw’));
SQL_ID  12jr4j8vzx5pw, child number 0    
————————————-    
update xue.test_pp set name=’cce’ where id=13    
  
Plan hash value: 935868745    
  
—————————————————————————————————    
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    
—————————————————————————————————    
|   0 | UPDATE STATEMENT        |         |       |       |     1 (100)|          |       |       |    
|   1 |  UPDATE                 | TEST_PP |       |       |            |          |       |       |    
|   2 |   PARTITION RANGE SINGLE|         |     1 |    20 |     1   (0)| 00:00:01 |     2 |     2 |    
|*  3 |    INDEX RANGE SCAN     | UUU     |     1 |    20 |     1   (0)| 00:00:01 |     2 |     2 |    
—————————————————————————————————    
  
Predicate Information (identified by operation id):    
—————————————————    
  
   3 – access("ID"=13)    
  
Note    
—–    
   – dynamic sampling used for this statement
该操作使用了新索引：UUU，同时使用了INDEX RANGE SCAN
创建原来的索引：  
SQL> alter table xue.test_pp enable constraint u_pp;
Table altered.
SQL> select index_name from dba_indexes where table_name=’TEST_PP’;  
UUU    
U_PP
这种情况下该语句的执行计划变为：  
SQL_ID  7crj9fgwu7kfw, child number 0    
————————————-    
update xue.test_pp set name=’eeee’ where id=13    
  
Plan hash value: 2992425951    
  
——————————————————————-    
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)|    
——————————————————————-    
|   0 | UPDATE STATEMENT   |         |       |       |     1 (100)|    
|   1 |  UPDATE            | TEST_PP |       |       |            |    
|*  2 |   INDEX UNIQUE SCAN| U_PP    |     1 |    20 |     0   (0)|    
——————————————————————-    
  
Predicate Information (identified by operation id):    
—————————————————    
  
   2 – access("ID"=13)
可以看到，使用U_PP索引时是INDEX UNIQUE SCAN。
修改测试语句的where条件：    
update xue.test_pp set name=’ffff’ where id=13 and name=’eeee’;
SQL_ID  8r665dj5174n0, child number 0    
————————————-    
update xue.test_pp set name=’ffff’ where id=13 and name=’eeee’    
  
Plan hash value: 1843624919    
  
—————————————————————————————-    
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    
—————————————————————————————-    
|   0 | UPDATE STATEMENT        |         |       |       |     1 (100)|       |       |    
|   1 |  UPDATE                 | TEST_PP |       |       |            |       |       |    
|   2 |   PARTITION RANGE SINGLE|         |     1 |    20 |     0   (0)|     2 |     2 |    
|*  3 |    INDEX UNIQUE SCAN    | UUU     |     1 |    20 |     0   (0)|     2 |     2 |    
—————————————————————————————-    
  
Predicate Information (identified by operation id):    
—————————————————    
  
   3 – access("ID"=13 AND "NAME"=’eeee’)    
  
–删掉索引  
SQL> drop index XUE.UUU    
  2  ;
Index dropped.
update xue.test_pp set name=’aaaaaa’ where id=13 and name=’ffff’;
Execution Plan  
———————————————————-    
Plan hash value: 714092732
—————————————————————————————————-  
———–
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Psta  
rt| Pstop |
—————————————————————————————————-  
———–
|   0 | UPDATE STATEMENT                    |         |     1 |     6 |     1   (0)| 00:00:01 |  
  |       |
|   1 |  UPDATE                             | TEST_PP |       |       |            |          |  
  |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TEST_PP |     1 |     6 |     1   (0)| 00:00:01 |  
2 |     2 |
|*  3 |    INDEX UNIQUE SCAN                | U_PP    |     1 |       |     0   (0)| 00:00:01 |  
  |       |
—————————————————————————————————-  
———–
Predicate Information (identified by operation id):    
—————————————————
   2 – filter("NAME"=’ffff’)  
   3 – access("ID"=13)
–使用了索引U_PP，不过仍然是INDEX UNIQUE SCAN

测试结论  
1、truncate 操作会导致全局索引失效    
2、如果全局索引失效，即使有其他索引可用，该表也不能进行INSERT\DELETE，可以UPDATE。    
3、假设有索引1（索引列为id1），索引2（索引列为id1，id2），且都是unique索引，则：    
–只有索引2的情况下：    
  以id1为where条件的语句会使用索引2，但是其索引模式为：INDEX RANGE SCAN；    
  以id1，id2为where条件的语句会使用索引2，并且其索引模式为：INDEX UNIQUE SCAN ；    
–只有索引1的情况下：    
以id1为where条件，或以id1，id2为条件，都会使用索引1，并且其索引模式为：INDEX UNIQUE SCAN ；    
–2个索引都存在的情况下：    
以id1为where条件的语句会使用索引1，并且其索引模式为：INDEX UNIQUE SCAN ；    
以id1，id2为where条件的语句会使用索引2，并且其索引模式为：INDEX UNIQUE SCAN ；