oracle virtual index
来源:互联网 发布:网络物流管理系统 编辑:程序博客网 时间:2024/04/30 02:57
虚拟索引不消耗CPU以及IO,还有存储空间,用来判断创建的索引是否可被用,下面举例说明:
SQL> create table tb1 as select owner,object_name,object_id from dba_objects; --创建测试表
Table created.
SQL> select count(*) from tb1;
COUNT(*)
----------
50518
SQL> explain plan for select * from tb1 where object_id=108;
Explained.
SQL> select * from table(dbms_xplan.display); ---未创建虚拟索引前的执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3226679318
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 384 | 78 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB1 | 4 | 384 | 78 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"=108)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> alter session set "_use_nosegment_indexes"=true; --修改相关参数
Session altered.
SQL> create index tb1_object_id_idx on tb1(object_id) nosegment; --创建虚拟索引
Index created.
SQL> explain plan for select * from tb1 where object_id=108; --创建虚拟索引侯的执行计划
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 959502086
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 384 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB1 | 4 | 384 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TB1_OBJECT_ID_IDX | 219 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_ID"=108)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
注意:此索引在dba_indexes中是无法查到的,需要查询dba_ind_columns
SQL> create index TB1_OBJECT_ID_IDX on tb1(object_id); --虽然是虚拟索引,但是还是不能创建同名的索引,需要删除改虚拟索引 --创建真实索引
create index TB1_OBJECT_ID_IDX on tb1(object_id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> select index_owner,index_name from dba_ind_columns where lower(index_name)='tb1_object_id_idx';
INDEX_OWNER INDEX_NAME
------------------------------ ------------------------------
SYS TB1_OBJECT_ID_IDX
SQL> drop index tb1_object_id_idx; ---找出并删除该虚拟索引
Index dropped.
- oracle virtual index
- oracle virtual index
- Oracle 虚拟索引(Virtual index)
- Virtual Index的测试作用
- oracle index
- oracle index
- Oracle Index
- oracle index
- oracle index
- Oracle Index
- ORACLE INDEX
- Oracle Index
- Oracle index
- ORACLE Virtual Private Database
- Oracle Virtual Columns
- oracle virtual列
- Oracle virtual column
- Oracle的Index
- C语言野指针
- oracle copy 与postgresql copy
- EM
- 泛型的嵌套使用
- vc对话框显示图片
- oracle virtual index
- UML图例
- 技术人应该做的
- POJ 2406 Power Strings
- stack and heap
- AES加密算法在linux下相同输入每次加密结果都不同的问题
- H.264 SPS结构体内成员意义及用途说明
- javac的结构和执行流程介绍
- object-c学习笔记