全局索引失效带来的几个测试场景

来源：这里教程网时间：2026-03-03 19:00:23 作者：

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 ；