ヒストグラムの初歩的な理解
10105 ワード
参照
システム内のいくつかのテーブルに高度に不均一なデータ分布がある場合、ヒストグラムを使用すると、より良い選択的評価が生成され、より最適化された実行計画が生成されます.
次の例でヒストグラムの役割を感じます
ベースデータ
1万件のデータを挿入
せいぞう
不均一な場合
このときb列のデータは
【1】インデックスを作成する前に、クエリb=1またはb=5にかかわらず、移動するしかない
全表スキャン.
【2】b列にインデックスを作成する
create index sure.ix_tab_b on sure.tab(b);
ネット上の例では、インデックスがあればINDEX RANGE SCANを歩くべきだが、実際の状況(10201)では、b=5の場合、正しいパスが選択されている--
全表スキャン.統計がないため、Oracleは
ダイナミックサンプリングは、一時的に統計を収集することに相当するので、この場合は逆に正確です.
【3】統計は収集するがヒストグラムは収集しない
analyze table sure.tab compute statistics;
統計に関連するビュー
実行計画の観察
統計情報があり、
b=5の照会はかえって偏った.
最適化器は、tabテーブルb列の柱状図統計を作成し、その列の各値の具体的な分布を知ることができる.
analyze table sure.tab compute statistics for columns b size 10;
ヒストグラムのENDPOINT_VALUEはカラム値を表し、ENDPOINT_NUMBERは累積した行数を表す.
実行計画の観察
【まとめ】b=5のクエリにとって、全テーブルスキャンはインデックス範囲スキャンよりも合理的であり、ヒストグラムがあれば、オプティマイザは正確な判断を下すことができる.(統計はありませんが、ダイナミックサンプリングでパスを選択したため、歪んでいます)
システム内のいくつかのテーブルに高度に不均一なデータ分布がある場合、ヒストグラムを使用すると、より良い選択的評価が生成され、より最適化された実行計画が生成されます.
次の例でヒストグラムの役割を感じます
ベースデータ
drop user sure cascade;
create user sure identified by oracle;
grant resource to sure;
create table sure.tab (a number, b number);1万件のデータを挿入
begin
for i in 1..10000 loop
insert into sure.tab values (i, i);
end loop;
commit;
end;
/せいぞう
不均一な場合
update sure.tab set b=5 where b between 6 and 9995;
commit;このときb列のデータは
select b, count(*) from sure.tab group by b order by b;
B COUNT(*)
---------- ----------
1 1
2 1
3 1
4 1
5 9991
9996 1
9997 1
9998 1
9999 1
10000 1【1】インデックスを作成する前に、クエリb=1またはb=5にかかわらず、移動するしかない
全表スキャン.
explain plan for select * from sure.tab where b=1;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 1 | 26 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------explain plan for select * from sure.tab where b=5;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 253K| 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 253K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------【2】b列にインデックスを作成する
create index sure.ix_tab_b on sure.tab(b);
explain plan for select * from sure.tab where b=1;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
- dynamic sampling used for this statement
18 rows selected.explain plan for select * from sure.tab where b=5;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 253K| 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 253K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
- dynamic sampling used for this statement
17 rows selectedネット上の例では、インデックスがあればINDEX RANGE SCANを歩くべきだが、実際の状況(10201)では、b=5の場合、正しいパスが選択されている--
全表スキャン.統計がないため、Oracleは
ダイナミックサンプリングは、一時的に統計を収集することに相当するので、この場合は逆に正確です.
【3】統計は収集するがヒストグラムは収集しない
analyze table sure.tab compute statistics;
統計に関連するビュー
select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
from dba_tables where table_name = 'TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
10000 20 4 2080 0 10
col low_value format a16
col high_value format a16
col column_name format a16
select column_name, num_distinct, low_value, high_value, density, num_buckets
from dba_tab_columns where table_name = 'TAB';
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS
---------------- ------------ ---------------- ---------------- ---------- -----------
A 10000 C102 C302 .0001 1
B 10 C102 C302 .1 1
select table_name, column_name, endpoint_number, endpoint_value
from dba_tab_histograms where table_name = 'TAB';
TABLE_NA COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------- ---------------- --------------- --------------
TAB B 0 1
TAB A 0 1
TAB B 1 10000
TAB A 1 10000実行計画の観察
explain plan for select * from sure.tab where b=1;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------explain plan for select * from sure.tab where b=5;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------統計情報があり、
b=5の照会はかえって偏った.
最適化器は、tabテーブルb列の柱状図統計を作成し、その列の各値の具体的な分布を知ることができる.
analyze table sure.tab compute statistics for columns b size 10;
ヒストグラムのENDPOINT_VALUEはカラム値を表し、ENDPOINT_NUMBERは累積した行数を表す.
select table_name, column_name, endpoint_number, endpoint_value
from dba_histograms where table_name = 'TAB';
TABLE_NA COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------- ---------------- --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1
TAB A 1 10000実行計画の観察
explain plan for select * from sure.tab where b=1;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------explain plan for select * from sure.tab where b=5;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------【まとめ】b=5のクエリにとって、全テーブルスキャンはインデックス範囲スキャンよりも合理的であり、ヒストグラムがあれば、オプティマイザは正確な判断を下すことができる.(統計はありませんが、ダイナミックサンプリングでパスを選択したため、歪んでいます)