Query a specified partition
In addition to performing partition pruning based on the conditions specified in an SQL statement, OceanBase Database allows you to directly specify the partition to access in an SQL statement. For example, in the following statement, the partition(p0) function specifies that only the p0 partition is accessed:
obclient> SELECT * FROM t1 partition (p0);
If you specify a partition in an SQL statement, OceanBase not only limits the query to the specified partition, but also performs partition pruning based on the conditions specified in the statement. The final accessed part is the intersection of the specified partition and the result of partition pruning.
Partition naming conventions
For a list- or range- partitioned table, the partition names are specified when the table is created. For a HASH- or KEY- partitioned table, if no partition names are specified when the table is created, the system generates the names based on the following naming conventions: If HASH or KEY appears in partitions, the partitions are named as p0, p1, ..., pn. If HASH or KEY appears in subpartitions, the partitions are named as sp0, sp1, ..., spn.
Subpartitions are named in the parition+subpartition format. For example, in p0sp0, p0 indicates the partition and sp0 indicates the subpartition.
Get subpartition IDs
The partition ID of a subpartition is a very large number.
The following example shows the IDs of partitions in the t1 table:
obclient> CREATE TABLE t1
(
c1 INT,
c2 INT
)
PARTITION BY hash(c1)
SUBPARTITION BY RANGE(c2)
SUBPARTITION template
(
SUBPARTITION sp0 VALUES less than(100),
SUBPARTITION sp1 VALUES less than(200)
) partitions 5
obclient> SELECT partition_id FROM __all_meta_table JOIN __all_table using(table_id)
WHERE table_name = 't1';
+---------------------+
| partition_id |
+---------------------+
| 1152921504875282432 |
| 1152921504875282433 |
| 1152921509170249728 |
| 1152921509170249729 |
| 1152921513465217024 |
| 1152921513465217025 |
| 1152921517760184320 |
| 1152921517760184321 |
| 1152921522055151616 |
| 1152921522055151617 |
+---------------------+
Partition-wise join
When the tables you want to join are partitioned in the same way, you can use partition-wise join to boost the performance of the join operation. For more information about partition-wise join, see our SQL Tuning manual.
