玉面飞龙的BLOG

Oracle8.15就有这个功能了。一直没有在production环境中用过。当时的估计，就算session能够使用隐藏参数_use_nosegment_indexes看到该虚拟索引,但virtual index没有segment，如何去产生该虚拟索引的统计信息，如何保证CBO的有效判断。

但从这里看到DBMS_STATS.GENERATE_STATS这个函数,提供根据相关对象的统计信息来设置该对象的统计信息。可以给虚拟索引设置统计信息,就能够帮助CBO判断”虚拟”索引是否有用。

Generates object statistics from previously collected statistics of related objects

而且这个估计统计信息的过程及快,虽然不能够十分准确，但基本不消耗CPU IO等资源。

Generate_stats也可以在普通索引和普通表上使用。当系统因为缺乏统计信息SQL执行特别糟糕的时候，可以尝试该方法快速设置缺失的统计信息，快速优化SQL恢复系统状态。

SQL> create table t as select * from dba_objects;
Table created.
SQL> create index tidx1 on t(owner);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.

SQL> create index tidx2 on t(object_id) nosegment;
Index created.

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,’tidx2′);
PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET “_use_nosegment_indexes” = TRUE;
Session altered.

SQL> explain plan for select * from t where object_id=:1 and owner=:2;

PLAN_TABLE_OUTPUT
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T | | | |
| 2 | INDEX RANGE SCAN | TIDX1 | | | |
—————————————————————————
Note: rule based optimization, PLAN_TABLE’ is old version

SQL> show parameters optimizer_mode
NAME VALUE
————————-
optimizer_mode RULE

SQL> alter session set optimizer_mode=’all_rows’;
Session altered.

SQL> explain plan for select * from t where object_id=:1 and owner=:2;

PLAN_TABLE_OUTPUT
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 83 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 83 | 2 |
| 2 | INDEX RANGE SCAN | TIDX2 | 1 | | 1 |
—————————————————————————
Note: cpu costing is off, PLAN_TABLE’ is old version

看来虚拟索引需要在CBO模式下才生效。

SQL> select object_id from dba_objects where object_name=’TIDX2′;
OBJECT_ID DATA_OBJECT_ID
——– ————–
543015        543015

虚拟索引虽然没有segment，但还是有个不为空的data_object_id.

SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt
from ind$ where obj#=543015;

OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
—————————————————————————————————————–
543015    1 19 8710 1 1 3661 01-JAN-09 2500 8710

如上ind$输出有些乱，但毕竟虚拟索引也能够有”不精确”的统计信息了。不再是鸡肋了。

再试一下普通索引TIDX1. 下面是analyze index分析的结果。

OWNER INDEX_NAME LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FAC SAMPLE
————— —————————— — ————— ————— ——————
ORACLE     TIDX1   22   1   17    8724   133   99

使用dbms_stats.generate_stats重新”估计”

SQL> analyze index tidx1 delete statistics;
Index analyzed.

SQL> EXECUTE DBMS_STATS.GENERATE_STATS (user,’tidx1′);
PL/SQL procedure successfully completed.

新统计信息如下。除了cluster_factor差很多,其它的还好。

OWNER INDEX_NAME LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FAC SAMPLE
————— —————————— — ————— ————— ——————
ORACLE   TIDX1    22    1   17   8724    3667    28

DBMS_STATS包也是藏龙卧虎啊。

仔细看文档，从来没有错的。