New Features of Oracle11g: invisibleindex (invisible index)
How do you feel if a table has more than a dozen indexes? Obviously, it will slow down the process of adding, deleting, and modifying data. Do not expect developers to build indexes. My solution is to monitor the indexes for a long period of time before deleting them. But after deletion, if one day is found to be useful, it will be re-created. If it is a large table, it will be a little troublesome. Now the 11g provides a new feature that is invisible to indexes. You can set indexes to invisible indexes. CBO ignores these indexes when evaluating the execution plan. If necessary, set them back.
There is another purpose. When debugging an SQL statement, you need to create an index for testing, and you do not want to affect other sessions, it is time to use invisible indexes.
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
Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 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 |
Bytes -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 10)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL * Net to client
337 bytes encoded ed 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)
Statistics
----------------------------------------------------------
196 recursive cballs
0 db block gets
567 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL * Net to client
337 bytes encoded ed 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)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
544 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL * Net to client
337 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- To make the database see invisible indexes, 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
Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 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 |
Bytes -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 10)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL * Net to client
337 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
