11g new feature _ visible and invisible index, and 11g new feature index visible

11g new feature _ visible and invisible index, and 11g new feature index visible

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!

 

How to make ORACLE index ineffective (invisible)

OK. We can see from the above that the index is normal. Now let's assume that we no longer need this index, so we want to delete it, but we don't know if it will be at the beginning of this article. If the index is invisible before it can be deleted, confirm that there is no problem and then delete the index. To this end, Oracle11g introduces a new function to set whether the index is visible, as shown in the following example:

How to delete the dots in word? The index is incorrect.

The dot is gray and cannot be printed. It is used to distinguish spaces from other invisible characters. Click Tools, options, and views, and remove the spaces marked in the format from the front of all. To create a directory, set the format of the content to be added to the directory to "Title 1", "title 2. Click Insert, index, directory, and directory.