Table and Partition Statistics

When gathering statistics on Partitioned Tables, it is important to know the difference between Table Level and Partition Level (and, if applicable, SubPartition Level) statistics.

The Optimizer will use Partition Level statistics if the query predicates are on the Partition Keys. However, if the query predicates are not on these columns, (OR even if on the Partition Key do not allow for pruning to a *single partition* -- see update of 08-Sep below) the Optimizer relies on Table Level Statistics.

ANALYZE would aggregate Table Level statistics from Partition Level statistics. DBMS_STATS allows gathering of both or either statistics.

Here is an example showing how the Optimizer can badly estimate cardinality on a non-partition key column in the absence of Table Level statistics.

I have used the demo SH.SALES table which is Partitioned by TIME_ID. However, CUST_ID is a very relevant *dimension* column.

SQL> desc sh.sales
Name                                                                     Null?    Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PROD_ID                                                                  NOT NULL NUMBER
CUST_ID                                                                  NOT NULL NUMBER
TIME_ID                                                                  NOT NULL DATE
CHANNEL_ID                                                               NOT NULL NUMBER
PROMO_ID                                                                 NOT NULL NUMBER
QUANTITY_SOLD                                                            NOT NULL NUMBER(10,2)
AMOUNT_SOLD                                                              NOT NULL NUMBER(10,2)

SQL>
SQL> REM The SALES table is Range Partitioned by TIME_ID
SQL> REM  CUST_ID is a Dimension
SQL>
SQL> -- Current Statistics
SQL> --- NOTE !! Warning : For simplicity of reading I have converted NULL statistics to 0 (zero) for NUM_ROWS
SQL> --  However, in reality there is a big difference between NULL NUM_ROWS (meaning no statistics) and 0 NUM_ROWS (really meaning zero rows) !
SQL> exec dbms_stats.delete_table_stats('SH','SALES');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_CHANNEL_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROMO_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROD_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_CUST_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_TIME_BIX');

PL/SQL procedure successfully completed.

SQL> select table_name, nvl(num_rows,0) from dba_tables where owner = 'SH' and table_name = 'SALES';

TABLE_NAME                     NVL(NUM_ROWS,0)                                                                                    
------------------------------ ---------------                                                                                    
SALES                                        0                                                                                    

SQL> select partition_name, nvl(num_rows,0) from dba_tab_partitions where table_owner = 'SH' and table_name = 'SALES' order by partition_position;

PARTITION_NAME                 NVL(NUM_ROWS,0)                                                                                    
------------------------------ ---------------                                                                                    
SALES_1995                                   0                                                                                    
SALES_1996                                   0                                                                                    
SALES_H1_1997                                0                                                                                    
SALES_H2_1997                                0                                                                                    
SALES_Q1_1998                                0                                                                                    
SALES_Q2_1998                                0                                                                                    
SALES_Q3_1998                                0                                                                                    
SALES_Q4_1998                                0                                                                                    
SALES_Q1_1999                                0                                                                                    
SALES_Q2_1999                                0                                                                                    
SALES_Q3_1999                                0                                                                                    
SALES_Q4_1999                                0                                                                                    
SALES_Q1_2000                                0                                                                                    
SALES_Q2_2000                                0                                                                                    
SALES_Q3_2000                                0                                                                                    
SALES_Q4_2000                                0                                                                                    
SALES_Q1_2001                                0                                                                                    
SALES_Q2_2001                                0                                                                                    
SALES_Q3_2001                                0                                                                                    
SALES_Q4_2001                                0                                                                                    
SALES_Q1_2002                                0                                                                                    
SALES_Q2_2002                                0                                                                                    
SALES_Q3_2002                                0                                                                                    
SALES_Q4_2002                                0                                                                                    
SALES_Q1_2003                                0                                                                                    
SALES_Q2_2003                                0                                                                                    
SALES_Q3_2003                                0                                                                                    
SALES_Q4_2003                                0                                                                                    

28 rows selected.

