How can I tell which table partitions have synopses?

If you’re using incremental statistics to optimize stats gathering on partitioned tables, you may want to know which partitions have synopses and what type of synopses they are.

Some time ago I posted a simple script to do this on GitHub. However, at the same time I created an ER to expose this information via a dictionary view. The enhancement was made in Oracle Database 18c, but not many people know about it.

Here’s a query you can use:

select table_name, notes
from   user_part_col_statistics
where  table_name = 'MY_TABLE_T1';

TABLE_NAME      NOTES
--------------- ------------------------
MY_TABLE_T1     HYPERLOGLOG

The NOTES column exposes the synopsis information from Oracle Database 18c onwards. You will see HYPERLOGLOG for ‘new-style’ synopses (the default in Oracle Database 12c Release 2), and ADAPTIVE_SAMPLING for ‘old-style’ (the default before Oracle Database 12c Release 2). 

I’ve noticed that I need to bug the documentation, since it’s not accurate at the time of writing [it refers the values “INCREMENTAL(HLL)” and “INCREMENTAL(SAMPLING)” in the NOTES column].