Oracle查看表中每个字段的统计信息脚本

绝地灬酷狼 2022-08-06 06:04 138阅读 0赞

脚本可以很方便地看到表中每个字段的统计，优化器生成执行计划的时候也是看这个。

SQL> @d:/stat\_sanity.sql pub\_user

COLUMN\_NAME                      DISTINCT DENSITY      NULLS BKTS LO               HI  
\------------------------------ ---------- ------- ---------- ---- ---------------- ----------------  
CREATE\_TIME                          1520   0.001          0    1 26-6月 -2006     23-12月-2014  
CREATOR\_ID                            157   0.006          0    1 000000004065.ZH  szadmin.sz  
EMPLOYEE\_ID                         97381   0.000          0    1                  SuperAdmin  
MODIFIER\_ID                           157   0.006      76920    1 000000004065.ZH  szadmin.sz  
ORG\_ID                              10721   0.001          0  254 0000000043       FFF4CDB0E2E74A54  
STATE                                   3   0.000          0    2                0                2  
UPDATE\_TIME                          1604   0.001      76545    1 13-3月 -2014     23-12月-2014  
USER\_ID                             97381   0.000          0    1                  SuperAdmin  
USER\_TYPE                               2   0.000          0    2                0                1

stat\_sanity.sql

col density format 90.000  
col bkts    format  990  
col lo format A16  
col hi format A16  
select column\_name,  
       num\_distinct "DISTINCT",  
       round(density, 3) density,  
       num\_nulls "NULLS",  
       num\_buckets BKTS,  
       to\_char(to\_date(to\_char(sum(low\_val), '00000000000000'), 'YYYYMMDDHH24MISS'), 'DD-MON-YYYY') lo,  
       to\_char(to\_date(to\_char(sum(high\_val), '00000000000000'), 'YYYYMMDDHH24MISS'), 'DD-MON-YYYY') hi  
from (select column\_name,  
             num\_distinct,  
             density,  
             num\_nulls,  
             num\_buckets,  
             case rn  
               when 1 then low\_byte\_value - 100  -- century  
               when 2 then low\_byte\_value - 100  -- year  
               when 3 then low\_byte\_value        -- month  
               when 4 then low\_byte\_value        -- day  
               else low\_byte\_value - 1           -- hour, minute, second  
             end \* power(100, 7 - rn) low\_val,  
             case rn  
               when 1 then high\_byte\_value - 100  -- century  
               when 2 then high\_byte\_value - 100  -- year  
               when 3 then high\_byte\_value        -- month  
               when 4 then high\_byte\_value        -- day  
               else high\_byte\_value - 1           -- hour, minute, second  
             end \* power(100, 7 - rn) high\_val  
      from (select a.column\_name,  
                   a.num\_distinct,  
                   a.density,  
                   a.num\_nulls,  
                   a.num\_buckets,  
                   b.rn,  
                   to\_number(substr(low\_value, instr(low\_value, ' ', 1, 1 + b.rn),  
                                    instr(low\_value || ' ', ' ', 1, 2 + b.rn)  
                                    - instr(low\_value || ' ', ' ', 1, 1 + b.rn))) low\_byte\_value,  
                   to\_number(substr(high\_value, instr(high\_value, ' ', 1, 1 + b.rn),  
                                    instr(high\_value || ' ', ' ', 1, 2 + b.rn)  
                                    - instr(high\_value || ' ', ' ', 1, 1 + b.rn))) high\_byte\_value  
            from (select c.data\_type,  
                         t.num\_rows,  
                         c.column\_name,  
                         c.num\_distinct,  
                         translate(dump(c.low\_value), ',', ' ') low\_value,  
                         translate(dump(c.high\_value), ',', ' ') high\_value,  
                         c.density,  
                         c.num\_nulls,  
                         c.num\_buckets  
                  from dba\_tables t,  
                       dba\_tab\_columns c  
                  where t.owner = c.owner  
                    and t.owner = decode(instr('&1', '.'),  
                                         0, sys\_context('USERENV', 'CURRENT\_SCHEMA'),  
                                            upper(substr('&1', 1, instr('&1', '.') - 1)))  
                    and t.table\_name = decode(instr('&1', '.'),  
                                              0, upper('&1'),  
                                                 upper(substr('&1', instr('&1', '.') + 1)))  
                    and t.table\_name = c.table\_name  
                    and (c.data\_type  = 'DATE'  
                         or c.data\_type like 'TIMESTAMP%')) a,  
                 (select rownum rn  
                  from dual  
                  connect by level <= 7) b))  
group by column\_name,  
         num\_distinct,  
         density,  
         num\_nulls,  
         num\_buckets  
union all  
select column\_name,  
       num\_distinct,  
       round(density, 3),  
       num\_nulls,  
       num\_buckets,  
       substr(low\_value, 1, 16),  
       substr(high\_value, 1, 16)  
from (select c.data\_type,  
             t.num\_rows,  
             c.column\_name,  
             c.num\_distinct,  
             case c.data\_type  
               when 'FLOAT'  then lpad(to\_char(utl\_raw.cast\_to\_number(c.low\_value)), 16)  
               when 'NUMBER' then lpad(to\_char(utl\_raw.cast\_to\_number(c.low\_value)), 16)  
               else utl\_raw.cast\_to\_varchar2(c.low\_value)  
             end low\_value,  
             case c.data\_type  
               when 'FLOAT'  then lpad(to\_char(utl\_raw.cast\_to\_number(c.high\_value)), 16)  
               when 'NUMBER' then lpad(to\_char(utl\_raw.cast\_to\_number(c.high\_value)), 16)  
               else utl\_raw.cast\_to\_varchar2(c.high\_value)  
             end high\_value,  
             c.density,  
             c.num\_nulls,  
             c.num\_buckets  
      from dba\_tables t,  
           dba\_tab\_columns c  
      where t.owner = c.owner  
        and t.owner = decode(instr('&1', '.'),  
                             0, sys\_context('USERENV', 'CURRENT\_SCHEMA'),  
                                upper(substr('&1', 1, instr('&1', '.') - 1)))  
        and t.table\_name = decode(instr('&1', '.'),  
                                  0, upper('&1'),  
                                     upper(substr('&1', instr('&1', '.') + 1)))  
        and t.table\_name = c.table\_name  
        and (c.data\_type <> 'DATE'  
             and c.data\_type not like 'TIMESTAMP%'))  
order by column\_name  
/  
clear col  
clear breaks

转载自：

http://www.roughsea.com/article.php3?id\_article=60