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

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

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