Huaiyao's Notes

dbms_stats.convert_raw_value

Learn to use dbms_stats.convert_raw_value.

1
2
3
SQL> exec dbms_stats.gather_table_stats('SCOTT','DBA_OBJECTS',method_opt=>'for all columns size auto');
PL/SQL procedure successfully completed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> select * from dba_tab_col_statistics where TABLE_NAME='DBA_OBJECTS' and COLUMN_NAME='OBJECT_ID'
2 @pr
Pivoting output....
==============================
OWNER : SCOTT
TABLE_NAME : DBA_OBJECTS
COLUMN_NAME : OBJECT_ID
NUM_DISTINCT : 75248
LOW_VALUE : C103
HIGH_VALUE : C3084D14
DENSITY : .0000132893897512226
NUM_NULLS : 0
NUM_BUCKETS : 1
LAST_ANALYZED : 12-JUL-17
SAMPLE_SIZE : 75248
GLOBAL_STATS : YES
USER_STATS : NO
AVG_COL_LEN : 5
HISTOGRAM : NONE
PL/SQL procedure successfully completed.
1
2
3
4
5
6
7
SQL> select max(object_id) from dba_objects;
MAX(OBJECT_ID)
--------------
77619
1 row selected.
1
2
3
4
5
6
7
8
9
10
SQL> set serveroutput on
SQL> var temp number;
SQL> exec dbms_stats.convert_raw_value('C3084D14',:temp);
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line(:temp);
77619
PL/SQL procedure successfully completed.