Statistics collection and update strategy for partitioned tables

In OceanBase Database, statistics management for partitioned tables is divided into partition-level statistics and global statistics. This statistics mechanism applies only to partitioned tables. For non-partitioned tables (that is, regular single tables), only a single set of global statistics exists, and no partition statistics or derivation logic is involved.

The partitioned table statistics mechanism in OceanBase has the following characteristics:

1. Distinguishes global and partition statistics: The two are stored independently and are differentiated by partition_id.
2. Collects both by default: Under the APPROX_GLOBAL AND PARTITION granularity, both global and partition statistics are collected at the same time.
3. Automatically detects missing statistics: The system automatically detects missing or stale statistics and triggers their collection.
4. Derives global statistics from partition statistics: Global basic statistics are derived from the basic statistics of the partitions, which improves the timeliness and accuracy of global statistics.

This topic describes in detail the statistics collection strategy and automatic update rules that OceanBase applies to partitioned tables.

Default collection strategy

By default, OceanBase collects statistics for all partitioned tables at the APPROX_GLOBAL AND PARTITION granularity (implemented through the GATHER_INDEX_STATS procedure of the DBMS_STATS package). Under this strategy, the system generates two types of statistics at the same time: partition-level statistics and global statistics.

Partition-level statistics

Collects independent statistics for each partition:

• Basic statistics: such as the minimum value (min), maximum value (max), and number of distinct values (NDV) of a column;
• Histograms: used to characterize the data distribution.

Global statistics

• Basic statistics (min, max, number of distinct values, and so on): derived and synthesized from the basic statistics of each partition (for example, global min = the minimum of the min values across all partitions);
• Global histograms: cannot be derived from the partition results and require sampling and collection over the entire table.

Global basic statistics do not require a full table scan, which greatly improves the efficiency of statistics collection for large-scale partitioned tables, and is especially suitable for TB/PB-scale AP scenarios.

Automatic update mechanism

To ensure the timeliness of statistics, OceanBase has a built-in automatic detection and update mechanism that triggers a refresh based on the proportion of changed data.

Trigger conditions for updating global statistics

Any of the following situations triggers an update of global statistics:

• Global statistics missing: No global statistics exist for the table.
• Global data stale: The proportion of modified rows across the entire table is ≥ 10% (controlled by STALE_PERCENT).
• Any partition statistics stale: Even if the global data has not changed, as long as the statistics of a partition become invalid, an update of global statistics is triggered to ensure consistency.

Update process:

• Global basic statistics: derived from partition-level statistics (min, max, number of distinct values, and so on).
• Global histograms: must be collected separately, because histograms currently cannot be derived and generated from the partition results.

Trigger conditions for updating partition-level statistics

Statistics for an individual partition are updated in the following situations:

• Statistics for that partition missing: A partition has no statistics.
• Data in that partition stale: The proportion of modified rows in the partition is ≥ 10% (controlled by STALE_PERCENT).

Update process:

• Re-collect the statistics of the stale partition.
• Re-collect the global statistics.

How to configure STALE_PERCENT

You can flexibly adjust the staleness threshold through the DBMS_STATS package:

-- Set the global default value (affects all new tables)
CALL dbms_stats.set_global_prefs('STALE_PERCENT', '50');

-- Set at the schema level
CALL dbms_stats.set_schema_prefs('MY_SCHEMA', 'STALE_PERCENT', '30');

-- Set at the table level
CALL dbms_stats.set_table_prefs('MY_SCHEMA', 'MY_TABLE', 'STALE_PERCENT', '20');

Automatic detection capabilities

The OceanBase system continuously performs the following detection tasks in the background:

• Statistics status check: Scans all existing global and partition statistics.
• Stale partition detection: Checks whether each partition is stale or missing.
• Missing statistics detection: Automatically marks missing items and adds them to the collection queue.
• Independent update triggering: Supports triggering updates of global and partition statistics independently, without affecting each other.

Key parameters

Comparison with non-partitioned tables

Example: Managing statistics of an OceanBase partitioned table

This example is based on OceanBase Database (MySQL mode). It shows how to create a partitioned table, view the collection and automatic update behavior of its statistics, and demonstrates how global statistics are derived and generated from partition statistics.

Step 1: Create a test partitioned table

-- Create the database and user (if they do not already exist)
CREATE DATABASE IF NOT EXISTS sales_db;
USE sales_db;

-- Create a sales record table partitioned by RANGE
CREATE TABLE sales (
    id BIGINT NOT NULL,
    region VARCHAR(50),
    amount DECIMAL(10,2),
    sale_date DATE,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE COLUMNS(sale_date) (
    PARTITION p2023_q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p2023_q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023_q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p2023_q4 VALUES LESS THAN ('2024-01-01')
);

Step 2: Insert initial data

-- Insert test data into each partition
INSERT INTO sales VALUES
(1, 'North', 1500.00, '2023-01-15'),
(2, 'South', 2300.50, '2023-02-20'),
(3, 'East', 980.75, '2023-04-10'),
(4, 'West', 3200.00, '2023-06-25'),
(5, 'North', 1800.25, '2023-07-05'),
(6, 'South', 2100.00, '2023-09-18');

Step 3: Collect statistics (default granularity: APPROX_GLOBAL AND PARTITION)

CALL DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'sales_db',
    tabname => 'sales',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    granularity => 'APPROX_GLOBAL AND PARTITION'
);

