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

来源：互联网发布：webp转换jpg软件编辑：程序博客网时间：2024/05/18 13:12

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

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

0 0