Databases Reference
In-Depth Information
The following query shows how the database allocates storage only for the usable index:
SQL> select p.partition_name, p.status as "part_status",
2 p.segment_created as "seg_created",
3 from user_ind_partitions p, user_segments s
4 where s.segment_name = 'I_EMP_ENAME';
PARTITION_NAME PART_STA SEG_CREATED
-------------- -------- -----------
P2_I_EMP_ENAME USABLE YES
P1_I_EMP_ENAME UNUSABLE NO
SQL>
Regardless of whether the index is partitioned or not, once you make an index or part of a
partitioned index unusable, the segment that the index occupied will no longer exist. The following
query confirms this:
SQL> select segment_name,bytes from dba_segments
where segment_name in ('HR.I_EMP_ENAME','HR.EMP_EMAIL_UK');
no rows selected
SQL>
Once you mark an index
UNUSABLE
, you must rebuild it in order to mark it usable again, as shown in
the following examples:
SQL> alter index EMP_GLOBAL_HASH_IDX modify partition P2 unusable;
Index altered.
SQL> alter index EMP_GLOBAL_HASH_IDX rebuild partition P2;
Index altered.
SQL>
Specifying the SKIP_UNUSABLE_INDEXES Parameter
How the database handles an unusable index depends on the setting of the
skip_unusable_indexes
parameter. By default, the
skip_unusable_indexes
parameter is set to
TRUE
, meaning that when the
database encounters an unusable index, it simply ignores it and doesn't issue an error. The
skip_unusable_indexes
parameter, when set to
TRUE
, allows you perform inserts, deletes, updates, and
selects on a table with an unusable index or an index partition(s). Any DML statements you issue against
the unusable index will work fine, but the database stops maintaining the index. You can check the
skip_unusable_indexes
parameter's value thus:
SQL> show parameter skip_unusable_indexes
NAME TYPE VALUE
------------------------------------ ----------- ---------
skip_unusable_indexes boolean TRUE
SQL>
