Query data in specified partitions

This topic describes how to query data in a specified partition.

Overview

In addition to partition pruning based on the query conditions in an SQL statement, OceanBase Database also allows you to specify partitions to access in an SQL statement.

If you specify partitions in an SQL statement, the system limits the query scope to the specified partitions and also performs partition pruning based on the query conditions. The final partitions accessed are the intersection of the specified partitions and the partition pruning result.

Syntax

SELECT select_expr_list FROM table_name PARTITION (partition_name_list) [WHERE where_list];

Example

Create a RANGE-RANGE-subpartitioned table named tbl_m_rr by using a template, and then insert data into the table.

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),(50,1100),(120,1500),(150,1800),(150,2800),(200,2900);
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0

• Query all data in the tbl_m_rr table.

  obclient> SELECT * FROM tbl_m_rr;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |   10 |  500 |
  |   20 | 1500 |
  |   50 | 1100 |
  |  120 | 1500 |
  |  150 | 1800 |
  |  150 | 2800 |
  +------+------+
  6 rows in set
  

• Query data in the p0 partition of the tbl_m_rr table.

  obclient> SELECT * FROM tbl_m_rr PARTITION(p0);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |   10 |  500 |
  |   20 | 1500 |
  |   50 | 1100 |
  +------+------+
  3 rows in set
  

• Query data in the p0 partition and the p1smp1 subpartition of the tbl_m_rr table.

  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