[20151214]显示统计分析的最大与最小值

[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
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1871707/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1871707/