[20130815]关于虚拟索引的问题.txt

[20130815]关于虚拟索引的问题.txt

虚拟索引建立并没有占用磁盘空间，主要用来评估建立的索引是否可用。但是存在一个问题，如果建立了这样的索引，
dba如何知道目前数据库存在那些虚拟索引，做一个例子来说明：

1.建立测试环境:

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> create table t (a number,b number,c varchar2(10));
Table created.

SQL> insert into t values (1,2,'a');
1 row created.

SQL> commit ;
Commit complete.

SQL> create index i_t_a on t(a);
Index created.

SQL> create index i_t_b on t(b) nosegment;
Index created.

--建立两个索引，其中i_t_b为虚拟索引。

2.查询看看建立了那些索引在表T上。

SQL> select index_name,owner from dba_indexes where index_name like 'I_T_%' and wner=user;

INDEX_NAME                     OWNER

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

I_T_A                          SCOTT

--可以发现仅仅看到在a字段的索引。

SQL> create index i_t_b on t(b) ;
create index i_t_b on t(b)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> create index i_t_bx on t(b) ;
Index created.

SQL> select index_owner,index_name,column_name,table_name from dba_ind_columns where index_name like 'I_T_%' and table_owner=user;

INDEX_OWNER                    INDEX_NAME                     COLUMN_NAME          TABLE_NAME

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

SCOTT                          I_T_A                          A                    T

SCOTT                          I_T_B                          B                    T

SCOTT                          I_T_BX                         B                    T

--查询dba_ind_columns视图可以知道。可以发现一个奇怪的情况B字段存在两个索引。

--有什么方法知道当前的数据库建立了虚拟索引呢？

SQL> select object_name,object_id,data_object_id,object_type from dba_objects where object_name in ('I_T_A','I_T_B','I_T_BX');

OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

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

I_T_A                    273812         273812 INDEX

I_T_B                    273813         273813 INDEX

I_T_BX                   273815         273815 INDEX

SQL> SELECT obj#, dataobj#, ts#, file#, block#, bo#, flags  FROM SYS.ind$ WHERE obj# IN (273812, 273813, 273815);

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#        BO#      FLAGS

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

    273812     273812          4          4        530     273811          2

    273813     273813          4          0          0     273811       4096

    273815     273815          4          4        538     273811          2

-- 对比可以看出查询flags=4096 ，才是虚拟索引。其他file#=0,block#=0 ，不能作为判断的依据。

--看来仅仅查询：

SQL> SELECT obj#, dataobj#, ts#, file#, block#, bo#, flags  FROM SYS.ind$ WHERE flags=4096;

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#        BO#      FLAGS

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

    273813     273813          4          0          0     273811       4096

--通过obj#,dataobj#来查询dba_objects,知道那个索引是虚拟索引，再查询dba_ind_columns(不能查dba_indexes视图)。