通過dbms_stats包修改統計資訊裡的最大值最小值，等頻等高直方圖
--------------------------------------------------------------------
建立測試表
--------------------------------------------------------------------
create table TEST ( a number, b varchar2(128), c date );
insert into TEST select object_id, object_name, created from user_objects where rownum < 1000;
commit;
--------------------------------------------------------------------
分析統計資訊
--------------------------------------------------------------------
begin
dbms_stats.gather_table_stats(ownname => user,
tabname => 'test',
no_invalidate => FALSE,
estimate_percent => null,
force => true,
method_opt => 'for all columns size 1',
cascade => true);
end;
/
--------------------------------------------------------------------
設定欄位最大值最小值
--------------------------------------------------------------------
DECLARE
srec DBMS_STATS.STATREC;
v_distcnt NUMBER;
v_density NUMBER;
v_nullcnt NUMBER;
v_avgclen NUMBER;
numvals DBMS_STATS.NUMARRAY;
charvals DBMS_STATS.CHARARRAY;
datevals DBMS_STATS.DATEARRAY;
BEGIN
-- 數值型別
DBMS_STATS.get_column_stats (ownname => user,
tabname => 'TEST',
colname => 'A',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
numvals := DBMS_STATS.numarray (1, 100000);
DBMS_STATS.prepare_column_values (srec, numvals);
DBMS_STATS.set_column_stats (ownname => user,
tabname => 'TEST',
colname => 'A',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
-- 字元型別
DBMS_STATS.get_column_stats (ownname => user,
tabname => 'TEST',
colname => 'B',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
charvals := DBMS_STATS.chararray ('A_MIN', 'Z_MAX');
DBMS_STATS.prepare_column_values (srec, charvals);
DBMS_STATS.set_column_stats (ownname => user,
tabname => 'TEST',
colname => 'B',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
-- 日期型別
DBMS_STATS.get_column_stats (ownname => user,
tabname => 'TEST',
colname => 'C',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
datevals := DBMS_STATS.datearray (sysdate-365, sysdate);
DBMS_STATS.prepare_column_values (srec, datevals);
DBMS_STATS.set_column_stats (ownname => user,
tabname => 'TEST',
colname => 'C',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
COMMIT;
END;
/
--------------------------------------------------------------------
為了顯示方便，建立一個函式
--------------------------------------------------------------------
create or replace function display_raw(rawval raw, type varchar2)
return varchar2 is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd);
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/
--------------------------------------------------------------------
設定前欄位的最大值最小值
--------------------------------------------------------------------
COL LOW_VAL FOR A30
COL HIGH_VAL FOR A30
COL DATA_TYPE FOR A30
select
a.column_name,
display_raw(a.low_value,b.data_type) as low_val,
display_raw(a.high_value,b.data_type) as high_val,
b.data_type
from
user_tab_col_statistics a, user_tab_cols b
where
a.table_name='TEST' and
a.table_name=b.table_name and
a.column_name=b.column_name
/
COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------
A 2 1118 NUMBER
B ACCESS$ XS$VERIFIERS VARCHAR2
C 2009-08-24 14:59:03 2009-08-24 14:59:33 DATE
--------------------------------------------------------------------
設定後欄位的最大值最小值
--------------------------------------------------------------------
COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------
A 1 100000 NUMBER
B A_MIN Z_MAX VARCHAR2
C 2010-09-08 16:48:29 2011-09-08 16:48:29 DATE
--------------------------------------------------------------------
設定欄位A的等高直方圖
--------------------------------------------------------------------
declare
v_distcnt number;
v_density number;
v_nullcnt number;
srec dbms_stats.statrec;
v_avgclen number;
n_array dbms_stats.numarray;
begin
DBMS_STATS.get_column_stats (ownname => user,
tabname => 'TEST',
colname => 'A',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
n_array := dbms_stats.numarray(20, 60, 2000, 3000, 3000, 3000, 3000, 3000, 3000, 4000, 10000);
srec.bkvals := null;
srec.epc := 11;
dbms_stats.prepare_column_values(srec, n_array);
dbms_stats.set_column_stats(
ownname => user,
tabname => 'test',
colname => 'a',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
end;
/
select endpoint_value,
endpoint_number,
lag(endpoint_number, 1) over(order by endpoint_number) prev_number
from user_tab_histograms
where table_name = 'TEST'
and column_name = 'A'
order by endpoint_value;
ENDPOINT_VALUE ENDPOINT_NUMBER PREV_NUMBER
-------------- --------------- -----------
20 0
60 1 0
2000 2 1
3000 8 2
4000 9 8
10000 10 9
--------------------------------------------------------------------
設定等頻A的等頻直方圖
--------------------------------------------------------------------
declare
v_distcnt number;
v_density number;
v_nullcnt number;
srec dbms_stats.statrec;
v_avgclen number;
n_array dbms_stats.numarray;
begin
DBMS_STATS.get_column_stats (ownname => user,
tabname => 'TEST',
colname => 'A',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
srec.bkvals := dbms_stats.numarray(20, 60, 80,100,120, 40, 60, 80, 100, 100,240);---------------每個值所對應的頻率
n_array := dbms_stats.numarray(2,40, 200,300, 400,500,600,700,800,900,1000); ---------按照大小排序
srec.epc := 11;
dbms_stats.prepare_column_values(srec, n_array);
dbms_stats.set_column_stats(
ownname => user,
tabname => 'test',
colname => 'a',
distcnt => 11,
density => 0.000500501,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
end;
/
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
20 2
80 40
160 200
260 300
380 400
420 500
480 600
560 700
660 800
760 900
1000 1000
select * from test where A=1000;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240 | 6480 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 240 | 6480 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-706944/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dba_histograms等高直方圖和等頻直方圖的理解Histogram直方圖
- 等頻直方圖，計算COST直方圖
- oracle統計資訊--直方圖的收集：Oracle直方圖
- oracle統計資訊和直方圖Oracle直方圖
- Oracle直方圖統計資訊的應用Oracle直方圖
- 關於列的直方圖統計資訊直方圖
- 蛋疼的CBO等高直方圖演算法直方圖演算法
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- [轉] oracle統計資訊(statistics)和直方圖(histogram)Oracle直方圖Histogram
- oracle統計資訊包--dbms_stats介紹Oracle
- 收集直方圖及檢視直方圖資訊直方圖
- 等頻率直方圖計算基數直方圖
- Javafx-【直方圖】文字頻次統計工具 中文/英文單詞統計Java直方圖
- oracle統計資訊 使用dbms_stats包收集統計資料Oracle
- 11g 等頻直方圖下sql不走索引掃描直方圖SQL索引
- MATLAB實現頻數直方圖——hist的使用Matlab直方圖
- 關於高度均衡和頻率均衡的直方圖直方圖
- 微課sql最佳化(7)、統計資訊收集(5)-關於直方圖SQL直方圖
- 直方圖直方圖
- Oracle資料遷移後由列的直方圖統計資訊引起的執行計劃異常Oracle直方圖
- 關於dbms_stats對系統統計資訊的管理
- 使用dbms_stats列出沒有統計資訊的物件！物件
- OpenCV計算機視覺學習（9）——影像直方圖 & 直方圖均衡化OpenCV計算機視覺直方圖
- 直方圖(histograms)直方圖Histogram
- 7.3 直方圖直方圖
- oracle 直方圖Oracle直方圖
- 11G，可以只刪除直方圖資訊直方圖
- 使用dbms_stats包收集統計資料(zt)
- 修改oracle 的統計資訊Oracle
- Oracle多列統計資訊與直方圖對有關聯多列查詢影響Oracle直方圖
- DBMS_STATS收集統計資訊的問題及解決
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- 在收集統計資訊時指定method_opt=>.. size auto 時，有可能不收集直方圖直方圖
- 直方圖均衡化直方圖
- 04:垂直直方圖直方圖
- oracle直方圖使用Oracle直方圖
- 直方圖學習直方圖