Visibility and hiding of Oracle Indexes)

Visibility and hiding of Oracle Indexes)

Official Document: Making an Index Invisible

An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. making an index invisible is an alternative to making it unusable or dropping it. you cannot make an individual index partition invisible. attempting to do so produces an error.

Explanation: an invisible index is ignored in the OPTIMIZER_USE_INVISIBLE_INDEXES unless you manually set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Marking invisible indexes can replace unavailable indexes or delete indexes. You cannot make the partition index invisible. An error occurs when you try to do so.

Test:

1. Create a test table ti based on the dba_objects table.

Scott @ ORCL> create table ti as select * from dba_objects;

Table created.

Scott @ ORCL> select count (*) from ti;

COUNT (*)

----------

72799

2. Create an index ind_ti Based on the object_id Column

Scott @ ORCL> create index ind_ti on ti (object_id );

Index created.

3. Adjusted to view the execution plan

Scott @ ORCL> set autot trace exp
 

4. test whether the index takes effect and the result index is properly applied.

Scott @ ORCL> select * from ti where object_id = 20;

Execution Plan

----------------------------------------------------------

Plan hash value: 1655810896

Bytes --------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes --------------------------------------------------------------------------------------

| 0 | select statement | 1 | 207 | 2 (0) | 00:00:01 |

| 1 | table access by index rowid | TI | 1 | 207 | 2 (0) | 00:00:01 |

| * 2 | index range scan | IND_TI | 1 | 1 (0) | 00:00:01 |

Bytes --------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

2-access ("OBJECT_ID" = 20)

Note

-----

-Dynamic sampling used for this statement (level = 2)

 

5. Change the ind_ti index to invisible.

Scott @ ORCL> alter index ind_ti invisible;
 

6. The index is not used during another test.

Scott @ ORCL> select * from ti where object_id = 20;

Execution Plan

----------------------------------------------------------

Plan hash value: 798420002

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

--------------------------------------------------------------------------

| 0 | select statement | 12 | 2484 | 291 (1) | 00:00:04 |

| * 1 | table access full | TI | 12 | 2484 | 291 (1) | 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

1-filter ("OBJECT_ID" = 20)

Note

-----

-Dynamic sampling used for this statement (level = 2)

7. data modification

Scott @ ORCL> insert into ti select * from ti;

72799 rows created.

Scott @ ORCL> insert into ti select * from ti;

145598 rows created.

Scott @ ORCL> select count (*) from ti;

COUNT (*)

----------

291196

8. Change the index to the visible state.

Scott @ ORCL> alter index ind_ti visible;

Index altered.

9. test whether the index takes effect and the result index is properly applied.

Scott @ ORCL> set autot trace exp

Scott @ ORCL> select * from ti where object_id = 20;

Execution Plan

----------------------------------------------------------

Plan hash value: 1655810896

Bytes --------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes --------------------------------------------------------------------------------------

| 0 | select statement | 4 | 828 | 2 (0) | 00:00:01 |

| 1 | table access by index rowid | TI | 4 | 828 | 2 (0) | 00:00:01 |

| * 2 | index range scan | IND_TI | 4 | 1 (0) | 00:00:01 |

Bytes --------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

2-access ("OBJECT_ID" = 20)

Note

-----

-Dynamic sampling used for this statement (level = 2)

Summary: this feature is not available in earlier versions. If you want to remove the index for testing and then restore the index, you must first disable or delete the index. Later, it takes a lot of time to build the index, which may affect the business efficiency. After learning this feature, it will be of great help to optimize SQL testing in the future.