Histogram
The MaxCompute optimizer supports histograms for columns in tables. Histograms describe the distribution of column values across different value ranges, providing finer-grained statistics than other column-level statistical methods to help optimize query performance. Each histogram divides a column's values into non-overlapping buckets, and each bucket records the minimum value, maximum value, number of distinct values (NDV), and number of records within that range.
When histograms are available, the optimizer uses them for cardinality estimation, which improves query plan decisions.
Histograms must be collected manually using ANALYZE TABLE.
Limitations
-
Histograms can only be collected for columns of integer and floating-point data types.
-
Only the
ANALYZE TABLEstatement can collect histograms. Automatic collection is not supported. -
A maximum of 30 columns can be collected at a time.
-
Histograms cannot be collected on empty partitions or tables.
-
Cardinality estimation based on histograms applies to a single partition or table. Buckets cannot be combined across partitions.
-
If new data is written after a histogram is collected, the histogram becomes invalid.
Histogram collection generates computing costs. If a histogram becomes invalid, you can determine whether to collect a histogram again based on your business requirements.
Collect histograms
Syntax
ANALYZE TABLE <tablename> compute statistics FOR columns [(...)] [[WITH histogram [256 buckets]] [columns (...)]];Parameters:
| Parameter | Description |
|---|---|
<tablename> |
Name of the table on which to collect histograms |
WITH histogram |
Enables histogram collection |
N buckets |
Number of buckets per histogram. Default: 256. Maximum: 1,024. |
columns (col1, col2, ...) |
Specific columns for which to collect column statistics |
WITH histogram columns (col1, ...) |
Specific columns for which to collect histograms. Must be a subset of the columns list. |
Examples
Collect histograms for all eligible columns
ANALYZE TABLE <tablename> compute statistics FOR columns WITH histogram;The system collects histograms only for integer and floating-point columns and ignores other column types.
If the table has more than 10 eligible columns, the system returns the following error:
Analyze histogram column number exceeds auto columns limit, please specify column names for analyze(xxx) or set unlimited auto columns(xxx)To remove this limit and collect histograms for all eligible columns regardless of count, set the following flag before running ANALYZE TABLE:
set odps.sql.analyze.histogram.auto.column.num = -1;The 30-column-per-run limit still applies.
Collect histograms for all eligible columns and specify the bucket count
ANALYZE TABLE <tablename> compute statistics FOR columns WITH histogram 256 buckets;Collect column statistics for specific columns and histograms for all of them
ANALYZE TABLE <tablename> compute statistics FOR columns(col1, col2) WITH histogram;Collect column statistics for specific columns and histograms for a subset
ANALYZE TABLE <tablename> compute statistics FOR columns(col1, col2) WITH histogram columns (col1);The columns specified in WITH histogram columns (...) must be a subset of the columns listed in FOR columns (...). In the example above, col1 in WITH histogram columns (col1) is a subset of (col1, col2).
Collect column statistics for specific columns, histograms for a subset, and specify the bucket count
ANALYZE TABLE <tablename> compute statistics FOR columns(col1, col2) WITH histogram 256 buckets columns (col1);Verify and use histograms
Verify histogram collection
After running ANALYZE TABLE, display the collected statistics to confirm the histogram collection succeeded and inspect bucket values:
show statistic <tablename> columns;Enable histogram-based optimization
To use histograms for cardinality estimation during query planning, enable the histogram feature:
set odps.sql.optimizer.histogram.enable=true;Once enabled, the optimizer performs cardinality estimation based on histograms.