SQL>
SQL> REM REM ############################################################
SQL> -- Delete and re-gather statistics
SQL> -- statistics at partition level
SQL> exec dbms_stats.delete_table_stats('SH','SALES');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_CHANNEL_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROMO_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROD_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_CUST_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_TIME_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SH','SALES',estimate_percent=>100,granularity=>'PARTITION',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from dba_tables where owner = 'SH' and table_name = 'SALES';

TABLE_NAME                       NUM_ROWS                                                                                         
------------------------------ ----------                                                                                         
SALES                              918843                                                                                         

SQL> select num_distinct, num_buckets, nvl(sample_size,0), histogram from dba_tab_col_statistics
2  where owner = 'SH' and table_name  = 'SALES' and column_name = 'CUST_ID';

NUM_DISTINCT NUM_BUCKETS NVL(SAMPLE_SIZE,0) HISTOGRAM                                                                             
------------ ----------- ------------------ ---------------                                                                       
      3203           1                  0 NONE                                                                                  

SQL>
SQL>
SQL> explain plan for
2  select cust_id,time_id,quantity_sold
3  from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 279964487                                                                                                        
                                                                                                                                
--------------------------------------------------------------------------------------------------                                
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                
--------------------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT         |       |   134K|  2094K|   104   (4)| 00:00:02 |       |       |                                
|   1 |  PARTITION RANGE ITERATOR|       |   134K|  2094K|   104   (4)| 00:00:02 |    17 |    19 |                                
|*  2 |   TABLE ACCESS FULL      | SALES |   134K|  2094K|   104   (4)| 00:00:02 |    17 |    19 |                                
--------------------------------------------------------------------------------------------------                                
                                                                                                                                
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                               
                                                                                                                                
 2 - filter("TIME_ID"<=TO_DATE(' 2001-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                                   14 rows selected.  SQL>
SQL> select count(*) from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');

COUNT(*)                                                                                                                        
----------                                                                                                                        
  145760                                                                                                                        

SQL>
SQL> explain plan for
2  select cust_id,time_id,quantity_sold
3  from sh.sales where cust_id in (10,100,1001);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2821443835                                                                                                       
                                                                                                                                
----------------------------------------------------------------------------------------------------------------------            
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |            
----------------------------------------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT                    |                |   861 | 13776 |   226   (0)| 00:00:03 |       |       |            
|   1 |  PARTITION RANGE ALL                |                |   861 | 13776 |   226   (0)| 00:00:03 |     1 |    28 |            
|   2 |   INLIST ITERATOR                   |                |       |       |            |          |       |       |            
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |   861 | 13776 |   226   (0)| 00:00:03 |     1 |    28 |            
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |            
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |            
----------------------------------------------------------------------------------------------------------------------            
                                                                                                                                
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                               
                                                                                                                                
 5 - access("CUST_ID"=10 OR "CUST_ID"=100 OR "CUST_ID"=1001)                                                                    

17 rows selected.

SQL>
SQL> select count(*) from sh.sales where cust_id in (10,100,1001);

COUNT(*)                                                                                                                        
----------                                                                                                                        
     252                                                                                                                        

SQL>
SQL>
SQL>
SQL> REM REM ############################################################
SQL>
SQL> -- Delete and re-gather statistics
SQL> -- statistics at table and partition level
SQL> exec dbms_stats.delete_table_stats('SH','SALES');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_CHANNEL_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROMO_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROD_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_CUST_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_index_stats('SH','SALES_TIME_BIX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SH','SALES',estimate_percent=>100,granularity=>'ALL',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from dba_tables where owner = 'SH' and table_name = 'SALES';

TABLE_NAME                       NUM_ROWS                                                                                         
------------------------------ ----------                                                                                         
SALES                              918843                                                                                         

SQL> select num_distinct, num_buckets, nvl(sample_size,0), histogram from dba_tab_col_statistics
2  where owner = 'SH' and table_name  = 'SALES' and column_name = 'CUST_ID';

NUM_DISTINCT NUM_BUCKETS NVL(SAMPLE_SIZE,0) HISTOGRAM                                                                             
------------ ----------- ------------------ ---------------                                                                       
      7059           1             918843 NONE                                                                                  

SQL>
SQL> explain plan for
2  select cust_id,time_id,quantity_sold
3  from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 279964487                                                                                                        
                                                                                                                                
--------------------------------------------------------------------------------------------------                                
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                
--------------------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT         |       |   134K|  2094K|   104   (4)| 00:00:02 |       |       |                                
|   1 |  PARTITION RANGE ITERATOR|       |   134K|  2094K|   104   (4)| 00:00:02 |    17 |    19 |                                
|*  2 |   TABLE ACCESS FULL      | SALES |   134K|  2094K|   104   (4)| 00:00:02 |    17 |    19 |                                
--------------------------------------------------------------------------------------------------                                
                                                                                                                                
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                               
                                                                                                                                
 2 - filter("TIME_ID"<=TO_DATE(' 2001-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                                   14 rows selected.  SQL>
SQL> select count(*) from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');

COUNT(*)                                                                                                                        
----------                                                                                                                        
  145760                                                                                                                        

SQL>
SQL> explain plan for
2  select cust_id,time_id,quantity_sold
3  from sh.sales where cust_id in (10,100,1001);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2821443835                                                                                                       
                                                                                                                                
----------------------------------------------------------------------------------------------------------------------            
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |            
----------------------------------------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT                    |                |   390 |  6240 |   158   (0)| 00:00:02 |       |       |            
|   1 |  PARTITION RANGE ALL                |                |   390 |  6240 |   158   (0)| 00:00:02 |     1 |    28 |            
|   2 |   INLIST ITERATOR                   |                |       |       |            |          |       |       |            
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |   390 |  6240 |   158   (0)| 00:00:02 |     1 |    28 |            
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |            
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |            
----------------------------------------------------------------------------------------------------------------------            
                                                                                                                                
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                               
                                                                                                                                
 5 - access("CUST_ID"=10 OR "CUST_ID"=100 OR "CUST_ID"=1001)                                                                    

17 rows selected.

SQL>
SQL> select count(*) from sh.sales where cust_id in (10,100,1001);

COUNT(*)                                                                                                                        
----------                                                                                                                        
     252                                                                                                                        

SQL> 

Let's compare the two runs.

The first run of queries is with granularity=>'PARTITION'.
In this case, for the CUST_ID, Oracle estimates 3,203 distinct values and no Histogram. It actually did not gather statistics based on any sampling of CUST_IDs. !
For the query on CUST_ID IN (10,100,1001), Oracle estimates a Cardinality of 861 rows (while the actual count is 252 rows).

The second run of queries is with granularity=>'ALL'.
Here, for the CUST_ID, Oracle estimates 7,059 distinct values, based on a sampling of 918,843 rows. It hsa not yet created a Histogram.
For the query on CUST_ID IN (10,100,1001), Oracle estimates a Cardinality of 390 rows, which is closer to the actual count of 252 rows.

Thus, although gathering statistics only at the Partition Level may make sense where your query predicates are always based on the Partition Key and Partition Pruning can happen, Table Level statistics are necessary for queries where Partition Pruning cannot be done.

.
.

And I have not even started improving the quality of the column statistics with the correct FOR COLUMNS SIZE specification for METHOD_OPT !
.
.
UPDATE : 08-Sep : Randolf Geist has pointed out "I think it's important to point out that partition or subpartition level statistics only get used if the optimizer is able to prune to a single partition/subpartition. Otherwise the next level statistics (partition/global) will get used.".
This also ties in with MetaLink Note#166215.1

So if I rerun the test where the query will hit a *single* partition only, I get :

SQL> explain plan for
 2  select cust_id,time_id,quantity_sold
 3  from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('28-FEB-2001','DD-MON-YYYY');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4096232376

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       | 39925 |   623K|    35   (3)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       | 39925 |   623K|    35   (3)| 00:00:01 |    17 |    17 |
|*  2 |   TABLE ACCESS FULL    | SALES | 39925 |   623K|    35   (3)| 00:00:01 |    17 |    17 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("TIME_ID"<=TO_DATE(' 2001-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  14 rows selected.  SQL>
SQL> select count(*) from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('28-FEB-2001','DD-MON-YYYY');

 COUNT(*)
----------
    39924

SQL> 

Thus, when the query is against a single partition -- such that Oracle can apply partition pruning -- the statistics from that specific partition are used. The cardinality estimate is more accurate -- coming to 39,925 against the actual count of 39,924.


.
.
.

I am an Oracle ACE, officially

I am very grateful to Randolf Geist for having nominated me as an Oracle ACE. He even went further and followed up on the nomination and secured me the Award. Thank you !

I also thank Lillian Buziak of the Oracle ACE Program Office for informing me of the award and putting up my profile.

The Oracle ACE program recognises proficiency in Oracle technology as well as a willingness to share knowledge and experiences with the Oracle community.

My Oracle ACE profile is available via the Oracle ACE page.
.
.
.

Update 06-Sep : THANK YOU, everyone who has sent a congratulatory message, either on this blog or by email.

I will continue to be active on this blog and oracle forums.

.

.

.

Histograms on "larger" columns

UPDATE :  These figures (the 254 distinct values, the 32 characters/bytes limit etc) were in tests on Release 10.2
Release 12.1 has relaxed some of the limits and has new Histogram types.

We rely on Oracle's collection of Histograms to denote data skew. However, if we give it some thought, it should be fairly obvious that generation of such histograms is a resource intensive task. The kernel has to identify all the distinct values for a column and, for each distinct value, count the number of occurrences of each such value.
If there are very many distinct values (more than 254) Oracle actually creates what it calls a Height Balanced Histogram, instead of a Frequency Histogram. (If the DBA explicitly specifies a number of buckets for column statistics and Oracle determines that the distinct count is more than such number, then, too, Oracle creates a Height Balanced Histogram).

Thus, it is fairly understood that
a. The number of distinct values
and
b. The number of rows in the table or sample
would determine the "effort" required to create a Histogram.

However, we sometimes forget that how "large" the column is -- in terms of how many characters (or bytes) are present in each occurrence [row] -- is also important. It would require more time and effort to compute the number of distinct values for a column with a length of 36 characters then it would for, say, a column with a length of 6 characters only. Sorting 36-character elements requires more memory/temp space and CPU (and I/O) then sorting 6-character elements. As also would be the effort to count the number of occurrences of each distinct value. (There may well be "word-size" limitations of the algorithms used as well !)

Therefore, when it comes to deriving a Histogram on such "large" columns, Oracle has to draw the line somewhere.

If the first 6 characters (actually, bytes !) of a column are the same, Oracle doesn't actually place the column values in ENDPOINT_VALUE in the %_TAB_HISTOGRAMS table. The values are placed in ENDPOINT_ACTUAL_VALUE !

However, if the first 32 characters (actually, bytes !) are the same, Oracle doesn't compare the rest of the characters in that row. Effectively, no Histogram is computed for a column where the first 32 characters (bytes) are the same.


Here I conduct a small test with columns of different values with 'N' leading characters being the same.

I first create a 10,000 row table with 4 columns and 10 distinct values in each column (with exactly 1,000 rows for each value) such that :

a. In Column A_SMALLER_COLUMN, only the first 2 characters are always the same and the third character varies
b. In Column B_LARGER_COLUMN, the first 6 characters are always the same
c. In Column C_GREATEST_COLUMN, the first 32 characters are always the same
d. In Column FIRST_FIVE_SAME, the first 5 characters the always the same

SQL> create table TEST_HSTGRM
2  (
3  a_smaller_column  varchar2(5),
4  b_larger_column varchar2(20),
5  c_greatest_column varchar2(60),
6  first_five_same varchar2(8)
7  );

Table created.

SQL>
SQL> declare
2  num_rows number;
3
4  begin
5  for num_rows in 1..10000
6  loop
7   insert into TEST_HSTGRM
8   values (
9           'A_'||mod(num_rows,10)||'_X',
10           'Large_'||mod(num_rows,10)||'_XY',
11           'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_'||mod(num_rows,10)||'_XYZ',
12           'Five_'||mod(num_rows,10));
13  end loop;
14  end;
15  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','TEST_HSTGRM',estimate_percent=>100,-
> method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed. 

I next see what column statistic Oracle has collected :

SQL> col column_name format a18
SQL> col endpoint_value format 9999999999999999999999999999999999999
SQL> col endpoint_actual_value format a30
SQL>
SQL> select column_name, sample_size, num_distinct, num_buckets, histogram,
2  low_value, high_value
3  from user_tab_columns
4  where table_name = 'TEST_HSTGRM';

COLUMN_NAME        SAMPLE_SIZE NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------ ----------- ------------ ----------- ---------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE
----------------------------------------------------------------
A_SMALLER_COLUMN         10000           10          10 FREQUENCY
415F305F58
415F395F58

B_LARGER_COLUMN          10000           10          10 FREQUENCY
4C617267655F305F5859
4C617267655F395F5859

C_GREATEST_COLUMN        10000            1           1 FREQUENCY
433132335F4142434445464748494A4B4C4D4E4F505152535455565758595A5F
433132335F4142434445464748494A4B4C4D4E4F505152535455565758595A5F

FIRST_FIVE_SAME          10000           10          10 FREQUENCY
466976655F30
466976655F39


SQL>
SQL> select column_name, count(*)
2  from user_tab_histograms
3  where table_name = 'TEST_HSTGRM'
4  group by column_name order by 1;

COLUMN_NAME          COUNT(*)
------------------ ----------
A_SMALLER_COLUMN           10
B_LARGER_COLUMN            10
C_GREATEST_COLUMN           1
FIRST_FIVE_SAME            10

SQL> 

Notice how Oracle could not (did not) compute a Histogram for C_GREATEST_COLUMN. With the first 32 characters being the same, Oracle just says that there is "1 distinct value". The LOW_VALUE and HIGH_VALUE are the same.

Next, I look at the actual Histograms on these columns :

SQL> set linesize132
SQL> select column_name, endpoint_value, endpoint_actual_value, endpoint_number
2  from user_tab_histograms
3  where table_name = 'TEST_HSTGRM' order by 1,3,2;

COLUMN_NAME                                ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE          ENDPOINT_NUMBER
------------------ -------------------------------------- ------------------------------ ---------------
A_SMALLER_COLUMN     339429957176373000000000000000000000                                           1000
A_SMALLER_COLUMN     339430036404535000000000000000000000                                           2000
A_SMALLER_COLUMN     339430115632698000000000000000000000                                           3000
A_SMALLER_COLUMN     339430194860860000000000000000000000                                           4000
A_SMALLER_COLUMN     339430274089023000000000000000000000                                           5000
A_SMALLER_COLUMN     339430353317185000000000000000000000                                           6000
A_SMALLER_COLUMN     339430432545348000000000000000000000                                           7000
A_SMALLER_COLUMN     339430511773510000000000000000000000                                           8000
A_SMALLER_COLUMN     339430591001673000000000000000000000                                           9000
A_SMALLER_COLUMN     339430670229835000000000000000000000                                          10000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_0_XY                                1000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_1_XY                                2000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_2_XY                                3000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_3_XY                                4000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_4_XY                                5000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_5_XY                                6000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_6_XY                                7000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_7_XY                                8000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_8_XY                                9000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_9_XY                               10000
C_GREATEST_COLUMN    348881704899430000000000000000000000                                          10000
FIRST_FIVE_SAME      365599813402059000000000000000000000                                           1000
FIRST_FIVE_SAME      365599813402063000000000000000000000                                           2000
FIRST_FIVE_SAME      365599813402068000000000000000000000                                           3000
FIRST_FIVE_SAME      365599813402073000000000000000000000                                           4000
FIRST_FIVE_SAME      365599813402078000000000000000000000                                           5000
FIRST_FIVE_SAME      365599813402082000000000000000000000                                           6000
FIRST_FIVE_SAME      365599813402087000000000000000000000                                           7000
FIRST_FIVE_SAME      365599813402092000000000000000000000                                           8000
FIRST_FIVE_SAME      365599813402096000000000000000000000                                           9000
FIRST_FIVE_SAME      365599813402101000000000000000000000                                          10000

31 rows selected.

SQL> 

As we've already seen, there is no real Histogram for C_GREATEST_COLUMN. There is a single entry representing 10,000 rows.
However, for column B_LARGER_COLUMN (where the first 6 characters are the same in every row), the actual value for each Histogram endpoint is visible in the ENDPOINT_ACTUAL_VALUE column while ENDPOINT_VALUE appears to be the same.

Finally, I look at how Oracle uses the Histograms to come up with the Cardinality estimates :

SQL> explain plan for
 2  select a_smaller_column from TEST_HSTGRM where a_smaller_column = 'A_3_X';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                           
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 |  6000 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM |  1000 |  6000 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
                                                                     
  1 - filter("A_SMALLER_COLUMN"='A_3_X')

13 rows selected.

SQL> select count(*) from TEST_HSTGRM where a_smaller_column = 'A_3_X';

 COUNT(*)                                                                                         
----------                
     1000

SQL> 
SQL> explain plan for
 2  select b_larger_column from TEST_HSTGRM where b_larger_column = 'Large_3_XY';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 | 11000 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM |  1000 | 11000 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("B_LARGER_COLUMN"='Large_3_XY')

13 rows selected.

SQL> select count(*) from TEST_HSTGRM where b_larger_column = 'Large_3_XY';

 COUNT(*)
----------
     1000

SQL>
SQL> explain plan for
 2  select c_greatest_column from TEST_HSTGRM
 3  where c_greatest_column = 'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_3_XYZ';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 10000 |   371K|    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM | 10000 |   371K|    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("C_GREATEST_COLUMN"='C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_3_XYZ'
             )

14 rows selected.

SQL> select count(*) from TEST_HSTGRM
 2  where c_greatest_column = 'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_3_XYZ';

 COUNT(*)
----------
     1000

SQL>
SQL> explain plan for
 2  select first_five_same from TEST_HSTGRM where first_five_same = 'Five_3';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 |  7000 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM |  1000 |  7000 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("FIRST_FIVE_SAME"='Five_3')

13 rows selected.

SQL> select count(*) from TEST_HSTGRM where first_five_same = 'Five_3';

 COUNT(*)
----------
     1000

SQL> 

For Column A_SMALLER_COLUMN, the Cardinality Estimate of 1,000 rows is accurate.
For Column B_LARGER_COLUMN, too, Cardinality Estimate is accurate (thus, we should look at ENDPOINT_ACTUAL_VALUE rather than ENDPOINT_VALUE in USER_TAB_HISTOGRAMS)
For Column FIRST_FIVE_SAME, we see similar results as A_SMALLER_COLUMN.

However, for column C_GREATEST_COLUMN, in the absence of the Histogram (which did not get computed), Oracle presents a Cardinality Estimate of 1 ! For this column, LOW_VALUE and HIGH_VALUE were the same and NUM_DISTINCT was 1.
For this column, Oracle does not DENSITY, even for an Outlier value :

SQL> l
  1  explain plan for
  2  select c_greatest_column from TEST_HSTGRM
  3* where c_greatest_column = 'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_z_XYZ'
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 10000 |   371K|    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM | 10000 |   371K|    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_GREATEST_COLUMN"='C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_z_XYZ'
              )

14 rows selected.

SQL>  

Therefore, when you have columns with the first 32 chraracters/bytes being the same, do not expect Oracle to present any degree of accuracy in the Cardinality estimate. You will have to rely on other columns in the query.

.
.
.