Note
This view is introduced in V3.2.1.
Purpose
This view displays the histogram information of all subpartitions in the partitioned tables that the current user can access.
Applicability
This view is applicable only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The user to which the histogram belongs. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table to which the histogram belongs. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition to which the histogram belongs. |
| COLUMN_NAME | VARCHAR2(128) | NO | The name of the column to which the histogram belongs. |
| ENDPOINT_NUMBER | NUMBER | NO | The cumulative frequency of the bucket. |
| ENDPOINT_VALUE | NUMBER | NO | The normalized endpoint value of the bucket. |
| ENDPOINT_ACTUAL_VALUE | VARCHAR2(4000) | NO | The endpoint value of the bucket. |
| ENDPOINT_ACTUAL_VALUE_RAW | VARCHAR2(4000) | NO | The binary endpoint value of the bucket. |
| ENDPOINT_REPEAT_COUNT | NUMBER | NO | The frequency of the endpoint value of the bucket. |
Sample query
Query the histogram information of all subpartitions in the partitioned tables that the current user can access for the T_SUBPART table.
obclient [SYS]> SELECT * FROM SYS.ALL_PART_HISTOGRAMS WHERE TABLE_NAME ='T_SUBPART';
The query result is as follows:
+-------+------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
| OWNER | TABLE_NAME | PARTITION_NAME | COLUMN_NAME | ENDPOINT_NUMBER | ENDPOINT_VALUE | ENDPOINT_ACTUAL_VALUE | ENDPOINT_ACTUAL_VALUE_RAW | ENDPOINT_REPEAT_COUNT |
+-------+------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
| SYS | T_SUBPART | P3 | QUANTITY | 200 | NULL | 1000 | 1000 | 2 |
| SYS | T_SUBPART | P3 | QUANTITY | 198 | NULL | 1 | 1 | 198 |
| SYS | T_SUBPART | P3 | ID | 200 | NULL | 999 | 999 | 20 |
| SYS | T_SUBPART | P3 | ID | 180 | NULL | 9 | 9 | 20 |
| SYS | T_SUBPART | P3 | ID | 160 | NULL | 8 | 8 | 20 |
| SYS | T_SUBPART | P3 | ID | 140 | NULL | 7 | 7 | 20 |
| SYS | T_SUBPART | P3 | ID | 120 | NULL | 6 | 6 | 20 |
| SYS | T_SUBPART | P3 | ID | 100 | NULL | 5 | 5 | 20 |
| SYS | T_SUBPART | P3 | ID | 80 | NULL | 4 | 4 | 20 |
| SYS | T_SUBPART | P3 | ID | 60 | NULL | 3 | 3 | 20 |
| SYS | T_SUBPART | P3 | ID | 40 | NULL | 2 | 2 | 20 |
| SYS | T_SUBPART | P3 | ID | 20 | NULL | 1 | 1 | 20 |
| SYS | T_SUBPART | P2 | QUANTITY | 300 | NULL | 1000 | 1000 | 3 |
| SYS | T_SUBPART | P2 | QUANTITY | 297 | NULL | 1 | 1 | 297 |
| SYS | T_SUBPART | P2 | ID | 300 | NULL | 999 | 999 | 30 |
| SYS | T_SUBPART | P2 | ID | 270 | NULL | 9 | 9 | 30 |
| SYS | T_SUBPART | P2 | ID | 240 | NULL | 8 | 8 | 30 |
| SYS | T_SUBPART | P2 | ID | 210 | NULL | 7 | 7 | 30 |
| SYS | T_SUBPART | P2 | ID | 180 | NULL | 6 | 6 | 30 |
| SYS | T_SUBPART | P2 | ID | 150 | NULL | 5 | 5 | 30 |
| SYS | T_SUBPART | P2 | ID | 120 | NULL | 4 | 4 | 30 |
| SYS | T_SUBPART | P2 | ID | 90 | NULL | 3 | 3 | 30 |
| SYS | T_SUBPART | P2 | ID | 60 | NULL | 2 | 2 | 30 |
| SYS | T_SUBPART | P2 | ID | 30 | NULL | 1 | 1 | 30 |
| SYS | T_SUBPART | P1 | QUANTITY | 500 | NULL | 1000 | 1000 | 5 |
| SYS | T_SUBPART | P1 | QUANTITY | 495 | NULL | 1 | 1 | 495 |
| SYS | T_SUBPART | P1 | ID | 500 | NULL | 999 | 999 | 50 |
| SYS | T_SUBPART | P1 | ID | 450 | NULL | 9 | 9 | 50 |
| SYS | T_SUBPART | P1 | ID | 400 | NULL | 8 | 8 | 50 |
| SYS | T_SUBPART | P1 | ID | 350 | NULL | 7 | 7 | 50 |
| SYS | T_SUBPART | P1 | ID | 300 | NULL | 6 | 6 | 50 |
| SYS | T_SUBPART | P1 | ID | 250 | NULL | 5 | 5 | 50 |
| SYS | T_SUBPART | P1 | ID | 200 | NULL | 4 | 4 | 50 |
| SYS | T_SUBPART | P1 | ID | 150 | NULL | 3 | 3 | 50 |
| SYS | T_SUBPART | P1 | ID | 100 | NULL | 2 | 2 | 50 |
| SYS | T_SUBPART | P1 | ID | 50 | NULL | 1 | 1 | 50 |
+-------+------------+----------------+-------------+-----------------+----------------+-----------------------+---------------------------+-----------------------+
36 rows in set
References
View the histograms of all partitions of all partitioned tables in the current tenant: DBA_PART_HISTOGRAMS
View the histograms of all partitions of all partitioned tables owned by the current user: USER_PART_HISTOGRAMS
View the histograms of tables by querying the following views:
View the histograms of subpartitions by querying the following views:
View the column statistics of tables by querying the following views:
View the global column statistics by querying the following views:
View the column statistics of partitions by querying the following views:
View the column statistics of subpartitions by querying the following views:
View the index statistics by querying the following views:
For more information about how to collect statistics, see the following topics: