11g new feature _ visible and invisible Index

When you want to evaluate the impact of an index on your SQL query statement, when your database is 11 GB, you can quickly set the index to visible or invisible. In commendable cases, when the index is set to invisable, the index will be maintained as usual when the DML statement occurs. That is to say, the index in the invisable state is not invalid, the optimizer does not select the index path.

 

The following is an experiment to verify:

 

SQL> select * from V $ version; banner implements Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionpl/SQL release 11.2.0.4.0-productioncore11.2.0.4.0productiontns for Linux: version 11.2.0.4.0-productionnlsrtl version 11.2.0.4.0-productionsql> select * from t; Id ---------- 21122 133sql> select count (ID) from t; count (ID) ---------- 5 execution plan hash value: 2966233522 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 3 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 3 | 2 | <strong> table access full </strong> | T | 5 | 15 | 2 (0) | 00:00:01 | -- we can see that this is a full table scan.

Create a unique index in the ID column of the T table

 

<P> SQL> create unique index idx_t_id on T (ID) invisible; </P> <p> index created. </P> SQL> select count (ID) from t; count (ID) ---------- 5 Execution Plan -------------------------------------------------------- plan hash value: 4168287108 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 3 | 1 (0) | 00:00:01 | 1 | sort aggregate | 1 | 3 | 2 | <strong> index full scan </strong> | idx_t_id | 5 | 15 | 1 (0) | 00:00:01 | ----------------------------------------------------------------------------- -- full index Scan

Set the index to invisible

SQL> alter index idx_t_id invisible; index altered <p> SQL> select index_name, status, visibility from dba_indexes where table_name = 'T '; index_name status visibility ------------------------------ -------- ---------- idx_t_id <span style = "color: # ff0000; "> <strong> valid </strong> </span> <strong> invisible </strong> </P> <p> SQL> select count (ID) from T; </P> <p> count (ID) ---------- 5 </P> <p> execution plan ------------------------------------------------------ plan hash value: 2966233522 </P> <p> ------------------------------------------------------------------------- | ID | operation | Name | rows | bytes | cost (% CPU) | time | --------------------------------------------------------------------------- | 0 | SELECT statement | 1 | 3 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 3 | 2 | <strong> table access full </strong> | T | 5 | 15 | 2 (0) | 00:00:01 | -- in this case, the full table scan path is used again in the execution plan </P> <p> SQL> alter index idx_t_id visible; index altered </P>

The invisible index can avoid index reconstruction, especially the index of large tables. This new feature is more conducive to database optimization.

 

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

This article comes from my technical blog http://blog.csdn.net/robo23

For reprinting, please mark the source text link; otherwise, you will be held legally responsible!

 

11g new feature _ visible and invisible Index