在"利用10053分析执行计划的一个例子"这篇文章里，我提到----"这个默认值不是固定的，感觉这里这个默认值取决于Blks"。
当时为什么这么说是因为我看到的所有关于10053的文章中都或直接或隐约的指出当没有统计信息的时候，oracle是根据Blks来计算默认的统计信息。
呵呵，这个观点误导了我，我们来看一下真相是什么。
SQL> create table tb0624 (type number,ts timestamp) pctfree 90;
Table created
SQL> begin
2 for i in 1..10000 loop
3 insert into tb0624 values (1,sysdate);
4 insert into tb0624 values (3,sysdate);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed
SQL> begin
2 for i in 1..10 loop
3 insert into tb0624 values (2,sysdate);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS')
PL/SQL procedure successfully completed
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
20010 438 14
SQL> create index idx_type on tb0624(type);
Index created
这里我们分别修改NUM_ROWS，BLOCKS和AVG_ROW_LEN，以便来看看ix_sel到底取决于哪个值。
1、修改#Blks后的10053：
SQL> exec dbms_stats.set_table_stats(ownname => 'IPRA',tabname => 'TB0624',numblks => 538);
PL/SQL procedure successfully completed
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
20010 538 14
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB0624 Alias: TB0624
#Rows: 20010 #Blks: 538 AvgRowLen: 14.00
Index Stats::
Index: IDX_TYPE
LVLS: 1 #LB: 40 #DK:
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): TYPE(NUMBER) NO STATISTICS (using defaults)
AvgLen: 22.00 NDV: 625 Nulls: 0 Density: 0.0015992
Table: TB0624 Alias: TB0624
Card: Original: 20010 Rounded: 200 Computed: 200.10 Non Adjusted: 200.10
Access Path: TableScan
Cost: 121.28 Resp: 121.28 Degree: 0
Cost_io: 119.00 Cost_cpu: 7837335
Resp_io: 119.00 Resp_cpu: 7837335
Access Path: index (AllEqGuess)
Index: IDX_TYPE
resc_io: 17.00 resc_cpu: 151884
ix_sel: 0.004 ix_sel_with_filters: 0.004
Cost: 17.04 Resp: 17.04 Degree: 1
Best:: AccessPath: IndexRange Index: IDX_TYPE
Cost: 17.04 Degree: 1 Resp: 17.04 Card: 200.10 Bytes: 0
可以看到，当我在只改变#Blks的情况下，ix_sel的值未变。
2、修改#Rows后的10053
SQL> exec dbms_stats.set_table_stats(ownname => 'IPRA',tabname => 'TB0624',numblks => 438,numrows => 30010);
PL/SQL procedure successfully completed
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
30010 438 14
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB0624 Alias: TB0624
#Rows: 30010 #Blks: 438 AvgRowLen: 14.00
Index Stats::
Index: IDX_TYPE
LVLS: 1 #LB: 40 #DK:
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): TYPE(NUMBER) NO STATISTICS (using defaults)
AvgLen: 22.00 NDV: 938 Nulls: 0 Density: 0.0010663
Table: TB0624 Alias: TB0624
Card: Original: 30010 Rounded: 300 Computed: 300.10 Non Adjusted: 300.10
Access Path: TableScan
Cost: 100.66 Resp: 100.66 Degree: 0
Cost_io: 98.00 Cost_cpu: 9127191
Resp_io: 98.00 Resp_cpu: 9127191
Access Path: index (AllEqGuess)
Index: IDX_TYPE
resc_io: 18.00 resc_cpu: 173806
ix_sel: 0.005999 ix_sel_with_filters: 0.005999
Cost: 18.05 Resp: 18.05 Degree: 1
Best:: AccessPath: IndexRange Index: IDX_TYPE
Cost: 18.05 Degree: 1 Resp: 18.05 Card: 300.10 Bytes: 0
可以看到，当我在只改变#ROWS的情况下，ix_sel的值已经发生了改变。
3、修改AvgRowLen后的10053：
SQL> exec dbms_stats.set_table_stats(ownname => 'IPRA',tabname => 'TB0624',numblks => 438,numrows => 20010,avgrlen => 24);
PL/SQL procedure successfully completed
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
20010 438 24
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TB0624 Alias: TB0624
#Rows: 20010 #Blks: 438 AvgRowLen: 24.00
Index Stats::
Index: IDX_TYPE
LVLS: 1 #LB: 40 #DK:
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): TYPE(NUMBER) NO STATISTICS (using defaults)
AvgLen: 22.00 NDV: 625 Nulls: 0 Density: 0.0015992
Table: TB0624 Alias: TB0624
Card: Original: 20010 Rounded: 200 Computed: 200.10 Non Adjusted: 200.10
Access Path: TableScan
Cost: 100.08 Resp: 100.08 Degree: 0
Cost_io: 98.00 Cost_cpu: 7125191
Resp_io: 98.00 Resp_cpu: 7125191
Access Path: index (AllEqGuess)
Index: IDX_TYPE
resc_io: 17.00 resc_cpu: 151884
ix_sel: 0.004 ix_sel_with_filters: 0.004
Cost: 17.04 Resp: 17.04 Degree: 1
Best:: AccessPath: IndexRange Index: IDX_TYPE
Cost: 17.04 Degree: 1 Resp: 17.04 Card: 200.10 Bytes: 0
可以看到，当我在只改变AvgRowLen的情况下，ix_sel的值未变。
从上述测试中我们可以很清晰的得出如下结论：
ix_sel是指索引的选择性，表示索引字段的值的行数占总行数的比例，所以总行数变了，这个值就会变。而且该索引列的直方图变了，或者查询中的列的值变了，该值也会变。