What do you feel if you have more than 10 indexes on a single table? Obviously, it will slow down the rate of increase, deletion and change. Don't expect developers to be able to build indexes. My approach is to monitor a very long period of time first. See which indexes are not practical to, and then delete.
But after deletion, suppose to find a day practical, and then again built, assuming that the big table. That's a bit of a problem. Today 11g provides a new feature, Invisible Index. The ability to build indexes is set to an invisible index. The CBO ignores it when it evaluates the run-time plan, assuming it is needed. Set it back and you can.
Another use, you are debugging an SQL statement, to build an index test. And you don't want to affect other sessions, with an invisible index just in 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;
Run a 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;
Run a 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;
Run a 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
--Allows the database to see an invisible index, by changing a number of parameters
sql> alter session set optimizer_use_invisible_indexes = true;
Sql> SELECT * FROM test where object_id = 10;
Run a 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
Oracle 11g new features invisible index (non-visible indexes)
