索引成本計算的基礎知識
索引訪問路徑的工作流程：
1 為定義的索引列準備相關的謂詞
2 定位索引的根塊
3 沿著索引分支層往下走，一直到葉塊，這也是第一個符合謂詞條件的索引項 start key
4 遍歷整個葉塊連表，一直到最後一個能與謂詞匹配的索引項 stop key
5 對每一個索引項，確定是否訪問表塊
有以上可以知道和索引成本相關的3個因素：
1 按降續遍歷的分之層數
2 遍歷的葉快數目
3 訪問過的表塊數目
SQL> run;
1* select index_name,blevel,leaf_blocks,status,num_rows from user_indexes where table_owner='SCOTT
'
INDEX_NAME BLEVEL LEAF_BLOCKS STATUS NUM_ROWS
------------------------------ ---------- ----------- -------- ----------
PK_DEPT 0 1 VALID 4
PK_EMP 0 1 VALID 14
INDEX_TEST1 1 245 VALID 49834
IND_UPPER_TEST1 1 245 VALID 49834
IND_IND 0 1 VALID 3
TAI 0 0 VALID 0
TCI 0 0 VALID 0
IDX 0 1 VALID 93
JOB_IDX 0 1 VALID 5
GENDER_IDX 0 1 VALID 1
LOCATION_IDX 0 1 VALID 15
INDEX_NAME BLEVEL LEAF_BLOCKS STATUS NUM_ROWS
------------------------------ ---------- ----------- -------- ----------
AGE_GROUP_IDX 0 1 VALID 4
T2_PK 1 2 VALID 1024
已選擇13行。
索引成本的基本公式：
cost=blevel+ceiling(leaf_blocks*effective index selectivity)+ceiling(clusting_factor*effective table selectivity)
第一行：blevel
由於平衡二叉樹的原來，根塊到每個葉塊的距離是相等的，也就是說無論遍歷哪個葉塊，降序遍歷訪問的層數是相等的
第二行 ：leaf_blocks*effective index selectivity
表示的遍歷的葉塊數目
第三行
clusting_factor*effective table selectivity
表示表塊訪問的數目
下面舉例說明：
環境搭建：
create tablespace test datafile '' size 100m extent management local uniform size 8k segment space management manual;
execute dbms_random.seed(0)
drop table t1;
begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;
begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
exception when others then null;
end;
begin execute immediate 'alter session set "_optimizer_cost_model"=io';
exception when others then null;
end;
end;
/
create table t1
nologging
as
select
trunc(dbms_random.value(0,25)) n1,
rpad('x',40) ind_pad,
trunc(dbms_random.value(0,20)) n2,
lpad(rownum,10,'0') small_vc,
rpad('x',200) padding
from
all_objects
where
rownum <= 10000
;
create index t1_i1 on t1(n1, ind_pad, n2)
nologging
pctfree 91
;
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
SQL> select
2 num_rows, distinct_keys,
3 blevel, leaf_blocks, clustering_factor,
4 avg_leaf_blocks_per_key, avg_data_blocks_per_key
5 from
6 user_indexes
7 where table_name = 'T1'
8 and index_name = 'T1_I1'
9 ;
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PE LOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- -------------------------------
10000 500 2 1111 9745 2 19
已選擇 1 行。
可以看到當前索引T1_I1
有10000行，500個不同值，blevel 為2 葉塊1111，clustering_factor 9745
索引pctfree,只有當建立，重構，或者合併時才有用，並且只能對葉塊起作用
SQL> select
2 column_name,
3 num_nulls, num_distinct, density,
4 low_value, high_value
5 from
6 user_tab_columns
7 where table_name = 'T1'
8 and column_name in ('N1','N2','IND_PAD')
9 order by
10 column_name
11 ;
COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY LOW_VALUE HIGH_VALUE
-------------------- ---------- ------------ ---------- ------------------------ ------------------------
COLUMN_NAME,NUM_NULLS,NUM_DISTINCT,DENSITY,LOW_VALUE,HIGH_VALUE
IND_PAD,0,1,1,7820202020202020202020202020202020202020202020202020202020202020,7820202020202020202020202020202020202020202020202020202020202020
N1,0,25,0.04,80,C119
N2,0,20,0.05,80,C114
已選擇3行。
可以看出 列ind_pad 空值為0個，num_distinct 1 density 1
n1 列空值0，num_distinct 25 densitiy=0.04最小值80
SQL> select
2 small_vc
3 from
4 t1
5 where
6 n1 = 2
7 and ind_pad = rpad('x',40)
8 and n2 = 3
9 ;
已選擇21行。
執行計劃
----------------------------------------------------------
Plan hash value: 1429545322
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1160 | 25 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 1160 | 25 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=2 AND "IND_PAD"='x
' AND "N2"=3)
Note
-----
- cpu costing is off (consider enabling it)
統計資訊
----------------------------------------------------------
183 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
6045 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
21 rows processed
以上查詢中 ，最佳化器選定一個索引 ，並計算成本為25，基數20，我們以上查詢知道基數為20，估計非常準備 ，但是成本25怎麼來的呢
成本第一部分來自於索引訪問的第2行，cost=5,另一部分來自語表訪問的第一行增加了20，索引的成本5，遍歷分支層 2 可能是最佳化器已經年過估計為獲取20行資料必須遍歷三個 葉塊
我們接著分析兩種選擇率和clusting_fator因子
有效索引選擇率------
我們現在考慮查詢使用3個謂詞，那麼索引的返回項是多少呢？
在上帖子中
http://www.itpub.net/thread-945217-1-3.html
聯合選擇率公式selectivity(p and q)=selectivity(p)*selectivity(q)
selectivity(x and y and z)=
selectivity((x and y)and z))=
selectivity((x and y)*selectivity(y)=
selectivity(x)*selectivity(y)*selectivity(z)
所以有效索引選擇率=0.04*1*0.05=0.002不知道怎麼來的 看上邊查詢的num_distinct
如果我們要遍歷0.2%的索音項，必須遍歷0.2%的葉塊，因為索引中的葉塊是按順序儲存的
也就是成本公式中的：leaf_blocks*effective index selectivity
在user_indexes檢視中有distinct_keys為500，也就是有500個索引項。
也就是簡單的認為索引中有500中組合，某一種的組合機率為1/500=0.002?
一般情況下，最佳化器緊緊是將多個 獨立選擇率想乘，並不留意聯合索引選擇率的問題。有一個特例，以後帖子中會介紹。
oracle 為每一列儲存了density num_distinct，當存在直方圖可用並且索引中的不同值的數目值得懷疑時，考慮到偏斜資料，將獨立選擇率想乘來計算聯合選擇率是一種通用的解決方案。
有效表選擇率
在上個帖子中我們已經知道如何計算表選擇率，當所有與表相關的謂詞都是and連線時，只要簡單的每個選擇率相乘就OK
假設查詢中我們還包含了一個額外的謂詞small_vc='0000000001',如果我們選擇透過現有的索引訪問表，一直到接觸到表之後，我們還不能用上謂詞small_vc，因此這個謂詞將不能影響我們要訪問資料的分數，只能影響最終返回的資料的分數
所以有效表選擇率是：緊緊基於那些在接觸到表之前就能夠在索引中進行評估的謂詞。。。。。[@more@]
1 為定義的索引列準備相關的謂詞
2 定位索引的根塊
3 沿著索引分支層往下走，一直到葉塊，這也是第一個符合謂詞條件的索引項 start key
4 遍歷整個葉塊連表，一直到最後一個能與謂詞匹配的索引項 stop key
5 對每一個索引項，確定是否訪問表塊
有以上可以知道和索引成本相關的3個因素：
1 按降續遍歷的分之層數
2 遍歷的葉快數目
3 訪問過的表塊數目
SQL> run;
1* select index_name,blevel,leaf_blocks,status,num_rows from user_indexes where table_owner='SCOTT
'
INDEX_NAME BLEVEL LEAF_BLOCKS STATUS NUM_ROWS
------------------------------ ---------- ----------- -------- ----------
PK_DEPT 0 1 VALID 4
PK_EMP 0 1 VALID 14
INDEX_TEST1 1 245 VALID 49834
IND_UPPER_TEST1 1 245 VALID 49834
IND_IND 0 1 VALID 3
TAI 0 0 VALID 0
TCI 0 0 VALID 0
IDX 0 1 VALID 93
JOB_IDX 0 1 VALID 5
GENDER_IDX 0 1 VALID 1
LOCATION_IDX 0 1 VALID 15
INDEX_NAME BLEVEL LEAF_BLOCKS STATUS NUM_ROWS
------------------------------ ---------- ----------- -------- ----------
AGE_GROUP_IDX 0 1 VALID 4
T2_PK 1 2 VALID 1024
已選擇13行。
索引成本的基本公式：
cost=blevel+ceiling(leaf_blocks*effective index selectivity)+ceiling(clusting_factor*effective table selectivity)
第一行：blevel
由於平衡二叉樹的原來，根塊到每個葉塊的距離是相等的，也就是說無論遍歷哪個葉塊，降序遍歷訪問的層數是相等的
第二行 ：leaf_blocks*effective index selectivity
表示的遍歷的葉塊數目
第三行
clusting_factor*effective table selectivity
表示表塊訪問的數目
下面舉例說明：
環境搭建：
create tablespace test datafile '' size 100m extent management local uniform size 8k segment space management manual;
execute dbms_random.seed(0)
drop table t1;
begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;
begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
exception when others then null;
end;
begin execute immediate 'alter session set "_optimizer_cost_model"=io';
exception when others then null;
end;
end;
/
create table t1
nologging
as
select
trunc(dbms_random.value(0,25)) n1,
rpad('x',40) ind_pad,
trunc(dbms_random.value(0,20)) n2,
lpad(rownum,10,'0') small_vc,
rpad('x',200) padding
from
all_objects
where
rownum <= 10000
;
create index t1_i1 on t1(n1, ind_pad, n2)
nologging
pctfree 91
;
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
SQL> select
2 num_rows, distinct_keys,
3 blevel, leaf_blocks, clustering_factor,
4 avg_leaf_blocks_per_key, avg_data_blocks_per_key
5 from
6 user_indexes
7 where table_name = 'T1'
8 and index_name = 'T1_I1'
9 ;
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PE LOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- -------------------------------
10000 500 2 1111 9745 2 19
已選擇 1 行。
可以看到當前索引T1_I1
有10000行，500個不同值，blevel 為2 葉塊1111，clustering_factor 9745
索引pctfree,只有當建立，重構，或者合併時才有用，並且只能對葉塊起作用
SQL> select
2 column_name,
3 num_nulls, num_distinct, density,
4 low_value, high_value
5 from
6 user_tab_columns
7 where table_name = 'T1'
8 and column_name in ('N1','N2','IND_PAD')
9 order by
10 column_name
11 ;
COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY LOW_VALUE HIGH_VALUE
-------------------- ---------- ------------ ---------- ------------------------ ------------------------
COLUMN_NAME,NUM_NULLS,NUM_DISTINCT,DENSITY,LOW_VALUE,HIGH_VALUE
IND_PAD,0,1,1,7820202020202020202020202020202020202020202020202020202020202020,7820202020202020202020202020202020202020202020202020202020202020
N1,0,25,0.04,80,C119
N2,0,20,0.05,80,C114
已選擇3行。
可以看出 列ind_pad 空值為0個，num_distinct 1 density 1
n1 列空值0，num_distinct 25 densitiy=0.04最小值80
SQL> select
2 small_vc
3 from
4 t1
5 where
6 n1 = 2
7 and ind_pad = rpad('x',40)
8 and n2 = 3
9 ;
已選擇21行。
執行計劃
----------------------------------------------------------
Plan hash value: 1429545322
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1160 | 25 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 1160 | 25 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=2 AND "IND_PAD"='x
' AND "N2"=3)
Note
-----
- cpu costing is off (consider enabling it)
統計資訊
----------------------------------------------------------
183 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
6045 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
21 rows processed
以上查詢中 ，最佳化器選定一個索引 ，並計算成本為25，基數20，我們以上查詢知道基數為20，估計非常準備 ，但是成本25怎麼來的呢
成本第一部分來自於索引訪問的第2行，cost=5,另一部分來自語表訪問的第一行增加了20，索引的成本5，遍歷分支層 2 可能是最佳化器已經年過估計為獲取20行資料必須遍歷三個 葉塊
我們接著分析兩種選擇率和clusting_fator因子
有效索引選擇率------
我們現在考慮查詢使用3個謂詞，那麼索引的返回項是多少呢？
在上帖子中
http://www.itpub.net/thread-945217-1-3.html
聯合選擇率公式selectivity(p and q)=selectivity(p)*selectivity(q)
selectivity(x and y and z)=
selectivity((x and y)and z))=
selectivity((x and y)*selectivity(y)=
selectivity(x)*selectivity(y)*selectivity(z)
所以有效索引選擇率=0.04*1*0.05=0.002不知道怎麼來的 看上邊查詢的num_distinct
如果我們要遍歷0.2%的索音項，必須遍歷0.2%的葉塊，因為索引中的葉塊是按順序儲存的
也就是成本公式中的：leaf_blocks*effective index selectivity
在user_indexes檢視中有distinct_keys為500，也就是有500個索引項。
也就是簡單的認為索引中有500中組合，某一種的組合機率為1/500=0.002?
一般情況下，最佳化器緊緊是將多個 獨立選擇率想乘，並不留意聯合索引選擇率的問題。有一個特例，以後帖子中會介紹。
oracle 為每一列儲存了density num_distinct，當存在直方圖可用並且索引中的不同值的數目值得懷疑時，考慮到偏斜資料，將獨立選擇率想乘來計算聯合選擇率是一種通用的解決方案。
有效表選擇率
在上個帖子中我們已經知道如何計算表選擇率，當所有與表相關的謂詞都是and連線時，只要簡單的每個選擇率相乘就OK
假設查詢中我們還包含了一個額外的謂詞small_vc='0000000001',如果我們選擇透過現有的索引訪問表，一直到接觸到表之後，我們還不能用上謂詞small_vc，因此這個謂詞將不能影響我們要訪問資料的分數，只能影響最終返回的資料的分數
所以有效表選擇率是：緊緊基於那些在接觸到表之前就能夠在索引中進行評估的謂詞。。。。。[@more@]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9907339/viewspace-1054955/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 計算機基礎知識（自我的理解）計算機
- 【基礎知識】索引--點陣圖索引索引
- 索引基礎知識總結索引
- 計算機基礎知識複習計算機
- 計算機基礎知識很重要計算機
- 公共基礎知識-計算機組成計算機
- 計算機網路——基礎知識（一）計算機網路
- 計算機系統結構的基礎知識計算機
- 計算機基礎知識擴充套件篇計算機套件
- 計算機組成原理基礎知識（一）計算機
- Kafka訊息系統基礎知識索引Kafka索引
- iOS 基礎知識學習目錄索引iOS索引
- 程式設計基礎知識程式設計
- 什麼值得讀 ? 計算機基礎知識速成課計算機
- 面試-JS基礎知識-變數型別和計算面試JS變數型別
- 《java程式設計基礎》java的基礎知識（三）Java程式設計
- 基礎知識
- oracle spatial之基礎知識之四空間索引Oracle索引
- 遊戲基礎知識——“寵物”角色的設計遊戲
- 遊戲基礎知識——下位文明的設計遊戲
- LUA的基礎知識
- 【計算機網路知識掃盲】10、DOS命令基礎(轉)計算機網路
- HPC高效能運算知識: 基礎科普
- 計算機網路基礎知識總結計算機網路
- Java基礎——程式設計之路的開始，Java基礎知識Java程式設計
- 計算機基礎知識對程式設計師來說有多重要？計算機程式設計師
- 服務計算基礎知識 UDDI SOAP WSDL特性 SOA 設計原則
- 02.統計學基礎知識
- 網路程式設計基礎知識程式設計
- 架構設計基礎知識整理架構
- AI 基礎知識AI
- Webpack 基礎知識Web
- Dart基礎知識Dart
- RabbitMQ基礎知識MQ
- webpack基礎知識Web
- javascript基礎知識JavaScript
- ThinkPHP基礎知識PHP
- Laravel基礎知識Laravel