Shulou(Shulou.com)06/01 Report--

Oracle Study---Oracle 11g Invisible Index case

Compared with the previous version, Oracle 11g has introduced many new features, one of which is invisible index (invisible index).

Starting with Oracle 11g, invisible indexes (invisible index) can be created, and the optimizer ignores invisible indexes.

The initialization parameter optimizer_use_invisible_indexes determines whether to use invisible index, and its default value is false, that is, invisible index is not used by default. However, if the optimizer_use_invisible_indexes initialization parameter is set to true at the session level or system level, then invisible index can be used.

Unlike unavailable indexes, invisible indexes are still maintained during the use of DML statements.

It is useful for Oracle to introduce invisible indexes. Making indexes invisible is an alternative to making indexes unavailable or deleting indexes. Using an invisible index, you can do the following:

1. Test whether the deletion of the index will have an impact before deleting the index.

2. The specific operation or module of the application uses a temporary index structure so that it does not affect the entire application.

When the index is not visible, the execution plan generated by the optimizer does not use the index. When you delete an index, you can first change the index to invisible, and if there is no performance degradation, you can delete the index. When you create a new index on a table, you can first create an index that is initially invisible, then perform a test to see how efficient the index is, and finally determine whether to make the index visible and whether to use it.

You can check the visibility field of the dba_indexes, all_indexes, and user_indexes views to determine whether the index is visible or invisible.

1. Create an index

11:47:59 SCOTT@ enmo > create index emp1_name_ind on emp1 (ename) tablespace indx

Index created.

Elapsed: 00:00:00.07

11:50:13 SCOTT@ enmo > exec dbms_stats.gather_table_stats (user,'emp1',cascade= > true)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08

2. The setting index is not visible.

11:53:47 SCOTT@ enmo > ALTER INDEX emp1_name_ind invisible

Index altered.

Elapsed: 00:00:00.05

3. When executing the query, the optimizer did not select the index

11:56:17 SCOTT@ enmo > SELECT * FROM EMP1 WHERE ENAME='SCOTT'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed: 00:00:00.00

Execution Plan

Plan hash value: 2226897347

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

| | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP1 | 1 | 38 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("ENAME" = 'SCOTT')

Statistics

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

863 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4. Set optimization parameters

11:58:33 SYS@ enmo > grant alter session to scott

Grant succeeded.

11:56:34 SCOTT@ enmo > alter session set optimizer_use_invisible_indexes=true

Session altered.

Elapsed: 00:00:00.00

11:59:45 SCOTT@ enmo > SELECT * FROM EMP1 WHERE ENAME='SCOTT'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed: 00:00:00.00

Execution Plan

Plan hash value: 1963203073

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP1 | 1 | 38 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | EMP1_NAME_IND | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("ENAME" = 'SCOTT')

Statistics

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

867 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Invisible index is enabled by optimizer

5. if the index is made visible, the optimizer uses the index to access

11:59:56 SCOTT@ enmo > alter index emp1_name_ind visible

Index altered.

Elapsed: 00:00:00.06

12:00:46 SCOTT@ enmo > alter session set optimizer_use_invisible_indexes=false

Session altered.

Elapsed: 00:00:00.00

12:00:58 SCOTT@ enmo > select * from emp1 where ename='SCOTT'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed: 00:00:00.01

Execution Plan

Plan hash value: 1963203073

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP1 | 1 | 38 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | EMP1_NAME_IND | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("ENAME" = 'SCOTT')

Statistics

189 recursive calls

0 db block gets

26 consistent gets

0 physical reads

0 redo size

867 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

6. With hints, invisible indexes cannot be enabled.

12:01:10 SCOTT@ enmo > ALTER INDEX emp1_name_ind invisible

Index altered.

12:02:49 SCOTT@ enmo > select table_name,index_name,leaf_blocks,status,VISIBILITY FROM USER_INDEXES

12:06:03 2 where table_name='EMP1'

TABLE_NAME INDEX_NAME LEAF_BLOCKS STATUS VISIBILIT

-

EMP1 EMP1_NAME_IND 1 VALID INVISIBLE

12:26:11 SCOTT@ enmo > select / * + index (emp1 emp1_name_ind) * / * from emp1 where ename='SCOTT'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed: 00:00:00.01

Execution Plan

Plan hash value: 2226897347

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

| | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP1 | 1 | 38 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("ENAME" = 'SCOTT')

Statistics

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

863 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.