Databases Reference
In-Depth Information
When creating an index, we recommend that you place the index in a tablespace separate from its
corresponding table. This allows you more options for storage management and backup and recovery.
This isn't a hard-and-fast rule, but rather a guideline to help with maintenance and manageability.
B-tree indexes are closely associated with primary key and unique key constraints. Oracle will
automatically create a B-tree index for you when the primary key or unique key constraint is created.
When an index is automatically created with the constraint, the index is also automatically dropped
when you drop or disable the constraint. You have the option of creating the index and constraint
separately if you are required to manage these two objects separately.
Oracle doesn't automatically create an index for you when creating a foreign key constraint. Indexes
on foreign key columns must be manually created. It's usually advisable to create an index on foreign
key columns as this helps prevent locking issue and helps with the performance of queries that join
parent/child tables on primary key and foreign key columns.
