Oracle直方圖統計資訊說明了表中資料的分佈情況，用於在表中資料分佈十分不均衡的情況下，指導CBO最佳化器選擇最優的執行計劃。以下例子說明了這一應用。

建立表

create table scott.t(id number);

建立索引

create index scott.idx_t_id on scott.t(id) compute statistics parallel;

插入資料

begin

for i in 1 .. 29990 loop

insert into scott.t values (1);

end loop;

commit;

end;

/

begin

for i in 29991 .. 30000 loop

insert into scott.t values (mod(i, 7));

end loop;

commit;

end;

/

檢視資料分佈

select id,

count(*) cardinality,

sum(count(*)) over(order by id range unbounded preceding) sum_cardinality

from scott.t

group by id;

ID CARDINALITY SUM_CARDINALITY

---------- ----------- ---------------

0 1 1

1 29991 29992

2 1 29993

3 2 29995

4 2 29997

5 2 29999

6 1 30000

可以看到表中資料的分佈嚴重不均衡，ID為0、2和6的記錄各只有1條，而ID為1的記錄有29991條，ID為3、4、5的記錄也各只有1條。

在這種情況下執行查詢，看執行計劃，可以看到，由於謂詞ID=1選擇性差，導致採用全表掃描

set autot trace exp

select * from scott.t where id=1;

執行計劃

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 29991 | 89973 | 15 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 29991 | 89973 | 15 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=1)

下面查詢唯一性高的條件，由於謂詞ID=0選擇性好，因此採用了索引掃描

select * from scott.t where id=0;

執行計劃

----------------------------------------------------------

Plan hash value: 371777749

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IDX_T_ID | 1 | 3 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"=0)

建立與distinct_keys=7相等數量的寬度均衡的直方圖

begin

dbms_stats.gather_table_stats(ownname => 'SCOTT',

tabname => 'T',

estimate_percent => 100,

method_opt => 'FOR COLUMNS SIZE 7 ID',

degree => 4,

cascade => true);

end;

/

查詢直方圖buckets資料分佈資訊

col owner for a10

col table_name for a20

col column_name for a20

col endpoint_number for a20

col endpoint_value for a20

select h.owner,

h.table_name,

h.column_name,

to_char(h.endpoint_number) endpoint_number,

to_char(h.endpoint_value) endpoint_value

from dba_histograms h

where h.owner = 'SCOTT'

and h.table_name = 'T';

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

---------- -------------------- -------------------- -------------------- --------------------

SCOTT T ID 1 0

SCOTT T ID 29992 1

SCOTT T ID 29993 2

SCOTT T ID 29995 3

SCOTT T ID 29997 4

SCOTT T ID 29999 5

SCOTT T ID 30000 6

在直方圖統計資訊的基礎上如果不使用繫結變數，查詢選擇性低的謂詞也是不會走索引的

select * from scott.t where id=1;

執行計劃

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 29991 | 89973 | 15 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 29991 | 89973 | 15 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=1)

但如果使用繫結變數，效果就不一樣了

var i number

exec :i:=1;

select * from scott.t where id=:i;

執行計劃

----------------------------------------------------------

Plan hash value: 371777749

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4286 | 12858 | 9 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IDX_T_ID | 4286 | 12858 | 9 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"=TO_NUMBER(:I))

以上測試說明，在表中資料分佈不均衡的情況下執行選擇性低的查詢，如果有完整準確的直方圖統計資訊，並且採用繫結變數，CBO會選擇索引掃描。如果沒有直方圖資訊，CBO將不走索引而選擇全表掃描。此處使用直方圖統計資訊使得查詢效能得到了較大提升。

Oracle直方圖統計資訊的應用

相關文章