Invisible index and virtual index.

Invisible index and virtual column index. Today is. I am going to learn about other types of indexes. Here I will record my study notes. Invisible index: by default, after an index is created, the database automatically identifies the index and starts to include the index in the cost estimation, invisible index, but the control optimizer checks the index

Invisible index and virtual column index. Today is. I am going to learn about other types of indexes. Here I will record my study notes. Invisible index: by default, after an index is created, the database automatically identifies the index and starts to include the index in the cost estimation, invisible index, but the control optimizer checks the index

Invisible index and virtual column index.

Today is. I am going to learn about other types of indexes. Here I will record my study notes.
Invisible index:

By default, after an index is created, the database automatically identifies the index and starts to include the index in the cost estimation, invisible indexes, but the control optimizer determines whether the indexes are visible. The usefulness of the invisible index is that sometimes when we create an index, we tend to improve the performance of one or more SQL statements, which may cause disadvantages to other SQL statements, if an invisible index is used, you can select between the optimizer to use the index and the optimizer to determine whether to create the index. When we delete an index, the general step is to make the index invisible, and then run it to make it unavailable, and finally Delete the index.

Create an invisible index:

SQL> select index_name,index_TYPE FROM USER_INDEXES WHERE table_name='EMP';no rows selectedSQL> create index emp_idx1 on emp(empno) invisible;Index created.SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT------------------------------ --------------------------- -------- ---------EMP_IDX1                       NORMAL                      VALID    INVISIBLESQL> 

In the visibilit field of the user_indexes view, you can check whether the index is invisible.

Switch the current index between the invisible index and the visible index:

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT------------------------------ --------------------------- -------- ---------EMP_IDX1                       NORMAL                      VALID    INVISIBLESQL> alter index emp_idx1 visible;Index altered.SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT------------------------------ --------------------------- -------- ---------EMP_IDX1                       NORMAL                      VALID    VISIBLESQL> alter index emp_idx1 invisible;Index altered.SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT------------------------------ --------------------------- -------- ---------EMP_IDX1                       NORMAL                      VALID    INVISIBLESQL> 

How can I enable the optimizer to use an invisible index?

This function is usually used for SQL optimization. When we find that an SQL statement lacks related indexes, we are not sure how useful it will be to SQL after the index is created, you can use this method. Note: When an index is set to invisible, the index is not displayed even when the hits optimizer is used.

Therefore, it involves a dynamic database parameter: optimizer_use_invisible_indexes, which defaults to fale. When this parameter is changed to true, invisible indexes will be used, it can be set at the session level and system level (generally at the session level ).

The demo process is as follows:

eg:SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1'  2  ;INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT------------------------------ --------------------------- -------- ---------EMP_IDX1                       NORMAL                      VALID    INVISIBLESQL> select * from emp where empno=7902;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20SQL> set linesize 200SQL> set autotrace trace expSQL> r  1* select * from emp where empno=7902Execution Plan----------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=7902)SQL> select /*+index(EMP_IDX1)*/ * FROM EMP WHERE EMPNO=7902;Execution Plan----------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=7902)SQL> SHOW parameter optimizer_use_  ORA-00942: table or view does not existSQL> conn sys/root as sysdbaConnected.SQL> show parameter optimizer_useNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------optimizer_use_invisible_indexes      boolean     FALSEoptimizer_use_pending_statistics     boolean     FALSEoptimizer_use_sql_plan_baselines     boolean     TRUESQL> conn amy/rhys Connected.SQL> alter session set optimizer_use_invisible_indexes=true;Session altered.SQL> set autotrace trace exp      SQL> select * from emp where empno=7902;Execution Plan----------------------------------------------------------Plan hash value: 3085206398----------------------------------------------------------------------------------------| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |          |     1 |    32 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    32 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | EMP_IDX1 |     1 |       |     1   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=7902)SQL> alter session set optimizer_use_invisible_indexe=false;alter session set optimizer_use_invisible_indexe=false                  *ERROR at line 1:ORA-02248: invalid option for ALTER SESSIONSQL> alter session set optimizer_use_invisible_indexes=false;Session altered.SQL> select * from emp where empno=7902;Execution Plan----------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=7902)SQL> 

