11g's new parameter OPTIMIZER_USE_INVISIBLE_INDEXES以及不可见索引
来源:互联网 发布:中金香港 知乎 编辑:程序博客网 时间:2024/05/01 05:58
11G中出现了不可见索引,可以随时设置索引的可见与否,不可见索引仍然维护DML的变化。
1.创建测试表
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> create table a as select * from all_objects;
Table created.
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
2.无索引情况下的执行计划
SQL> set autotrace trace exp;
SQL> select * from a where object_id=789;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1738 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| A | 11 | 1738 | 275 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=789)
Note
-----
- dynamic sampling used for this statement
3.创建不可见索引后的执行计划
SQL> create index a_id on a(object_id) invisible;
Index created.
SQL> set autotrace trace exp;
SQL> select * from a where object_id=789;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1738 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| A | 11 | 1738 | 275 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=789)
Note
-----
- dynamic sampling used for this statement
此时仍然是全表扫描,可见不可见索引没有被采纳
4.把不可见索引改为可见后的执行计划
SQL> alter index a_id visible;
Index altered.
SQL> select * from a where object_id=789;
Execution Plan
----------------------------------------------------------
Plan hash value: 3205579595
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 158 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | A_ID | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=789)
Note
-----
- dynamic sampling used for this statement
此时,执行中就采用了刚才的可见索引。
再次修改为不可见,再看执行计划
SQL> alter index a_id invisible;
Index altered.
SQL> select * from a where object_id=789;
SQL> select * from a where object_id=789;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1738 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| A | 11 | 1738 | 275 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=789)
Note
-----
- dynamic sampling used for this statement
此时,又采用的全表扫描
5.以上的这些都由一个参数optimizer_use_invisible_indexes来控制
默认的参数值为
SQL> show parameter optimizer_use_in
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
更改参数为TRUE,再来看执行计划,
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> set autotrace trace exp
SQL> select * from a where object_id=789;
Execution Plan
----------------------------------------------------------
Plan hash value: 3205579595
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 158 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | A_ID | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=789)
Note
-----
- dynamic sampling used for this statement
可见,参数设置为TRUE时,执行计划就采用了不可见的索引
修改参数为FALSE,
SQL> alter session set optimizer_use_invisible_indexes=false;
Session altered.
SQL> select * from a where object_id=789;
Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1738 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| A | 11 | 1738 | 275 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=789)
Note
-----
- dynamic sampling used for this statement
此时,不可见的索引没有被采用
6.可见,不可见的索引,数据库仍然在维护着,只是在sql的执行计划中不被采用。
- 11g's new parameter OPTIMIZER_USE_INVISIBLE_INDEXES以及不可见索引
- 11g's New Parameter DB_ULTRA_SAFE
- Oracle 11g新特性:索引不可见
- Oracle 11g之不可见的索引
- 11G利用HITI使用不可见索引
- Oracle Study---Oracle 11g 不可见索引案例
- 11g新特性_索引的可见与不可见
- Oracle 11g 新特性 -- Invisible Indexes(不可见的索引) 说明
- Oracle 11g 新特性 -- Invisible Indexes(不可见的索引) 说明
- Oracle 11g新特性invisible index(不可见的索引)
- 【翻译自mos文章】OGG支持oracle db 11g中的不可见索引吗?
- oracle 不可见索引
- 不可见索引
- 不可见索引
- 设置View的可见以及不可见
- 不可见索引和虚拟索引。
- oracle 虚拟索引和不可见索引
- New UI-设置view的可见与不可见
- Model-View-Controller Pattern
- UVA 920 - Sunny Mountains
- 著名的"parameter sniffing"问题
- android TextView属性大全(转)
- 一些有用网址收集
- 11g's new parameter OPTIMIZER_USE_INVISIBLE_INDEXES以及不可见索引
- POJ 3925 Minimal Ratio Tree 最小生成树
- linux下c语言操作sqlite3(1)
- 函数
- 经验分享:做软件测试三,四年后的感想【zz】
- UVA 11704 - Caper pizza
- DoModal()没有显示
- android程序如何签名和发布应用程序
- CDialog中嵌入CView