This topic describes how to query data from a specified partition of a table.
Purpose
In addition to partition pruning based on the query conditions in a SQL statement, OceanBase AI Database allows you to directly specify the partitions to access in the SQL statement.
If partitions are specified in the SQL statement, OceanBase AI Database not only limits the query to the specified partitions but also performs partition pruning based on the conditions specified in the statement. The final accessed partitions are the intersection of the specified partitions and the result of partition pruning.
Syntax
SELECT select_expr_list FROM table_name PARTITION (partition_name_list) [WHERE where_list];
Examples
Create a template-based RANGE + RANGE subpartitioned table tbl_m_rr and insert data into it.
obclient> CREATE TABLE tbl_m_rr(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION mp0 VALUES LESS THAN(1000),
SUBPARTITION mp1 VALUES LESS THAN(2000),
SUBPARTITION mp2 VALUES LESS THAN(3000)
)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200),
PARTITION p2 VALUES LESS THAN(300)
);
Query OK, 0 rows affected
obclient> INSERT INTO tbl_m_rr VALUES(10,500),(20,1500),(50,1100),(120,1500),(150,1800),(150,2800);
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
Query all data from the
tbl_m_rrtable.obclient> SELECT * FROM tbl_m_rr; +------+------+ | COL1 | COL2 | +------+------+ | 10 | 500 | | 20 | 1500 | | 50 | 1100 | | 120 | 1500 | | 150 | 1800 | | 150 | 2800 | +------+------+ 6 rows in setQuery data from partition
p0of thetbl_m_rrtable.obclient> SELECT * FROM tbl_m_rr PARTITION(p0); +------+------+ | COL1 | COL2 | +------+------+ | 10 | 500 | | 20 | 1500 | | 50 | 1100 | +------+------+ 3 rows in setQuery data from partition
p0and subpartitionp1smp1of thetbl_m_rrtable.obclient> SELECT * FROM tbl_m_rr PARTITION (p0,p1smp1); +------+------+ | COL1 | COL2 | +------+------+ | 10 | 500 | | 20 | 1500 | | 50 | 1100 | | 120 | 1500 | | 150 | 1800 | +------+------+ 5 rows in set