56 lines
1.2 KiB
SQL
56 lines
1.2 KiB
SQL
set verify off
|
|
|
|
-- arguments:
|
|
-- table name as SCHEMA.TABLE_NAME
|
|
-- column name
|
|
|
|
col endpoint_number format 999,999,999 head 'Endpoint|number'
|
|
col endpoint_value format 999,999,999 head 'Endpoint|value'
|
|
col frequency format 999,999,999 head 'Frequency'
|
|
col endpoint_repeat_count format 999,999,999 head 'Endpoint|repeat count'
|
|
|
|
|
|
col column_name format a30
|
|
|
|
SELECT column_id,
|
|
column_name,
|
|
histogram
|
|
FROM user_tab_columns
|
|
WHERE upper(table_name) LIKE
|
|
upper(CASE
|
|
WHEN INSTR('&1','.') > 0 THEN
|
|
SUBSTR('&1',INSTR('&1','.')+1)
|
|
ELSE
|
|
'&1'
|
|
END
|
|
) ESCAPE '\'
|
|
and COLUMN_NAME='&2'
|
|
ORDER BY column_id;
|
|
|
|
|
|
break on report skip 1
|
|
compute sum of frequency on report
|
|
compute sum of endpoint_repeat_count on report
|
|
|
|
select
|
|
endpoint_number,
|
|
endpoint_value,
|
|
endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value, endpoint_number) AS frequency,
|
|
endpoint_repeat_count
|
|
from
|
|
dba_tab_histograms
|
|
where
|
|
upper(table_name) LIKE
|
|
upper(CASE
|
|
WHEN INSTR('&1','.') > 0 THEN
|
|
SUBSTR('&1',INSTR('&1','.')+1)
|
|
ELSE
|
|
'&1'
|
|
END
|
|
) ESCAPE '\'
|
|
and COLUMN_NAME='&2'
|
|
order by
|
|
endpoint_number
|
|
;
|
|
|