Virtual index

"Virtual" index 是Oracle undocumented的一个特性:创建后dba_segment/dba_indexes(9i)数据字典中没有记载,不会影响对应的sql,只是在系统隐含参数“_use_nosegment_indexes”设为true时发挥作用......

SQL> create table t1 as select * from dba_tables;

Table created.

SQL> create index idx_t1 on t1(owner,table_name) nosegment;

Index created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze index idx_t1 compute statistics;

Index analyzed.

SQL> select segment_name, segment_type, bytes
2 from dba_segments
3 where segment_name = 'IDX_T1';

no rows selected

SQL> select object_name, object_type, status
2 from dba_objects
3 where object_name ='IDX_T1';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------ -------
IDX_T1
INDEX VALID

SQL> select index_name, index_type, status
2 from dba_indexes
3 where index_name ='IDX_T1';

no rows selected

SQL> set autot traceonly;
SQL> select owner,table_name,num_rows
2 from t1
3 where owner='TEST' and table_name='T1';

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=21)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=8 Card=1 Bytes=21)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select owner,table_name,num_rows
2 from t1
3 where owner='TEST' and table_name='T1';

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=21)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes
=21)

2 1 INDEX (RANGE SCAN) OF 'IDX_T1' (NON-UNIQUE) (Cost=1 Card
=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
372 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 autot off;

SQL> alter index idx_t1 rebuild;
alter index idx_t1 rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134876/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134876/