If there are more than 10 indexes on a table, how do you feel? Obviously it will slow down, delete, change the speed, do not expect developers to build index. My approach is to monitor a long period of time to see which indexes are not used, and then delete them. But after the deletion, if found a day useful, and then re-build, if it is a big table, it is a bit of trouble. Now 11g provides a new feature, an invisible index, which can be indexed to an invisible index, and the CBO ignores it when evaluating the execution plan and, if necessary, setting it back.
There is also a purpose, you are debugging an SQL statement, to build an index test, and you do not want to affect other sessions, with an invisible index is the right time.
sql> drop table test purge;
Sql> CREATE TABLE Test as SELECT * from Dba_objects;
Sql> CREATE index ind_t_object_id on test (object_id);
sql> exec dbms_stats.gather_table_stats (user, ' test ', cascade = TRUE);
Sql> Set Autotrace traceonly
Sql> SELECT * FROM test where object_id = 10;
Execution plan
----------------------------------------------------------
Plan Hash value:255872589
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| TEST | 1 | 100 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | ind_t_object_id | 1 | | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("object_id" =10)
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
4 consistent gets
0 physical Reads
0 Redo Size
1195 Bytes sent via sql*net to client
337 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
sql> alter index ind_t_object_id invisible;
Sql> SELECT * FROM test where object_id = 10;
Execution plan
----------------------------------------------------------
Plan Hash value:1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 209 (1) | 00:00:03 |
|* 1 | TABLE ACCESS full| TEST | 1 | 100 | 209 (1) | 00:00:03 |
--------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-filter ("object_id" =10)
Statistical information
----------------------------------------------------------
196 Recursive calls
0 db Block gets
567 consistent gets
0 physical Reads
0 Redo Size
1195 Bytes sent via sql*net to client
337 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
6 Sorts (memory)
0 Sorts (disk)
1 rows processed
Sql> Select/*+ Index (Test ind_t_object_id) */* from Test where object_id = 10;
Execution plan
----------------------------------------------------------
Plan Hash value:1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 209 (1) | 00:00:03 |
|* 1 | TABLE ACCESS full| TEST | 1 | 100 | 209 (1) | 00:00:03 |
--------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-filter ("object_id" =10)
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
544 consistent gets
0 physical Reads
0 Redo Size
1195 Bytes sent via sql*net to client
337 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
--Let the database see the invisible Index, you can change a parameter
sql> alter session set optimizer_use_invisible_indexes = true;
Sql> SELECT * FROM test where object_id = 10;
Execution plan
----------------------------------------------------------
Plan Hash value:255872589
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| TEST | 1 | 100 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | ind_t_object_id | 1 | | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("object_id" =10)
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
4 consistent gets
0 physical Reads
0 Redo Size
1195 Bytes sent via sql*net to client
337 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
