Databases Reference
In-Depth Information
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">10)
SQL>
Once the index is usable again, the database uses the index. As the two previous examples show,
when dealing with a nonpartitioned index, the optimizer ignores an unusable index. In the case of a
partitioned index, the database ignores the index if the optimizer can't determine at query compile time
that any of the index partitions can be pruned. However, you can override the default behavior of the
database where it ignores an unusable index by specifying an
index
hint in the query.
Now check what happens when you set the
skip_unusable_indexes parameter
to
FALSE
and execute
the same query, after first rendering the index unusable.
SQL> alter system set skip_unusable_indexes=false;
System altered.
SQL>
SQL> alter index test_idx1 unusable;
Index altered.
SQL>
SQL> select * from test where id > 10;
select * from test where id > 10
*
ERROR at line 1:
ORA-01502: index 'HR.TEST_IDX1' or partition of such index is in unusable state
SQL>
This example shows how when you set the
skip_unusable_indexes
parameter to
FALSE
, the database
issues an error when any statement tries to use the index in a
SELECT
statement. This is true also when a
statement tries to update the index. The database won't permit any
insert, update,
or
delete
operations on a table with an unusable index or index partition(s).
Oracle database will let you perform your
select
(or
insert
/
delete
/
update
) operations on the table
as long as you set the
skip_unusable_indexes parameter
to
TRUE
. However, this is applicable only to non-
unique indexes. Let's see what happens when you're dealing with an unique index.
SQL> drop index test_idx1;
Index dropped.
SQL> create unique index test_idx1 on test(id);
Index created.
