ENDPOINT_ACTUAL_VALUE is not populated for VARCHAR2(2)
The value for endpoint_actual_value does not seem to be populated for columns whose data type is varchar2(1) or varchar2(2). I am not sure why this is or if this is documented.
I used the following SQL snippets to reproduce this behavior.
create table tq84_ep (
c2 varchar2(2) not null,
c3 varchar2(3) not null
);
insert into tq84_ep
select
chr( 65 + trunc(power(dbms_random.value(0, sqrt( 26)), 2))) ||
chr( 65 + trunc(power(dbms_random.value(0, sqrt( 26)), 2))) ,
--
chr( 65 + trunc(power(dbms_random.value(0, sqrt( 26)), 2))) ||
chr( 65 + trunc(power(dbms_random.value(0, sqrt( 26)), 2))) ||
chr( 65 + trunc(power(dbms_random.value(0, sqrt( 26)), 2)))
from
dual connect by level <= 1e5;
commit;
Make sure, histograms are created (see
here):
select
count(*)
from
tq84_ep
where
c2 >= 'YY' or
c3 <= 'AB';
Create the histograms:
begin
dbms_stats.gather_table_stats(
user,
'tq84_ep',
method_opt => 'for all columns size auto'
);
end;
/
Select some histogram data related to column C2. The values in endpoint_actual_value are null:
column endpoint_actual_value format a10
column endpoint_value format 9999999999999999999999999999999999999
select
hst.endpoint_number,
hst.endpoint_value,
hst.endpoint_actual_value
from
user_tab_histograms hst
where
hst.table_name = 'TQ84_EP' and
hst.column_name = 'C2'
order by
hst.endpoint_value
;
--
-- ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
-- --------------- -------------------------------------- ----------
-- 197 338817652429001000000000000000000000
-- 288 338837934838605000000000000000000000
-- 367 338858217248209000000000000000000000
-- 419 338878499657812000000000000000000000
--
-- … etc …
Select some histogram data related to column C3. The values in endpoint_actual_value are filled according to the original values in the table:
select
hst.endpoint_number,
hst.endpoint_value,
hst.endpoint_actual_value
from
user_tab_histograms hst
where
hst.table_name = 'TQ84_EP' and
hst.column_name = 'C3'
order by
hst.endpoint_value
;
--
-- ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
-- --------------- -------------------------------------- ----------
-- 34 338822802259565000000000000000000000 AAA
-- 61 338822960715890000000000000000000000 AAC
-- 90 338823277628540000000000000000000000 AAG
-- 115 338823515313027000000000000000000000 AAJ
--
-- … etc …