转--不可见索引和虚拟索引

不可见索引：

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

创建不可见索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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字段可以观察该索引是否为不可见索引。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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到底有多大用处，那么可以使用该方法。注意：当索引被置为不可见的时候，即使使用hits优化器也是不会看到该索引的。

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

演示过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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    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>

 

对于维护不可见索引和其他Ｂ树索引没有不同，后续将准备在索引维护内容进行学习。

虚拟索引：

　虚拟索引顾名思义，就是虚拟的索引不存在段的索引，注意：该索引在数据库中并不存在真正的物理存储信息，而是只是一个定义（有点类似于外部表的定义）信息，当我们测试一个潜在的索引是否对ｓｑｌ执行有效时，如果不是一个大表那么建议使用不可见索引，而对于大表只是为了测试优化器是否使用该索引而不想等到索引创建完就开始测试，那么可以使用该虚拟索引。对于索引创建测试更推荐使用不可见索引而不是虚拟索引。

在此设计到一个参数那就是：_use_nosegment_indexes ，注意该参数并不是意味着数据库就要使用虚拟索引，而是代表该索引在优化器中是否使用其在执行计划内：

?
1
2
3
4
5
6
7
8
SQL> set feedback off
SQL> @getsp.sql
Enter value for par: _use_nosegment
 
KSPPINM                                            KSPPSTVL             KSPPDESC
-------------------------------------------------- -------------------- ------------------------------------------------------------
_use_nosegment_indexes                             FALSE                use nosegment indexes in explain plan
SQL>

创建虚拟索引：

?
1
2
3
4
5
6
7
8
9
10
11
12
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                      EMP
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                      EMP

 

?
1
 

　　有一点可以看到，在创建虚拟索引的时候，ｏｒａｃｌｅ并没有提示索引是否创建成功？第二，在查询ｕｓｅｒ＿ｉｎｄｅｘｅｓ视图中并没有显示该虚拟索引的信息？

 

这是因为ｄｂａ＿ｉｎｄｅｘｅｓ是显示的真实有存储信息的索引信息。

?
1
2
3
4
5
6
7
8
9
SQL> COL COLUMN_NAME FOR A30
SQL> R
  1* select index_name,column_name from user_ind_columns where table_name='EMP'
 
INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP_IDX1                       EMPNO
EMP_NOSEGMENT_IDX1             DEPTNO
SQL>

如何使用虚拟索引，就是通过在ｓｅｓｓｉｏｎ和ｓｙｓｔｅｍ　ｌｅｖｅｌ调节_use_nosegment_indexes。
ｅｇ：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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>

从以上可以看出虚拟索引也是不准的，ｏｒａｃｌｅ有可能使用虚拟索引也有可能不使用虚拟索引。

 

posted @ 2015-09-17 14:54  mayya  阅读(76)  评论(0)    收藏  举报