Databases Reference
In-Depth Information
You can change the status of an existing index to that of an unusable index by specifying the
keyword
unusable
, as shown here:
SQL> alter index emp_email_uk unusable;
Index altered.
SQL>
The following example shows how to make a partition of an index unusable:
SQL> alter index i_emp_name modify partition p2_i_emp ename unusable;
Index altered.
SQL>
The following is an example that shows how to specify one of the partitions of a local index as
UNUSABLE
when you're creating the index.
SQL> create index i_emp_ename ON employees_part (employee_id)
2 local (partition p1_i_emp_ename UNUSABLE, partition
p2_i_emp_ename);
Index created.
SQL>
In this example, the database creates a locally partitioned index with two partitions: p1-
_i_empname and p2_i_empname. However, the index creation statement creates the second partition
(p2_i_empname) as unusable. You can confirm the status of the two index partitions by executing the
following query:
SQL> select index_name as "INDEX OR PARTITION NAME", status
2 from user_indexes
3 where index_name = 'I_EMP_ENAME'
4 union all
5 select partition_name as "INDEX OR PARTITION NAME", status
6 from user_ind_partitions
7 where partition_name like '%I_EMP_ENAME%';
INDEX OR PARTITION NAME STATUS
------------------------------ --------
P1_I_EMP_ENAME UNUSABLE
P2_I_EMP_ENAME USABLE
SQL>
