修改统计信息的最值

最新推荐文章于 2021-04-14 08:36:24 发布

cong55109

最新推荐文章于 2021-04-14 08:36:24 发布

阅读量149

点赞数

-- 下面是显示最值的方法
declare
min_date date;
max_date date;
begin
dbms_stats.convert_raw_value('78700B06143B28',min_date);
dbms_stats.convert_raw_value('7873020F01070D',max_date);
dbms_output.put_line('min_date is '|| to_char(min_date,'yyyy-mm-dd hh24:mi:ss'));
dbms_output.put_line('max_date is '|| to_char(max_date,'yyyy-mm-dd hh24:mi:ss'));
end;
/

-- 获得当前统计信息中的最值和直方图信息，主要看srec
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
-- Date Type
DBMS_STATS.get_column_stats (ownname => 'GIMDATA',
tabname => 'GIM_MSG_INFO',
colname => 'DATE_CREATED',
distcnt => v_distcnt,
density => v_density,
nullcnt => v_nullcnt,
srec => srec,
avgclen => v_avgclen
);
datevals := DBMS_STATS.datearray (to_date('2012-11-06 19:58:39','yyyy-mm-dd hh24:mi:ss'), to_date('2015-02-15 00:06:12','yyyy-mm-dd hh24:mi:ss'));

-- 准备好要设置的最值，后面可以用dbms_stats.set_column_stats来设置
DBMS_STATS.prepare_column_values (srec, datevals);
DBMS_STATS.set_column_stats (ownname => 'GIMDATA',
tabname => 'GIM_MSG_INFO',
colname => 'DATE_CREATED',
srec => srec
);
COMMIT;
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/22818880/viewspace-2077107/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22818880/viewspace-2077107/