内容很长。
create table t001x as select * from dba_objects;
create table t002x as select * from t001x;
create index idx_t001x_01 on t001x(object_id);
update t001x set object_id=rownum;
update t001x set object_id=1 where object_idtrue);
set autotrace traceonly;
select min(object_id) from t001x where object_id>65431;
2consistent gets
select min(object_id) from t001x where object_id>65430;
2consistent gets
select min(object_id) from t001x where object_id>65;
306consistent gets
set autotrace off;
删除数据
delete from t001x where object_idtrue);
set autotrace traceonly;
select min(object_id) from t001x where object_id>65431;
2consistent gets
select min(object_id) from t001x where object_id>65430;
2consistent gets
select min(object_id) from t001x where object_id>65;
306consistent gets
create index idx_t002x_01 on t002x(object_id);
update t002x set object_id=rownum;
update t002x set object_id=65431 where object_idtrue);
set autotrace traceonly;
select min(object_id) from t002x where object_id>65431;
3consistent gets
select min(object_id) from t002x where object_id>65430;
3consistent gets
select min(object_id) from t002x where object_id>65;
306consistent gets
set autotrace off;
删除数据
delete from t002x where object_idtrue);
set autotrace traceonly;
select min(object_id) from t002x where object_id>65431;
3consistent gets
select min(object_id) from t002x where object_id>65430;
261consistent gets
select min(object_id) from t002x where object_id>65;
564consistent gets
select NUM_ROWS,PCT_FREE,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,INDEX_NAME from dba_indexes where index_name like 'IDX_T00%';
NUM_ROWS PCT_FREE BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR INDEX_NAME
---------- ---------- ---------- ----------- ----------------- ------------------------------
7905 10
2
44
115 IDX_T002X_01
7905 10
1
44
115 IDX_T001X_01
问题：
1.update使用的值不一样，导致索引的BLEVEL也不一样？ 为什么？
2.在没有删除数据前以下两个SQL的consistent gets一致（一个BLEVEL=1 一个BLEVEL=2）
select min(object_id) from t001x where object_id>65430;
select min(object_id) from t002x where object_id>65430;
为什么删除数据后，这两个SQL的consistent gets差这么多？ 一个是2 一个是261
3.删除数据后
select min(object_id) from t002x where object_id>65;
比删除数据前的consistent gets多？为什么？ 306 vs564
查找了很多资料没有找到原因，请帮忙解惑。
OSREDHAT 5.7 X64
DB11.2.0.1.0 X64
dba_objects表有73000+数据
