在做SQL性能优化时，有时候需要查看low_value,high_value的具体值，因为他们是raw类型，可以通过dbms_stats.convert_raw_value来获取具体值，可以用下面的函数获取：
|
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;
/
|
下面看个例子，查看表T的low_value,high_value具体值：
|
col low_val for a32
col high_val for a32
col data_type for a32
select
a.column_name,
display_raw(a.low_value,b.data_type) as low_val,
display_raw(a.high_value,b.data_type) as high_val,
b.data_type
from
user_tab_col_statistics a,
user_tab_cols b
where
a.table_name='T' and
a.table_name=b.table_name and
a.column_name=b.column_name;
COLUMN_NAME LOW_VAL HIGH_VAL DATA_TYPE
------------------------------ -------------------------------- -------------------------------- --------------------------------
OWNER AICBS XDB VARCHAR2
OBJECT_NAME /1000323d_DelegateInvocationHa yCbCrSubSamplingType229_T VARCHAR2
SUBOBJECT_NAME $VSN_1 WRH$_WAITST_2600569586_742 VARCHAR2
OBJECT_ID 2 83035 NUMBER
DATA_OBJECT_ID 0 83035 NUMBER
OBJECT_TYPE CLUSTER XML SCHEMA VARCHAR2
CREATED 2011-09-17 09:46:13 2023-05-06 09:28:17 DATE
LAST_DDL_TIME 2002-10-01 12:41:49 2023-05-06 09:28:17 DATE
TIMESTAMP 1990-08-26:11:25:00 2023-05-06:09:28:17 VARCHAR2
STATUS INVALID VALID VARCHAR2
TEMPORARY N Y VARCHAR2
GENERATED N Y VARCHAR2
SECONDARY N Y VARCHAR2
NAMESPACE 1 64 NUMBER
EDITION_NAME VARCHAR2
|
