索引系列七--索引特性之高度较低是优化利器
sqlplus ljb/ljb
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;
drop table t5 purge;
drop table t6 purge;
drop table t7 purge;
create table t1 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1;
create table t2 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10;
create table t3 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100;
create table t4 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000;
create table t5 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10000;
create table t6 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100000;
create table t7 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000000;
create index idx_id_t1 on t1(id);
create index idx_id_t2 on t2(id);
create index idx_id_t3 on t3(id);
create index idx_id_t4 on t4(id);
create index idx_id_t5 on t5(id);
create index idx_id_t6 on t6(id);
create index idx_id_t7 on t7(id);
set linesize 1000
select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
from user_ind_statistics
where table_name in( 'T1','T2','T3','T4','T5','T6','T7');
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------ ----------- ---------- ------------- -----------------
IDX_ID_T1 0 1 1 1 1
IDX_ID_T2 0 1 10 10 2
IDX_ID_T3 0 1 100 100 15
IDX_ID_T4 1 3 1000 1000 143
IDX_ID_T5 1 21 10000 10000 1429
IDX_ID_T6 1 222 100000 100000 14286
IDX_ID_T7 2 2226 1000000 1000000 142858
set autotrace traceonly statistics
set linesize 1000
--以下注意观察逻辑读的次数，另外注意尽量每条语句执行2遍以上，观察第2遍的结果。
select * from t1 where id=1;
统计信息
-----------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
select /*+full(t1)*/ * from t1 where id=1;
统计信息
-------------------------------
0 recursive calls
0 db block gets
3 consistent gets
select * from t2 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
3 consistent gets
select /*+full(t2)*/ * from t2 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
5 consistent gets
select * from t3 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
3 consistent gets
select /*+full(t3)*/ * from t3 where id=1;
统计信息
----------------------------
0 recursive calls
0 db block gets
19 consistent gets
select * from t4 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
4 consistent gets
select /*+full(t4)*/ * from t4 where id=1;
统计信息
----------------------------
0 recursive calls
0 db block gets
148 consistent gets
select * from t5 where id=1;
统计信息
------------------------------
0 recursive calls
0 db block gets
4 consistent gets
select /*+full(t5)*/ * from t5 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
1435 consistent gets
select * from t6 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
4 consistent gets
select /*+full(t6)*/ * from t6 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
14298 consistent gets
select * from t7 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
5 consistent gets
select /*+full(t7)*/ * from t7 where id=1;
统计信息
-----------------------------
0 recursive calls
0 db block gets
142866 consistent gets
/*
规律:
从t1到t7(表记录依次增大10倍，从1到1000000),索引读的逻辑读是 2,3,3,4,4,4,5
从t1到t7(表记录依次增大10倍，从1到1000000)全表扫描的逻辑读是 3,5,19,148,1435,14298,142866
*/