SYSTEM$CLUSTERING_INFORMATION returns statistics that show how well a table is clustered for specified columns. Use it to assess whether micro-partition organization supports effective pruning and query performance. section 8.4.1 section 8.7
Call the function with these inputs and interpret its result through the clustering metrics:
- Pass the table name and a parenthesized list of columns; the column-list parameter is optional when the table already has a clustering key. section 8.4.1
- Read total_constant_partition_count as an indicator of well-clustered micro-partitions; larger values indicate better clustering. section 8.4.1
- Prefer lower average_overlaps and average_depth values, because they indicate fewer overlapping micro-partitions and shallower partition search. section 8.4.1
- Review clustering_errors and heed warnings about high-cardinality clustering keys in production scenarios. section 8.4.1
Check clustering for STORE_ID
select SYSTEM$CLUSTERING_INFORMATION('retailer_sales', '(store_id)'); An initially poor result can show 0 constant partitions, average_overlaps near the total partition count, and average_depth equal to that count. After clustering, the example improves from 1,455 to 1,271 total partitions, 0 to 25 constant partitions, 1,454 to 7.6271 average overlaps, and 1,455 to 5.0433 average depth.
Use the function before and after clustering, and pair it with AUTOMATIC_CLUSTERING_HISTORY to relate metric changes to reclustering activity and resource consumption. section 8.4.3 section 8.7
