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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.