Query data in specified partitions

This topic describes how to query data in specified partitions.

Overview

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

If you specify partitions in an SQL statement, OceanBase Database not only limits the query to the specified partitions but also performs partition pruning based on the conditions specified in the statement. The finally 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 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),(20,1500),(50,1100),(120,1500),(150,1800),(150,2800);
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