sqlplus ljb/ljb
成都創(chuàng)新互聯(lián)公司執(zhí)著的堅(jiān)持網(wǎng)站建設(shè)，成都小程序開發(fā)；我們不會(huì)轉(zhuǎn)行，已經(jīng)持續(xù)穩(wěn)定運(yùn)營十多年。專業(yè)的技術(shù)，豐富的成功經(jīng)驗(yàn)和創(chuàng)作思維，提供一站式互聯(lián)網(wǎng)解決方案，以客戶的口碑塑造品牌，攜手廣大客戶，共同發(fā)展進(jìn)步。
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
--以下注意觀察邏輯讀的次數(shù)，另外注意盡量每條語句執(zhí)行2遍以上，觀察第2遍的結(jié)果。
select * from t1 where id=1;
統(tǒng)計(jì)信息
-----------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
select /*+full(t1)*/ * from t1 where id=1;
統(tǒng)計(jì)信息
-------------------------------
0 recursive calls
0 db block gets
3 consistent gets
select * from t2 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
3 consistent gets
select /*+full(t2)*/ * from t2 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
5 consistent gets
select * from t3 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
3 consistent gets
select /*+full(t3)*/ * from t3 where id=1;
統(tǒng)計(jì)信息
----------------------------
0 recursive calls
0 db block gets
19 consistent gets
select * from t4 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
4 consistent gets
select /*+full(t4)*/ * from t4 where id=1;
統(tǒng)計(jì)信息
----------------------------
0 recursive calls
0 db block gets
148 consistent gets
select * from t5 where id=1;
統(tǒng)計(jì)信息
------------------------------
0 recursive calls
0 db block gets
4 consistent gets
select /*+full(t5)*/ * from t5 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
1435 consistent gets
select * from t6 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
4 consistent gets
select /*+full(t6)*/ * from t6 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
14298 consistent gets
select * from t7 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
5 consistent gets
select /*+full(t7)*/ * from t7 where id=1;
統(tǒng)計(jì)信息
-----------------------------
0 recursive calls
0 db block gets
142866 consistent gets
/*
規(guī)律:
從t1到t7(表記錄依次增大10倍，從1到1000000),索引讀的邏輯讀是 2,3,3,4,4,4,5
從t1到t7(表記錄依次增大10倍，從1到1000000)全表掃描的邏輯讀是 3,5,19,148,1435,14298,142866
*/
重慶分公司
重慶分公司
