索引的结构图:
索引查询示例图:
索引的特性:
1.索引高度比较低.
索引特性之高度较低的验证体会
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 set autotrace off 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
索引特性之高度较低是优化利器
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 full(表)的目的是 括号里的表将会使用全表扫描 */
原文:https://www.cnblogs.com/sunliyuan/p/12320835.html