|* 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
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)
SQL> select o.obj#,o.owner#,o.name from obj$ o where name =upper('ind_test_id');
OBJ# OWNER# NAME
---------- ---------- ------------------------------
61081 61 IND_TEST_ID