Databases Reference
In-Depth Information
on a table, with the
drop index
command. For example, you can only drop any index that the database
has created to support a unique key or a primary key constraint by dropping (or disabling) the constraint
itself. Here's what happens when you try to drop an index that supports a primary key constraint:
SQL> drop index test_pk1;
drop index test_pk1
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>
In order to drop a constraint, issue the
drop constraint
command, as shown here:
SQL> alter table test
2 drop constraint test_pk1;
Table altered.
SQL>
If you drop a table, the database drops all indexes defined on that table as well.
The Hazards of Dropping an Index
While the
V$OBJECT_USAGE
view tells you if an index has been used or not, be leery about dropping an
index just because the
INDEX_USAGE
column shows a value of NO
.
There could very well be unexpected
side effects of dropping or modifying a multi-column composite index. Several writers have
demonstrated that Oracle can potentially use an index for a sanity check, even if the index itself remains
"unused"! Starting with Oracle Database 11g, Oracle uses certain index statistics even when it doesn't
use the index per se in retrieving a query's output. For example, if you create a composite index on two
columns that are related, Oracle can potentially arrive at different results with and without the presence
of the index, even if it doesn't use the index. There is some evidence that Oracle uses the
DISTINCT_KEYS
index statistic to determine the correct selectivity and the related cardinality estimates for a query. So, if
you drop an index because your index monitoring shows that the index isn't being used, the optimizer
could potentially lose vital information it needs to estimate the selectivity and cardinality of the indexed
columns.
Finally, as Chapter 5 explains, using Oracle's invisible indexes feature is quite often a smarter
alternative to just dropping an index.
