12C New limitation for LOW_VALUE, HIGH_VALUE and ENDPOINT_VALUE on character columns

When investigating statistics collected for new tables on a 12.1.0.2 database i have come across a new discover (for me). In earlier release (berfore 12c) when collecting statistics on a table only the first 32 bytes of a character string was saved for LOW_VALUE, HIGH_VALUE and ENDPOINT_VALUE.Let’s see what happen on a 12C database.

TEST CASE :

create table test_lenght (a varchar2(100));

insert into test_lenght select ‘12345678912345678912345678912345’||level||’6789123456789123456789′ a from dual connect by level < 3000; — put a new value ‘level’ after 32 bytes.

exec DBMS_STATS.GATHER_TABLE_STATS(null,’TEST_LENGHT’,method_opt => ‘FOR ALL COLUMNS SIZE 2048’);

select length(utl_raw.cast_to_varchar2(low_value)),utl_raw.cast_to_varchar2(low_value), length(utl_raw.cast_to_varchar2(HIGH_VALUE)), utl_raw.cast_to_varchar2(HIGH_VALUE) from USER_TAB_COL_STATISTICS where table_name =’TEST_LENGHT’;

LENGTH(UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE))
——————————————-
UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE)
——————————————————————————–
LENGTH(UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE))
——————————————–
UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
——————————————————————————–
                                         64
1234567891234567891234567891234510006789123456789123456789123456
                                          64
1234567891234567891234567891234599967891234567891234567891234567

select count(*),max(length(ENDPOINT_ACTUAL_VALUE)) from USER_TAB_HISTOGRAMS where table_name = ‘TEST_LENGHT’
COUNT(*) MAX(LENGTH(ENDPOINT_ACTUAL_VALUE))
———- ———————————-
2048 64

Behind the scene :

This is the query that is executed to capture the table stats :

Capture

Observation :

12C database store the first 64 bytes of a character string in LOW_VALUE, HIGH_VALUE and ENDPOINT_VALUE this can give more accurate selectivity in case of long character string (URL,PATH,etc).

Also i wanted to show that there is a new limit for the maximum bucket number on histogram (In this case HYBRID) that is 2048 and not 254.

You can try this to test for the number of bucket limit :

BEGIN DBMS_STATS.GATHER_TABLE_STATS(null,’TEST_LENGHT’,method_opt => ‘FOR ALL COLUMNS SIZE 2049’); END;
Error at line 1
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 2049
ORA-06512: à “SYS.DBMS_STATS”, ligne 34634
ORA-06512: à ligne 1

Script Terminated on line 23.