The maintenance of invisible indexes is not different from other B-tree indexes. You will be prepared to learn about the index maintenance content in the future.

Virtual index:

A virtual index, as its name implies, is an index with no segment in the virtual index. Note: This index does not have real physical storage information in the database, but is just a definition (somewhat similar to the definition of an External table) information. When we test whether a potential index is effective for SQL Execution, we recommend that you use an invisible index if it is not a large table, for large tables, the virtual index can be used only to test whether the optimizer uses the index and does not want to start the test after the index is created. We recommend that you use invisible indexes instead of virtual indexes for index creation tests.

Here we design a parameter named _ use_nosegment_indexes. Note that this parameter does not mean that the database will use a virtual index, but indicates whether the index is used in the optimizer in the execution plan:

SQL> set feedback offSQL> @getsp.sqlEnter value for par: _use_nosegmentKSPPINM                                            KSPPSTVL             KSPPDESC-------------------------------------------------- -------------------- ------------------------------------------------------------_use_nosegment_indexes                             FALSE                use nosegment indexes in explain planSQL> 

Create a virtual index:

SQL> create index emp_nosegment_idx1 on emp(deptno) nosegment;SQL> select index_name,index_type,table_name from user_indexes where table_name='EMP';INDEX_NAME                     INDEX_TYPE                  TABLE_NAME------------------------------ --------------------------- ------------------------------EMP_IDX1                       NORMAL                      EMPSQL> create index emp_nosegment_idx1 on emp(deptno) nosegment;SQL> select index_name,index_type,table_name from user_indexes where table_name='EMP';INDEX_NAME                     INDEX_TYPE                  TABLE_NAME------------------------------ --------------------------- ------------------------------EMP_IDX1                       NORMAL                      EMP

One thing we can see is that when creating a virtual index, oracle does not prompt whether the index is created successfully (hey .. Haha )? 2. Why is the virtual index not displayed in the query user_indexes view?

This is because dba_indexes displays the index information that actually stores information.

SQL> COL COLUMN_NAME FOR A30SQL> R  1* select index_name,column_name from user_ind_columns where table_name='EMP'INDEX_NAME                     COLUMN_NAME------------------------------ ------------------------------EMP_IDX1                       EMPNOEMP_NOSEGMENT_IDX1             DEPTNOSQL> 

How to use virtual indexes is to adjust _ use_nosegment_indexes at the session and system level.
Eg:

SQL> alter session set "_use_nosegment_indexes"=true;SQL> select empno from emp where empno=7369;Execution Plan----------------------------------------------------------Plan hash value: 3309675936---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     1 |     3 |     2   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| EMP_NOSEGMENT_IDX1 |     1 |     3 |     2   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("EMPNO"=7369)SQL> alter session set "_use_nosegment_indexes"=false;SQL> select empno from emp where empno=7369;Execution Plan----------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     3 |     6   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     1 |     3 |     6   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=7369)SQL> alter session set "_use_nosegment_indexes"=true;SQL> select * from emp where empno=7369;Execution Plan----------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPNO"=7369)SQL> alter session set "_use_nosegment_indexes"=false;SQL> drop index EMP_NOSEGMENT_IDX1  2  ;SQL> create index EMP_NOSEGMENT_IDX1 on emp(empno);SQL> select * from emp where empno=7369;Execution Plan----------------------------------------------------------Plan hash value: 612339345--------------------------------------------------------------------------------------------------| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                    |     1 |    32 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP                |     1 |    32 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | EMP_NOSEGMENT_IDX1 |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=7369)SQL> 

From the above we can see that the virtual index is not correct, and oracle may use virtual index or not use virtual index.