[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可以顯示最大最小值，連結：
--
-- 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 網路通訊系統的voronoi圖顯示與能耗分析matlab模擬Matlab
- 【高數覆盤】3.5函式的極值與最大最小值函式
- 最大值減不為0的最小值
- linux檔案管理命令例項分析【顯示、檢視、統計等】Linux
- PbootCMS修改後臺文章顯示最大數量boot
- [20230906]顯示最近統計分析的操作報表.txt
- 同時找到最大值和最小值——程式設計之美程式設計
- Python 查詢字典中最大最小值的keyPython
- JavaScript陣列中的最大值和最小值JavaScript陣列
- int float double 各型別的最大值最小值型別
- JavaScript 陣列最大值和最小值JavaScript陣列
- JavaScript 陣列 最大值和最小值JavaScript陣列
- JavaScript陣列最大值和最小值JavaScript陣列
- float型別最大值和最小值型別
- 2419 求最大值和最小值
- PbootCMS前臺顯示留言條數統計boot
- 通過dbms_stats包修改統計資訊裡的最大值最小值，等頻等高直方圖直方圖
- C++11獲取double型別的最大最小值C++型別
- MSSQL多列取最大或者最小值_轉載SQL
- Elance：調查顯示女性參與科技行業最大阻礙為缺榜樣行業
- C/C++_int/float/double的最大值和最小值C++
- 演算法求陣列中的最大值最小值演算法陣列
- 一個很好的指令碼，用來顯示cbo的統計資訊指令碼
- 不同系統裡同一Customizing activity的顯示差異分析
- Android 顯示系統Android
- Android系統耳機圖示顯示Android
- win10系統顯示桌面圖示的方法Win10
- Windows10系統顯示IME圖示的方法Windows
- 【譯】Swift演算法俱樂部-查詢最大／最小值Swift演算法
- JavaScript獲取陣列最大值和最小值JavaScript陣列
- JavaScript陣列最大值、最小值和平均數JavaScript陣列
- jQuery動畫的顯示與隱藏效果jQuery動畫
- jQuery動畫的顯示與隱藏效果！jQuery動畫
- NavigationBar的隱藏與顯示Navigation
- 【觸想智慧】工業顯示器的分類與應用領域分析
- 微服務治理與統計分析微服務
- MapReduce程式設計基礎（二）——數值概要（計算最大值、最小值、平均值）程式設計
- JavaScript可以設定最大值和最小值的隨機數JavaScript隨機