In OceanBase AI Database, partitioned table statistics are managed as partition-level statistics and global statistics, and this mechanism applies only to partitioned tables. For non-partitioned tables (i.e., regular single tables), there is only a single global statistic, without any partition-based statistics or derivation logic.
The partitioned table statistics mechanism in OceanBase has the following characteristics:
- Separate global and partition statistics: Stored independently and distinguished by
partition_id. - Default simultaneous collection: Global and partition statistics are collected simultaneously at the
APPROX_GLOBAL AND PARTITIONgranularity. - Automatic detection of missing statistics: Automatically detects missing or outdated statistics and triggers collection.
- Derive global statistics from partition statistics: Derives global basic statistics based on partition statistics, improving the timeliness and accuracy of global statistics.
This topic describes in detail the statistics collection strategy and automatic update rules adopted by OceanBase for partitioned tables.
Default collection strategy
OceanBase collects statistics for all partitioned tables by default at the APPROX_GLOBAL AND PARTITION granularity (implemented through the GATHER_INDEX_STATS procedure in the DBMS_STATS package). Under this strategy, the system generates two types of statistics simultaneously: partition-level statistics and global statistics.
Partition-level statistics
Collects independent statistics for each partition:
- Basic statistics: such as column minimum value (min), maximum value (max), number of distinct values (NDV), etc.;
- Histogram: used to describe data distribution characteristics.
Global statistics
- Basic statistics (min, max, number of distinct values, etc.): Derived and synthesized from the basic statistics of each partition (for example, global min = the minimum of all partition mins).
- Global histogram: Cannot be derived from partition results; requires sampling and collection across the entire table.
Global basic statistics do not require a full table scan, greatly improving the efficiency of statistics collection for large-scale partitioned tables, 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 refreshes based on the data change ratio.
Global statistics update trigger conditions
Any of the following conditions will trigger a global statistics update:
- Missing global statistics: No global statistics exist for the table.
- Global data expiration: The proportion of rows modified in the entire table ≥ 10% (controlled by
STALE_PERCENT). - Expiration of statistics for any partition: Even if global data remains unchanged, an update is triggered as soon as statistics for any partition become invalid to maintain consistency.
Update process:
- Global basic statistics: Derived from partition-level statistics (min, max, number of distinct values, etc.).
- Global histogram: Requires separate collection, as histograms cannot be derived from partition results at present.
Trigger conditions for partition-level statistics update
Statistics for a single partition are updated in the following cases:
- Statistics missing for the partition: The statistics for a particular partition do not exist.
- Partition data expired: The proportion of modified rows in this partition is ≥ 10% (controlled by
STALE_PERCENT).
Update process:
- Recollect statistics for the expired partition.
- Recollect global statistics.
Configure STALE_PERCENT
You can flexibly adjust the expiration threshold using the DBMS_STATS package:
-- Set global default values (applies to all new tables)
CALL dbms_stats.set_global_prefs('STALE_PERCENT', '50');
-- Set 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 background detection tasks:
- Statistics status check: Scans all existing global and partition statistics.
- Expired partition detection: Checks whether each partition is expired or missing.
- Missing statistics detection: Automatically marks missing items and adds them to the collection queue.
- Independent trigger for updates: Supports independent triggering of global and partition statistics updates, with no mutual impact.
Key parameters
Parameter |
Default value |
Description |
|---|---|---|
STALE_PERCENT |
10 (10%) | Parameter of the stored procedures (set_schema_prefs and set_table_prefs) that specifies the change threshold for determining whether statistics are stale. |
| Default collection granularity | APPROX_GLOBAL AND PARTITION |
Applies only to partitioned tables. For non-partitioned tables, use GLOBAL (implemented by the GATHER_INDEX_STATS procedure in the DBMS_STATS package). |
Comparison with non-partitioned tables
Feature |
Partitioned table |
Non-partitioned table (single table) |
|---|---|---|
| Statistics type | Global + partition | Global only |
| Source of global basic statistics | Derived from partition statistics | Generated by a full table scan |
| Partition pruning optimization supported | Yes | No |
| Default collection granularity | APPROX_GLOBAL AND PARTITION |
GLOBAL |
Example: Manage statistics for an OceanBase partitioned table
This example shows how to create a partitioned table, view its statistics collection and automatic update behavior, and demonstrates how global statistics are derived from partition statistics.
Step 1: Create a test partitioned table
-- Create a database and a user (if they do not 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 (such as 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 (such as the amount column in 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 auto-update mechanism (Optional)
-- Insert new data into p2023_q4 (simulating >10% change)
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;
-- Waiting for automatic backend detection (or manually triggered)
CALL DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
-- If the change ratio is greater than or equal to STALE_PERCENT (the default is 10%), the system automatically marks the data as stale and may trigger collection.
-- Query whether the LAST_ANALYZED time has been updated again.
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
References
The following are the reference documents for related PL procedures: