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 ；

全域性索引失效帶來的幾個測試場景

相關文章