How to find out virtual index from database?
Virtual index can be created in oracle database which doesn't has any physical
body and location. It can create with NOSEGMENT clause for testing purpose.
SQL> create table test11 (a number,b number);
Table created.
SQL> create index v_test11 on test11(a) nosegment;
Index created.
SQL> select index_name,owner from dba_indexes
where index_name='V_TEST11' and owner='SYS';
no rows selected
SQL> select index_owner,index_name,column_name,table_name from dba_ind_columns
2 where index_owner='SYS' and index_name='V_TEST11';
INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_NAME
SYS V_TEST11 A TEST11
SQL> select index_name from dba_ind_columns
2 minus
3 select index_name from dba_indexes;
INDEX_NAME
------------------------------
AAA_V
V_T1_A
V_TEST11
V_TEST1_A
SQL> select owner,object_id
2 from dba_objects
3 where object_name='V_TEST11';
OWNER OBJECT_ID
------------------------------ ----------
SYS 7718
SQL> select owner,object_id,object_type,status,temporary from dba_objects
2 where object_name='V_TEST11';
OWNER OBJECT_ID OBJECT_TYPE STATUS T
------------------------------ ---------- ------------------ ------- -
SYS 7718 INDEX VALID N
SQL> create index test11_b on test11(b);
Index created.
SQL>select object_name,object_id,object_type from dba_objects where object_type='INDEX'
and owner='SYS' and object_name like '%TEST%'
OBJECT_NAM OBJECT_ID OBJECT_TYPE
---------- ---------- ------------------
TEST11_B 7730 INDEX
V_TEST11 7718 INDEX
SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where obj# in
(7718,7730);
OBJ# TS# FILE# BLOCK# TYPE# FLAGS PROPERTY
7730 0 1 15832 1 0 0
7718 0 0 0 1 4096 0
|
Above query shows that in the IND$ flags of virtual index is 4096 and for
other index is 0. That means we can find out the VIRTUAL INDEX FROM following
queries ONLY.
SQL> select index_name from dba_ind_columns
2 minus
3 select index_name from dba_indexes;
AND
SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where
flags=4096
|
Full article...