Step 4: View statistics

View global table-level statistics

SELECT
    TABLE_NAME,
    OBJECT_TYPE,
    NUM_ROWS,
    AVG_ROW_LEN,
    LAST_ANALYZED
FROM oceanbase.DBA_TAB_STATISTICS
WHERE OWNER = 'SALES_DB' AND TABLE_NAME = 'SALES'
ORDER BY OBJECT_TYPE;

Expected result:

+------------+-------------+----------+-------------+----------------------------+
| TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED              |
+------------+-------------+----------+-------------+----------------------------+
| sales      | PARTITION   |        2 |          73 | 2026-01-14 10:45:58.067339 |
| sales      | PARTITION   |        2 |          72 | 2026-01-14 10:45:58.067339 |
| sales      | PARTITION   |        2 |          73 | 2026-01-14 10:45:58.067339 |
| sales      | PARTITION   |        0 |           0 | 2026-01-14 10:45:58.067339 |
| sales      | TABLE       |        6 |          72 | 2026-01-14 10:45:58.079250 |
+------------+-------------+----------+-------------+----------------------------+
5 rows in set

View global column-level statistics (for example, the amount column)

SELECT
    COLUMN_NAME,
    NUM_DISTINCT,
    LOW_VALUE,
    HIGH_VALUE,
    NUM_NULLS,
    HISTOGRAM
FROM oceanbase.DBA_TAB_COL_STATISTICS
WHERE OWNER = 'SALES_DB'
  AND TABLE_NAME = 'SALES'
  AND COLUMN_NAME = 'AMOUNT';

Expected result:

+-------------+--------------+-----------+------------+-----------+-----------+
| COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | NUM_NULLS | HISTOGRAM |
+-------------+--------------+-----------+------------+-----------+-----------+
| amount      |            6 | 980.75    | 3200.00    |         0 | NULL      |
+-------------+--------------+-----------+------------+-----------+-----------+
1 row in set

View partition-level column statistics (for example, the amount column of the p2023_q1 partition)

SELECT
    PARTITION_NAME,
    COLUMN_NAME,
    NUM_DISTINCT,
    LOW_VALUE,
    HIGH_VALUE,
    NUM_NULLS
FROM oceanbase.DBA_PART_COL_STATISTICS
WHERE OWNER = 'SALES_DB'
  AND TABLE_NAME = 'SALES'
  AND PARTITION_NAME = 'P2023_Q1'
  AND COLUMN_NAME = 'AMOUNT';

Expected result:

+----------------+-------------+--------------+-----------+------------+-----------+
| PARTITION_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | NUM_NULLS |
+----------------+-------------+--------------+-----------+------------+-----------+
| p2023_q1       | amount      |            2 | 1500.00   | 2300.50    |         0 |
+----------------+-------------+--------------+-----------+------------+-----------+
1 row in set

Step 5: Verify the automatic update mechanism (optional)

-- Insert new data into p2023_q4 (simulating a change of >10%)
INSERT INTO sales
SELECT 1000 + n, 'Central', ROUND(RAND()*5000, 2), '2023-11-01'
FROM (SELECT @n := @n + 1 AS n FROM information_schema.columns a, information_schema.columns b, (SELECT @n := 0) r LIMIT 50) t;

-- Wait for background automatic detection (or trigger it manually)
CALL DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
-- At this point, if the change proportion is ≥ STALE_PERCENT (10% by default), the system automatically marks the statistics as stale and may trigger collection

-- Query again to check whether the LAST_ANALYZED time has been updated
SELECT PARTITION_NAME, LAST_ANALYZED
FROM oceanbase.DBA_TAB_STATISTICS
WHERE OWNER = 'SALES_DB' AND TABLE_NAME = 'SALES'
ORDER BY PARTITION_NAME;

Expected result:

+----------------+----------------------------+
| PARTITION_NAME | LAST_ANALYZED              |
+----------------+----------------------------+
| NULL           | 2026-01-14 10:45:58.079250 |
| p2023_q1       | 2026-01-14 10:45:58.067339 |
| p2023_q2       | 2026-01-14 10:45:58.067339 |
| p2023_q3       | 2026-01-14 10:45:58.067339 |
| p2023_q4       | 2026-01-14 10:45:58.067339 |
+----------------+----------------------------+
5 rows in set

Related documents

The reference documents for the related PL procedures are as follows:

• SET_GLOBAL_PREFS in MySQL mode
• SET_SCHEMA_PREFS in MySQL mode
• SET_TABLE_PREFS in MySQL mode
• SET_GLOBAL_PREFS in Oracle mode
• SET_SCHEMA_PREFS in Oracle mode
• SET_TABLE_PREFS in Oracle mode