Databases Reference
In-Depth Information
You can verify that the index is being used by setting
AUTOTRACE
to
on
and running the
SELECT
statement:
SQL> set autotrace trace explain;
SQL> select cust_id from cust where cust_id = 3;
Here is some sample output, indicating that the optimizer chose to use the invisible index:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| CUST_IDX1 | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Keep in mind that
invisible index
simply means an index the optimizer can't see. Just like any other index, an
invisible index consumes space and resources during DML statements.
Maintaining Indexes
As applications age, you invariably have to perform some maintenance activities on existing indexes. You may need
to rename an index to conform to newly implemented standards, or you may need to rebuild a large index to move
it to a different tablespace that better suits the index's storage requirements. The following list shows common tasks
associated with index maintenance:
•
Renaming an index
•
Displaying the DDL for an index
•
Rebuilding an index
•
Setting indexes to
unusable
Monitoring an index
•
•
Dropping an index
Each of these items is discussed in the following sections.
Renaming an Index
Sometimes you need to rename an index. The index may have been erroneously named when it was created, or
perhaps you want a name that better conforms to naming standards. Use the
ALTER INDEX...RENAME TO
statement
to rename an index:
SQL> alter index cust_idx1 rename to cust_index1;
You can verify that the index was renamed by querying the data dictionary:
select
table_name
,index_name
,index_type
,tablespace_name
,status
from user_indexes
order by table_name, index_name;
