oracle虚拟索引的运用
SQL> alter session set events '10046 trace name context forever ,level 12' ;
SQL> create index ind_test_id on test(object_id) nosegment;
SQL> alter session set events '10046 trace name context off' ;
SQL> alter session set "_use_nosegment_indexes"=true;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 200 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
SQL> set autotrace traceonly
SQL> select * from test where object_id=1;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 200 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
772 consistent gets
279 physical reads
0 redo size
992 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
SQL> set autotrace off
SQL> alter session set statistics_level=all;
SQL> select * from test where object_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID btuhzhv88wwv3, child number 0
-------------------------------------
select * from test where object_id=1
Plan hash value: 1357081020
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TEST | 1 | 8 | 0 |00:00:00.01 | 706 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
在user_indexes中找不到这个索引。
SQL> select index_name,status from user_indexes where table_name='TEST';
未选定行
探究虚拟索引的原理，通过10046跟踪发现索引的信息插入到到数据字典中了。
SQL> alter session set events '10046 trace name context forever ,level 12' ;
SQL> create index ind_test_id on test(object_id) nosegment;
SQL> alter session set events '10046 trace name context off' ;
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,
remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,
spare1,spare2)
values
(:1,:2,:3,:4,0,0,0,:5,:6,:7)
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,
pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,
cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,
indmethod#,trunccnt,spare1,spare4,spare2,spare6)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,
:29,:30,:31,:33)
OBJ# OWNER# NAME
---------- ---------- ------------------------------
61081 61 IND_TEST_ID
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。