Note
This view is available starting with V4.0.0.
Purpose
This view displays the histogram data of subpartitions for all subpartitioned tables in the current tenant (the endpoints of each histogram).
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | varchar(128) | NO | The owner of the table. |
| TABLE_NAME | varchar(128) | NO | The name of the table to which the histogram belongs. |
| SUBPARTITION_NAME | varchar(128) | NO | The name of the subpartition to which the histogram belongs. |
| COLUMN_NAME | varchar(128) | NO | The name of the column to which the histogram belongs. |
| ENDPOINT_NUMBER | decimal(10,0) | NO | The cumulative frequency of the bucket. |
| ENDPOINT_VALUE | decimal(10,0) | NO | The normalized endpoint value of the bucket. |
| ENDPOINT_ACTUAL_VALUE | text | NO | The endpoint value of the bucket. |
| ENDPOINT_ACTUAL_VALUE_RAW | text | NO | The binary endpoint value of the bucket. |
| ENDPOINT_REPEAT_COUNT | decimal(10,0) | NO | The frequency of the endpoint value of the bucket. |
Sample query
Query the histogram information of the subpartitions of the sales_subpart table in the current tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_SUBPART_HISTOGRAMS WHERE TABLE_NAME ='sales_subpart';
The query result is as follows:
+----------+---------------+-------------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
| OWNER | TABLE_NAME | SUBPARTITION_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_VALUE | ENDPOINT_ACTUAL_VALUE | ENDPOINT_ACTUAL_VALUE_RAW | ENDPOINT_REPEAT_COUNT |
+----------+---------------+-------------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
| test | sales_subpart | p2023sp0 | quantity | 127 | NULL | 1 | 04053F0001 | 127 |
| test | sales_subpart | p2023sp0 | product_id | 127 | NULL | 8 | 04053F0008 | 63 |
| test | sales_subpart | p2023sp0 | product_id | 64 | NULL | 4 | 04053F0004 | 64 |
| test | sales_subpart | p2023sp1 | quantity | 190 | NULL | 1 | 04053F0001 | 190 |
| test | sales_subpart | p2023sp1 | product_id | 190 | NULL | 9 | 04053F0009 | 63 |
| test | sales_subpart | p2023sp1 | product_id | 127 | NULL | 5 | 04053F0005 | 63 |
| test | sales_subpart | p2023sp1 | product_id | 64 | NULL | 1 | 04053F0001 | 64 |
| test | sales_subpart | p2023sp2 | quantity | 127 | NULL | 1 | 04053F0001 | 127 |
| test | sales_subpart | p2023sp2 | product_id | 127 | NULL | 6 | 04053F0006 | 63 |
| test | sales_subpart | p2023sp2 | product_id | 64 | NULL | 2 | 04053F0002 | 64 |
| test | sales_subpart | p2023sp3 | quantity | 191 | NULL | 1000 | 04053F00E807 | 6 |
| test | sales_subpart | p2023sp3 | quantity | 185 | NULL | 1 | 04053F0001 | 185 |
| test | sales_subpart | p2023sp3 | product_id | 191 | NULL | 999 | 04053F00E707 | 64 |
| test | sales_subpart | p2023sp3 | product_id | 127 | NULL | 7 | 04053F0007 | 63 |
| test | sales_subpart | p2023sp3 | product_id | 64 | NULL | 3 | 04053F0003 | 64 |
| test | sales_subpart | p2024sp0 | quantity | 73 | NULL | 1 | 04053F0001 | 73 |
| test | sales_subpart | p2024sp0 | product_id | 73 | NULL | 8 | 04053F0008 | 37 |
| test | sales_subpart | p2024sp0 | product_id | 36 | NULL | 4 | 04053F0004 | 36 |
| test | sales_subpart | p2024sp1 | quantity | 110 | NULL | 1 | 04053F0001 | 110 |
| test | sales_subpart | p2024sp1 | product_id | 110 | NULL | 9 | 04053F0009 | 37 |
| test | sales_subpart | p2024sp1 | product_id | 73 | NULL | 5 | 04053F0005 | 37 |
| test | sales_subpart | p2024sp1 | product_id | 36 | NULL | 1 | 04053F0001 | 36 |
| test | sales_subpart | p2024sp2 | quantity | 73 | NULL | 1 | 04053F0001 | 73 |
| test | sales_subpart | p2024sp2 | product_id | 73 | NULL | 6 | 04053F0006 | 37 |
| test | sales_subpart | p2024sp2 | product_id | 36 | NULL | 2 | 04053F0002 | 36 |
| test | sales_subpart | p2024sp3 | quantity | 109 | NULL | 1000 | 04053F00E807 | 4 |
| test | sales_subpart | p2024sp3 | quantity | 105 | NULL | 1 | 04053F0001 | 105 |
| test | sales_subpart | p2024sp3 | product_id | 109 | NULL | 999 | 04053F00E707 | 36 |
| test | sales_subpart | p2024sp3 | product_id | 73 | NULL | 7 | 04053F0007 | 37 |
| test | sales_subpart | p2024sp3 | product_id | 36 | NULL | 3 | 04053F0003 | 36 |
+----------+---------------+-------------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
30 rows in set
References
Query the histogram statistics at the table level: DBA_TAB_HISTOGRAMS
Query the histogram statistics at the partition level: DBA_PART_HISTOGRAMS
Query the column statistics at the table level: DBA_TAB_STATISTICS
Query the column statistics at the global level: DBA_TAB_COL_STATISTICS
Query the column statistics at the partition level: DBA_PART_COL_STATISTICS
Query the column statistics at the subpartition level: DBA_SUBPART_HISTOGRAMS
Query the index statistics: DBA_IND_STATISTICS
For more information about how to collect statistics, see the following topics: