虚拟索引怎么监控

江南的诗

索引：
create index test_index on example(object_name) nosegment;


SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

SQL>
SQL> alter index test_index monitoring usage;
alter index test_index monitoring usage
*
ERROR at line 1:
ORA-08114: can not alter a fake index


请问，怎么监控这个索引！

江南的诗

9.2.0.8
sun 10

Maclean Liu(刘相兵

SQL> select * from v$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


CREATE TABLE virtual_index_tab as SELECT * FROM dba_tables;



CREATE INDEX virtual_index_tab_i ON virtual_index_tab(table_name) NOSEGMENT;


alter index virtual_index_tab_i monitoring usage;

SQL> alter index virtual_index_tab_i monitoring usage;
alter index virtual_index_tab_i monitoring usage
*
ERROR at line 1:
ORA-08114: can not alter a fake index


[oracle@oel8 ~]$ oerr ora 8114
08114, 00000, "can not alter a fake index"
// *Cause:  User attempted to alter a fake index
// *Action: drop fake index


一个fake index是不能被真实使用的 只能被CBO 用来生成执行计划而已， 且无法再实际执行时使用， 所以不存在 usage 使用信息， 所以也就没有monitor usage的必要

Maclean Liu(刘相兵

ODM FINDING;
Fake/NOSEGMENT Indexes

• Sometimes referred to as a Virtual Index

• An index that doesn’t actually exist

• Is not a segment, therefore contains no data

• Is not maintained during table DML

• Is useful to see what effect the index might have if it truly existed

• Can be used by the CBO to generate plan but

is not actually used at run time

江南的诗

tks