Databases Reference
In-Depth Information
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REPORTING_INDEX"
This code shows all of the aspects of the index that would be required to re-create it. Many of these
values reflect default settings or storage parameters that were inherited from the index tablespace.
If you want to display all index metadata for the currently connected user, run the following code:
SQL> select dbms_metadata.get_ddl('INDEX', index_name) from user_indexes;
If the currently connected user has many indexes, this query will produce a great deal of output.
Dropping a B-tree Index
If you determine that you aren't using an index anymore, then it should be dropped. Before you drop an
index, take the necessary precautions to ensure that there won't be an adverse impact on performance.
If possible, the best way to determine adverse performance implications is by dropping an index in a test
environment that reflects the production environment (in terms of hardware, data, load, and so on). If
it's not possible to thoroughly test, then consider doing one of the following before dropping:
•
Enable monitoring for the index.
•
Make the index invisible.
Make the index unusable.
The idea is to try to determine beforehand that the index is not used for any purpose before actually
dropping it. See Chapter 7 for details on monitoring an index. Monitoring an index will give you an idea
if the application is using it for
SELECT
statements. Index monitoring will not tell you if the index is used
for other internal purposes, like enforcing a constraint or preventing locking issues.
Making an index invisible requires Oracle Database 11g. An invisible index is still maintained by
Oracle but isn't considered by the query optimizer when determining the execution plan. Be aware that
an invisible index may still be used internally by Oracle to prevent locking issues or to enforce
constraints. So just making an index invisible isn't a completely reliable way to determine if it's used.
Here's an example of making an index invisible:
•
SQL> alter index addr_fk1 invisible;
This code makes the index invisible to the query optimizer so that it can't be used to retrieve rows for a
query. However, the index structure is still maintained by Oracle as records are modified in the table. If
you determine that the index was critical for performance, you can easily make it visible to the optimizer
again via
SQL> alter index addr_fk1 visible;
Your other option before dropping an index is to make it unusable.
SQL> alter index addr_fk1 unusable;
This code renders the index unusable, but doesn't drop it. Unusable means that the optimizer won't use
the index and Oracle won't maintain the index as DML statements operate on its table. Furthermore, an
unusable index can't be used internally to enforce constraints or prevent locking issues.
