Oracle索引的四种状态1 索引有四种状态，VALID 、UNUSABLE、USABLE、N/A，为了查明索引的四种状态的关系，做以下相关实验。2 创建普通的测试表，并创建索引，查看索引状态创建并并插入数据 SYS@orcl>create table test_tab_ind (id number,name varchar2(30)); Table created. SYS@orcl>insert into test_tab_ind values(1,'xsc1'); 1 row created. SYS@orcl>insert into test_tab_ind values(2,'xsc2'); 1 row created. SYS@orcl>insert into test_tab_ind values(3,'xsc3'); 1 row created. SYS@orcl>insert into test_tab_ind values(4,'xsc4'); 1 row created. SYS@orcl>insert into test_tab_ind values(5,'xsc5'); 1 row created. SYS@orcl>commit; Commit complete. 创建索引： SYS@orcl>create index test_tab_ind_id on test_tab_ind(id); Index created. 查看所有状态 select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='TEST_TAB_IND'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------------------- -------- SYS TEST_TAB_IND_ID TEST_TAB_IND VALID 3 更改普通索引的状态：SYS@orcl>alter index test_tab_ind_id disable;alter index test_tab_ind_id disable*ERROR at line 1:ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option 根据如上报错，Oracle有如下解释： ENABLE Clause Enable applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true: The function is currently valid The signature of the current function matches the signature of the function when the index was created The function is currently marked as DETERMINISTIC Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE. DISABLE Clause DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword. UNUSABLE Clause Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it. Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table. 根据如上信息，执行如下命令，修改索引状态： SYS@orcl>alter index test_tab_ind_id UNUSABLE ; Index altered. SYS@orcl>select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='TEST_TAB_IND'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------------------- -------- SYS TEST_TAB_IND_ID TEST_TAB_IND UNUSABLE alter index test_tab_ind_id rebuild ; SYS@orcl>alter index test_tab_ind_id rebuild ; Index altered. SYS@orcl>select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='TEST_TAB_IND'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------------------- -------- SYS TEST_TAB_IND_ID TEST_TAB_IND VALID 4 创建分区表，并创建索引，查看索引状态： 分区表及分区索引： create table partition_ind_test(id number(6),time_id date,name varchar2(30)) partition by range (time_id) (partition p0 values less than (to_date('2016-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), partition p1 values less than (to_date('2018-05-23 00:00:00','yyyy-mm-dd hh24:mi:ss')), partition p2 values less than (to_date('2020-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss')) ); Table created. 插入数据： SYS@orcl>insert into partition_ind_test values(1,'2016-05-01 10:01:00','xsc1'); 1 row created. SYS@orcl>insert into partition_ind_test values(2,'2017-05-01 10:01:00','xsc2'); 1 row created. SYS@orcl>insert into partition_ind_test values(3,'2020-05-01 10:01:00','xsc3'); 1 row created. SYS@orcl>commit; Commit complete. SYS@orcl>select * from partition_ind_test; ID TIME_ID NAME ---------- ------------------- ------------------------------ 1 2016-05-01 10:01:00 xsc1 2 2017-05-01 10:01:00 xsc2 3 2020-05-01 10:01:00 xsc3 创建普通的全局索引： SYS@orcl>create index ind_global_test on partition_ind_test(id) global; Index created. 查看索引状态： SYS@orcl>select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='PARTITION_IND_TEST'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------------------- -------- SYS IND_GLOBAL_TEST PARTITION_IND_TEST VALID 创建 local 分区索引，查看所有状态 SYS@orcl> create index ind_global_test_time_id on partition_ind_test(TIME_ID) local; Index created. SYS@orcl>select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='PARTITION_IND_TEST'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- -------------------- -------------------- -------- SYS IND_GLOBAL_TEST PARTITION_IND_TEST VALID SYS IND_GLOBAL_TEST_TIME_ID PARTITION_IND_TEST N/A 查看分区索引的状态 SYS@orcl>select ind.owner,ind.index_name,ind_p.PARTITION_NAME,ind_p.STATUS from dba_ind_partitions ind_p,dba_indexes ind where ind_p.index_name=ind.index_name and ind.TABLE_NAME='PARTITION_IND_TEST'; OWNER INDEX_NAME PARTITION_NAME STATUS -------------------- ----------------------------------- ------------------------------ -------- SYS IND_GLOBAL_TEST_TIME_ID P0 USABLE SYS IND_GLOBAL_TEST_TIME_ID P1 USABLE SYS IND_GLOBAL_TEST_TIME_ID P2 USABLE 5 清空分区表的数据，查看分区表索引的状态 SYS@orcl>alter table PARTITION_IND_TEST truncate partition p1; Table truncated. SYS@orcl>select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='PARTITION_IND_TEST'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- ----------------------------------- -------------------- -------- SYS IND_GLOBAL_TEST PARTITION_IND_TEST UNUSABLE SYS IND_GLOBAL_TEST_TIME_ID PARTITION_IND_TEST N/A SYS@orcl>select ind.owner,ind.index_name,ind_p.PARTITION_NAME,ind_p.STATUS from dba_ind_partitions ind_p,dba_indexes ind where ind_p.index_name=ind.index_name and ind.TABLE_NAME='PARTITION_IND_TEST'; OWNER INDEX_NAME PARTITION_NAME STATUS -------------------- ----------------------------------- ------------------------------ -------- SYS IND_GLOBAL_TEST_TIME_ID P0 USABLE SYS IND_GLOBAL_TEST_TIME_ID P1 USABLE SYS IND_GLOBAL_TEST_TIME_ID P2 USABLE 根据如上信息，可以确定全局索引失效，分区索引的状态正常，查看Oracle文档，说明local index数据库会自动维护，故分区索引状态正常。 以下为Oracle官方文档的解释：
Local Partitioned Indexes
Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This functionality enables Oracle to automatically keep the index partitions synchronized with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.
Local partitioned indexes support more availability when there are partition or subpartition maintenance operations on the table. A type of index called a local nonprefixed index is very useful for historical databases. In this type of index, the partitioning is not on the left prefix of the index columns. For more information about prefixed indexes, refer to "Index Partitioning".
You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. 重新rebuild全局索引，全局索引状态正常。 SYS@orcl>alter index IND_GLOBAL_TEST rebuild; Index altered. SYS@orcl>select owner,index_name,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='PARTITION_IND_TEST'; OWNER INDEX_NAME TABLE_NAME STATUS -------------------- ----------------------------------- -------------------- -------- SYS IND_GLOBAL_TEST PARTITION_IND_TEST VALID SYS IND_GLOBAL_TEST_TIME_ID PARTITION_IND_TEST N/A 根据如上信息，valid、unusable 对应普通的索引，N/A 对应的为local partition index,故还需要查看子分区的状态是否为 USABLE. valid (普通索引、全局索引) -> unusable usable （分区索引） -> unusable N/A （local partition index) -> usable \ unusable
