Required keyword to build index while leaving underlying table available.
Making an index invisible requires Oracle Database 11g. An invisible index is still maintained by Oracle but is not 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 is not a completely reliable way to determine if it is used. Here is 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 canot 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 by means of
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 does not drop it. Unusable means that the optimizer will not use the index and Oracle will not maintain the index as DML statements operate on its table.
Furthermore, an unusable index canot be used internally to enforce constraints or prevent locking issues.