不可见索引

最新推荐文章于 2024-03-23 23:55:42 发布

雅冰石

最新推荐文章于 2024-03-23 23:55:42 发布

阅读量817

点赞数

分类专栏： # ORACLE 性能调优 1 ORACLE 学习

1 ORACLE 学习同时被 2 个专栏收录

295 篇文章 4 订阅

订阅专栏

ORACLE 性能调优

39 篇文章 1 订阅

订阅专栏

默认情况下，当我们创建索引后，数据库就会自动的识别到该索引，并开始在成本估算中纳入创建的索引，不可见索引可是控制优化器是否对索引进行可见。那么不可见索引的用处就是，有时候当我们在创建一个索引往往对一个或是几个sql的执行存在性能提升，也许会对其他sql执行存在弊端，如果使用不可见索引，可以在优化器使用该索引和不使用该索引之间进行选择，从而便于我们决定索引的创建与否。其他在我们删除一个索引的时候一般步骤是将该索引至于不可见，随后运行将其至于不可用，最后删除索引。

创建不可见索引：

SQL> select index_name,index_TYPE FROM USER_INDEXES WHERE table_name='EMP';
 
no rows selected
 
SQL> 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    INVISIBLE
 
SQL>

在user_indexes 视图的visibilit字段可以观察该索引是否为不可见索引。

使当前索引在不可见索引和可见索引之间进行切换：

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    INVISIBLE
 
SQL> 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    VISIBLE
 
SQL> 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    INVISIBLE
 
SQL>

让优化器如何使用不可见索引？

一般在进行sql优化的时候常用到该功能，当我们发现一个sql中缺少相关索引，但是又不确定该索引创建后对sql到底有多大用处，且不知是否会对其他sql执行不利，那么可以使用该方法。注意：当索引被置为不可见的时候，即使使用hint优化器也是不会看到该索引的。

那么其中涉及到一个数据库动态参数：optimizer_use_invisible_indexes，该参数默认为fale，当将该参数改为true时，将对不可见索引进行使用，可以在会话级别和系统级别进行设定（一般我们在会话级别）。

演示过程如下：

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    INVISIBLE
 
SQL> select * from emp where empno=7902;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
 
SQL> set linesize 200
SQL> set autotrace trace exp
SQL> r
  1* 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> 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 exist
 
 
SQL> conn sys/root as sysdba
Connected.
SQL> show parameter optimizer_use
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> 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 SESSION
 
 
SQL> 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>

--本篇文章转自：http://www.2cto.com/database/201401/274096.html