Riddle me this! Why do decoded value for high_value and the MAX(ID) from the actual table not agree in the sql below?
I thought it was a statistics problem but after gathering statistics they are closer but still not the same.
- The table is not currently being accessed.
- I have limited these queries to 1 table while I work this out.
What am I actually trying to achieve? I am trying to find all tables where the current highest value in the ID column is approaching the maximum value for the column data type.
Some articles online suggest creating functions using dbms_stats.convert_raw_value(i_raw,m_n). In this particular system, I am not allowed to create anything so any solution has to be in query form. Open to any and all suggestions.
ALL_TAB_COLUMNS Query:
SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
low_value raw_low_value,
DECODE(data_type,'NUMBER',TO_CHAR(utl_raw.cast_to_number(low_value) ),low_value) low_value,
high_value raw_high_value,
DECODE(data_type,'NUMBER',TO_CHAR(utl_raw.cast_to_number(high_value) ),high_value) high_value,
density,
avg_col_len
FROM
all_tab_columns
WHERE
Column_Name = 'ID'
AND TABLE_NAME='OUTCOMES'
And Owner = 'PROD_A';
Query on table column:
Select Max(ID) From OUTCOMES;