[20120903]关于Virtual index.txt
virtual index没有segment，如何去产生该虚拟索引的统计信息，如何保证CBO的有效判断。
做一个测试与学习看看：
1.测试环境:
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 t as select rownum id ,'test' name from dual connect by level
create index i_t_id on t(object_id) nosegment;
--EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id');
SQL> EXECUTE DBMS_STATS.gather_table_STATS (USER,'t');
PL/SQL procedure successfully completed.
2.查看执行计划:
SQL> explain plan for select * from t where id=:1 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 9 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=TO_NUMBER(:1))
13 rows selected.
SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session altered.
SQL> explain plan for select * from t where id=:1 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:1))
14 rows selected.
--可以通过设置参数_use_nosegment_indexes=true,来看看执行计划是否有效！
退出！
3.看看如何分配空间的呢？
SQL> select object_id,data_object_id from dba_objects where wner=user and object_name='I_T_ID';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
106423 106423
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106423
--没有统计信息！
SQL> select * from dba_indexes where wner=user and index_name='I_T_ID';
no rows selected
4.分析索引看看。
analyze index i_t_id delete statistics;
analyze index i_t_id validate structure;
validate index i_t_id;
SQL> validate index i_t_id;
Index analyzed.
SQL> select * from index_stats;
no rows selected
查看文档发现：
procedure generate_stats
(ownname varchar2, objname varchar2,
organized number default 7,
force boolean default FALSE);
--
-- This procedure generates object statistics from previously collected
-- statistics of related objects. For fully populated
-- schemas, the gather procedures should be used instead when more
-- accurate statistics are desired.
-- The currently supported objects are b-tree and bitmap indexes.
--
-- ownname - schema of object
-- objname - name of object
-- organized - the amount of ordering associated between the index and
-- its undelrying table. A heavily organized index would have consecutive
-- index keys referring to consecutive rows on disk for the table
-- (the same block). A heavily disorganized index would have consecutive
-- keys referencing different table blocks on disk. This parameter is
-- only used for b-tree indexes.
-- The number can be in the range of 0-10, with 0 representing a completely
-- organized index and 10 a completely disorganized one.
-- force - generate statistics even if it is locked
-- Exceptions:
-- ORA-20000: Unsupported object type of object does not exist
-- ORA-20001: Invalid option or invalid statistics
-- ORA-20005: object statistics are locked
--
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm
GENERATE_STATS Procedure
This procedure generates object statistics from previously collected statistics of related objects. The currently
supported objects are b-tree and bitmap indexes.
DBMS_STATS.GENERATE_STATS (
ownname VARCHAR2,
objname VARCHAR2,
organized NUMBER DEFAULT 7);
Parameters
Table 103-33 GENERATE_STATS Procedure Parameters
Parameter Description
ownname Schema of object
objname Name of object
organized
Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive
index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have
consecutive keys referencing different table blocks on disk.
Usage Notes
For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.
Exceptions
ORA-20000: Unsupported object type of object does not exist.
ORA-20001: Invalid option or invalid statistics.
------
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id');
PL/SQL procedure successfully completed.
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106423 1 22 10000 1 1 4139 2012-09-03 09:00:29 2500 10000
--organized = 0 看看！
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id',0);
PL/SQL procedure successfully completed.
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106423 1 22 10000 1 1 24 2012-09-03 09:00:38 2500 10000
--organized = 10 看看！
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id',10);
PL/SQL procedure successfully completed.
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106423;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106423 1 22 10000 1 1 9997 2012-09-03 09:00:43 2500 10000
--说明 rganized=0 clufac最小，organized=10 clufac最大。其他参数一样！
5.建立真实的索引看看:
SQL> create index i_t_id on t(id) ;
create index i_t_id on t(id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create index i_t_id1 on t(id) ;
Index created.
--可以这样！
SQL> column object_name format a10
SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name like 'I_T_ID%';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
I_T_ID1 106424 106424
I_T_ID 106423 106423
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106424;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106424 1 21 10000 1 1 20 2012-09-03 09:02:18 10000 10000
SQL> validate index i_t_id1;
Index analyzed.
SQL> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
2 32 I_T_ID1 10000 21 149801 7996 20 1 220 8028 0 0 10000
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
1 175944 150021 86 1 3 0 0 0 0
6.拿真实的索引测试看看：执行DBMS_STATS.GENERATE_STATS。
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id1');
PL/SQL procedure successfully completed.
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106424;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106424 1 22 10000 1 1 4139 2012-09-03 10:41:43 10000 10000
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select obj#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj#=106424;
OBJ# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106424 1 21 10000 1 1 20 2012-09-03 10:42:17 10000 10000
--说明可以修改clufac因子。并且状态看不出是手工修改的。
SQL> select index_name,user_stats from dba_indexes where wner=user and table_name like 'T';
INDEX_NAME USE
------------------------------ ---
I_T_ID1 NO
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id1');
PL/SQL procedure successfully completed.
SQL> select index_name,user_stats from dba_indexes where wner=user and table_name like 'T';
INDEX_NAME USE
------------------------------ ---
I_T_ID1 NO
7.为什么看不建I_T_ID索引呢？
SQL> select obj#,ts#,file#,block#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj# in (106423,106424);
OBJ# TS# FILE# BLOCK# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106423 4 0 0 1 22 10000 1 1 4139 2012-09-03 10:41:29 2500 10000
106424 4 4 570 1 21 10000 1 1 20 2012-09-03 10:55:34 10000 10000
--file#,block#=0! obj#=106423
8.看看改变Clustering Factor的情况：
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select count(name) from t where id between 50 and 150;
COUNT(NAME)
-----------
101
SQL> @dpc
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID gnfqbm1wfph1m, child number 0
-------------------------------------
select count(name) from t where id between 50 and 150
Plan hash value: 1534616770
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 102 | 3 (0)|
|* 3 | INDEX RANGE SCAN | I_T_ID1 | 102 | 2 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">=50 AND "ID"
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
26 rows selected.
SQL> alter system flush shared_pool;
System altered.
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'i_t_id1');
PL/SQL procedure successfully completed.
SQL> select count(name) from t where id between 50 and 150;
COUNT(NAME)
-----------
101
SQL> @dpc
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID gnfqbm1wfph1m, child number 0
-------------------------------------
select count(name) from t where id between 50 and 150
Plan hash value: 2966233522
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 8 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| T | 102 | 8 (0)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("ID"=50))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
25 rows selected.
--执行计划发生变化，因为Clustering Factor发生了变化：
SQL> select obj#,ts#,file#,block#,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,analyzetime,samplesize,rowcnt from sys.ind$ where obj# in (106423,106424);
OBJ# TS# FILE# BLOCK# BLEVEL LEAFCNT DISTKEY LBLKKEY DBLKKEY CLUFAC ANALYZETIME SAMPLESIZE ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ----------
106423 4 0 0 1 22 10000 1 1 4139 2012-09-03 10:41:29 2500 10000
106424 4 4 570 1 22 10000 1 1 4139 2012-09-03 11:12:14 10000 10000
