[20151214]显示统计分析的最大与最小值.txt
--昨天看了一个链接:
http://www.pythian.com/blog/oracle-internal-datatype-storage/
col low_value format a20
col high_value format a20
col table_name format a10 head 'TABLE'
col data_type format a20
col column_name format a6 head 'COLUMN'
set linesize 200 trimspool on
set pagesize 60
select
us.table_name,
uc.data_type,
us.column_name,
case
when uc.data_type in ('VARCHAR2','VARCHAR','CHAR') then
utl_raw.cast_to_varchar2(us.low_value)
when uc.data_type = 'NUMBER' then
to_char(utl_raw.cast_to_number(us.low_value) )
when uc.data_type = 'DATE' then
-- extract the century and year information from the
-- internal date format
-- century = (century byte -100) * 100
to_char((
to_number(
-- parse out integer appearing before first comma
substr( substr(dump(us.low_value),15), 1, instr(substr(dump(us.low_value),15),',')-1) - 100
) * 100
)
+
-- year = year byte - 100
(
to_number(
substr(
substr(dump(us.low_value),15),
-- get position of 2nd comma
instr(substr(dump(us.low_value),15),',',2)+1,
-- get position of 2nd comma - position of 1st comma
instr(substr(dump(us.low_value),15),',',1,2) - instr(substr(dump(us.low_value),15),',',1,1) -1
)
)
- 100
)) --current_year
|| '-' ||
lpad(
substr(
substr(dump(us.low_value),15),
instr(substr(dump(us.low_value),15),',',1,2)+1,
instr(substr(dump(us.low_value),15),',',1,3) - instr(substr(dump(us.low_value),15),',',1,2) -1
) -- month
,2,'0'
)
|| '-' ||
lpad(
substr(
substr(dump(us.low_value),15),
instr(substr(dump(us.low_value),15),',',1,3)+1,
instr(substr(dump(us.low_value),15),',',1,4) - instr(substr(dump(us.low_value),15),',',1,3) -1
) -- day
,2,'0'
)
|| ' ' ||
lpad(
to_char(to_number(
substr(
substr(dump(us.low_value),15),
instr(substr(dump(us.low_value),15),',',1,4)+1,
instr(substr(dump(us.low_value),15),',',1,5) - instr(substr(dump(us.low_value),15),',',1,4) -1
)
)-1)
,2,'0'
) -- hour
|| ':' ||
lpad(
to_char(
to_number(
substr(
substr(dump(us.low_value),15),
instr(substr(dump(us.low_value),15),',',1,5)+1,
instr(substr(dump(us.low_value),15),',',1,6) - instr(substr(dump(us.low_value),15),',',1,5) -1
)
)-1
)
,2,'0'
) -- minute
|| ':' ||
lpad(
to_char(
to_number(
substr(
substr(dump(us.low_value),15),
instr(substr(dump(us.low_value),15),',',1,6)+1
)
)-1
)
,2,'0'
) --second
else 'NOT SUPPORTED'
end low_value,
-- get the high value
case
when uc.data_type in ('VARCHAR2','VARCHAR','CHAR') then
utl_raw.cast_to_varchar2(us.high_value)
when uc.data_type = 'NUMBER' then
to_char(utl_raw.cast_to_number(us.high_value) )
when uc.data_type = 'DATE' then
-- extract the century and year information from the
-- internal date format
-- century = (century byte -100) * 100
to_char((
to_number(
-- parse out integer appearing before first comma
substr( substr(dump(us.high_value),15), 1, instr(substr(dump(us.high_value),15),',')-1) - 100
) * 100
)
+
-- year = year byte - 100
(
to_number(
substr(
substr(dump(us.high_value),15),
-- get position of 2nd comma
instr(substr(dump(us.high_value),15),',',2)+1,
-- get position of 2nd comma - position of 1st comma
instr(substr(dump(us.high_value),15),',',1,2) - instr(substr(dump(us.high_value),15),',',1,1) -1
)
)
- 100
)) --current_year
|| '-' ||
lpad(
substr(
substr(dump(us.high_value),15),
instr(substr(dump(us.high_value),15),',',1,2)+1,
instr(substr(dump(us.high_value),15),',',1,3) - instr(substr(dump(us.high_value),15),',',1,2) -1
) -- month
,2,'0'
)
|| '-' ||
lpad(
substr(
substr(dump(us.high_value),15),
instr(substr(dump(us.high_value),15),',',1,3)+1,
instr(substr(dump(us.high_value),15),',',1,4) - instr(substr(dump(us.high_value),15),',',1,3) -1
) -- day
,2,'0'
)
|| ' ' ||
lpad(
to_char(to_number(
substr(
substr(dump(us.high_value),15),
instr(substr(dump(us.high_value),15),',',1,4)+1,
instr(substr(dump(us.high_value),15),',',1,5) - instr(substr(dump(us.high_value),15),',',1,4) -1
)
)-1)
,2,'0'
) -- hour
|| ':' ||
lpad(
to_char(
to_number(
substr(
substr(dump(us.high_value),15),
instr(substr(dump(us.high_value),15),',',1,5)+1,
instr(substr(dump(us.high_value),15),',',1,6) - instr(substr(dump(us.high_value),15),',',1,5) -1
)
)-1
)
,2,'0'
) -- minute
|| ':' ||
lpad(
to_char(
to_number(
substr(
substr(dump(us.high_value),15),
instr(substr(dump(us.high_value),15),',',1,6)+1
)
)-1
)
,2,'0'
) --second
else 'NOT SUPPORTED'
end high_value
from all_tab_col_statistics us
join all_tab_columns uc on uc.owner = us.owner
and uc.table_name = us.table_name
and uc.column_name = us.column_name
and us.owner = USER
and us.table_name = 'EMP'
order by uc.column_id;
--我记得以前有一个链接，定义函数display_raw可以显示最大最小值，链接：
http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/
https://github.com/gregrahn/oracle_scripts/blob/master/display_raw.sql
--
-- display_raw.sql
--
-- DESCRIPTION
-- helper function to print raw representation of column stats minimum or maximum
--
-- Created by Greg Rahn on 2011-08-19.
--
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);
cbf binary_float;
cbd binary_double;
begin
if (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 = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'BINARY_FLOAT') then
dbms_stats.convert_raw_value(rawval, cbf);
return to_char(cbf);
elsif (type = 'BINARY_DOUBLE') then
dbms_stats.convert_raw_value(rawval, cbd);
return to_char(cbd);
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(cr);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/
--但是那个方法的缺点是要安装这个函数，有1点点不方便，而且不能在上面的例子使用dbms_stats.convert_raw_value过程，因为返回值记录在第2个参数。
--我自己也有一个脚本，也是抄别人的，当然我也做了小量修改：脚本如下：(当然你的显示器显示要足够的宽，至少设置set linesize 250)
$ cat tab_lh.sql
PROMPT
PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
PROMPT INPUT OWNER TABLE_NAME COLUMN
PROMPT SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
PROMPT IF NOT INPUT COLUMN_NAME ,USE "" .
PROMPT
column trans_low format a32
column trans_high format a32
column data_default format a20
column column_name format a24
SELECT
--owner,
-- table_name,
column_name,
data_type,
data_length,
nullable,
num_distinct,
density,
sample_size,
CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (low_value)
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (low_value))
WHEN data_type = 'DATE'
THEN
RTRIM (
LTRIM (
TO_CHAR (
100
* ( TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100),
'0000'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX'),
'00'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX'),
'00'))
|| ' '
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1,
'00')))
END
trans_low,
CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (high_value)
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (high_value))
WHEN data_type = 'DATE'
THEN
RTRIM (
LTRIM (
TO_CHAR (
100
* ( TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100),
'0000'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX'),
'00'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX'),
'00'))
|| ' '
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1,
'00')))
END
trans_high,
num_nulls,
num_buckets,
last_analyzed,
histogram,
data_default
FROM dba_tab_cols
WHERE owner = decode('&1','',user,upper('&1'))
AND table_name = upper('&2')
AND column_name = decode('&&3','',column_name,upper('&&3'))
ORDER BY column_id
/