oracle virtual index (oracle虚拟索引)

In 8.1.5 and above it is possible to create virtual indexes


Virtual indexes have a data dictionary definition, but no associated segment


Effectiveness of new indexes can be tested by generating theoretical execution plans


The CBO will consider virtual indexes if the hidden parameter _use_nosegment_indexes is set to true


做测试:


SQL> CREATE TABLE t1 AS


2 SELECT * FROM dba_objects


3 WHERE ROWNUM < 1000;


Table created.


SQL> ANALYZE TABLE t1 COMPUTE STATISTICS;


Table analyzed.


SQL> CREATE INDEX i1 ON t1 (owner, object_name) NOSEGMENT;


Index created.(创建虚拟索引使用NOSEGMENT clause)


SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'I1');


PL/SQL procedure successfully completed.


SQL> set autotrace traceonly


SQL> SELECT object_id FROM t1


2 WHERE owner = USER AND object_name = 'T1';


no rows selected


Execution Plan


----------------------------------------------------------


Plan hash value: 3617692013


--------------------------------------------------------------------------


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


--------------------------------------------------------------------------


| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 |


|* 1 | TABLE ACCESS FULL| T1 | 1 | 21 | 5 (0)| 00:00:01 |


--------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


1 - filter("OBJECT_NAME"='T1' AND "OWNER"=USER@!)


Statistics


----------------------------------------------------------


1 recursive calls


0 db block gets


15 consistent gets


0 physical reads


0 redo size


278 bytes sent via SQL*Net to client


374 bytes received via SQL*Net from client


1 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


0 rows processed


(要使用虚拟索引,需要设置隐含参数_use_nosegment_indexes)


SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;


Session altered.


SQL> SELECT object_id FROM t1


2 WHERE owner = USER AND object_name = 'T1';


no rows selected


Execution Plan


----------------------------------------------------------


Plan hash value: 1704772559


------------------------------------------------------------------------------------


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


------------------------------------------------------------------------------------


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


| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 21 | 2 (0)| 00:00:01 |


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


------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


2 - access("OWNER"=USER@! AND "OBJECT_NAME"='T1')


Statistics


----------------------------------------------------------


1 recursive calls


0 db block gets


15 consistent gets


0 physical reads


0 redo size


278 bytes sent via SQL*Net to client


374 bytes received via SQL*Net from client


1 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


0 rows processed
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值