Databases Reference
In-Depth Information
If you need to re-enable an unusable index, then you'll have to rebuild it. Be aware that rebuilding a
large index can take a considerable amount of time and resources.
SQL> alter index addr_fk1 rebuild;
After you're sure an index isn't required, use the DROP INDEX statement to remove it. This
permanently drops the index. The only way to get the index back is to re-create it.
SQL> drop index addr_fk1;
Managing B-tree Indexes with Constraints
B-tree indexes and primary key and unique key constraints are inseparable. This is because Oracle uses
these indexes to enforce primary key and unique key constraints. You can't have an enabled primary key
or unique key constraint without an associated B-tree index.
When you create a primary key or unique key constraint, you have the option of having Oracle
automatically create the corresponding index. In this scenario, if you drop or disable the constraint,
Oracle will also automatically drop the corresponding index.
You can also create the index separately from the constraint. When you create the index and
constraint separately, this allows you to drop or disable the constraint without automatically dropping
the corresponding index. If you work with big data, you may want the flexibility of disabling a constraint
without dropping the corresponding index.
Oracle doesn't automatically create an index when a foreign key constraint is defined, so you must
manually create an index on columns associated with a foreign key constraint. In most scenarios it's
beneficial to create a B-tree index on foreign key columns because it helps prevent locking issues and
assists with performance of queries that join parent/child tables via the primary key and foreign key
columns.
Figure 2-2 displays the various decision paths associated with creating indexes associated with
constraints. Refer back to this diagram as you read through the following sections dealing with indexes
related to primary keys, unique keys, and foreign keys.
