PolarDB:Query a specified partition

Syntax

Query a specified partition:

SELECT ... FROM table_name PARTITION ( partition_name );

Query a specified subpartition:

SELECT ... FROM table_name SUBPARTITION ( subpartition_name );

Parameters

Examples

The following examples use a sales table partitioned by date range, with each partition further subpartitioned by country list.

Set up the table and data:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST(country)
  (
    PARTITION q1_2012
      VALUES LESS THAN('2012-Apr-01')
      (
        SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q1_americas VALUES ('US', 'CANADA')
       ),
    PARTITION q2_2012
      VALUES LESS THAN('2012-Jul-01')
      (
        SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q2_americas VALUES ('US', 'CANADA')
       ),
    PARTITION q3_2012
      VALUES LESS THAN('2012-Oct-01')
      (
        SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q3_americas VALUES ('US', 'CANADA')
       ),
    PARTITION q4_2012
      VALUES LESS THAN('2013-Jan-01')
      (
        SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q4_americas VALUES ('US', 'CANADA')
       )
  );

INSERT INTO sales VALUES (10, '4519b', 'FRANCE', '17-Jan-2012', '45000');
INSERT INTO sales VALUES (20, '3788a', 'INDIA', '01-Mar-2012', '75000');
INSERT INTO sales VALUES (40, '9519b', 'US', '12-Apr-2012', '145000');
INSERT INTO sales VALUES (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500');
INSERT INTO sales VALUES (40, '4577b', 'US', '11-Nov-2012', '25000');
INSERT INTO sales VALUES (30, '7588b', 'CANADA', '14-Dec-2012', '50000');
INSERT INTO sales VALUES (30, '9519b', 'CANADA', '01-Feb-2012', '75000');
INSERT INTO sales VALUES (30, '4519b', 'CANADA', '08-Apr-2012', '120000');
INSERT INTO sales VALUES (40, '3788a', 'US', '12-May-2012', '4950');
INSERT INTO sales VALUES (10, '9519b', 'ITALY', '07-Jul-2012', '15000');
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');
INSERT INTO sales VALUES (10, '9519b', 'FRANCE', '18-Aug-2012', '650000');
INSERT INTO sales VALUES (20, '3788b', 'INDIA', '21-Sept-2012', '5090');
INSERT INTO sales VALUES (40, '4788a', 'US', '23-Sept-2012', '4950');
INSERT INTO sales VALUES (40, '4788b', 'US', '09-Oct-2012', '15000');
INSERT INTO sales VALUES (20, '4519a', 'INDIA', '18-Oct-2012', '650000');
INSERT INTO sales VALUES (20, '4519b', 'INDIA', '2-Dec-2012', '5090');

Query a partition:

select * from sales partition(q1_2012);

Output:

 dept_no | part_no | country |           date           | amount
---------+---------+---------+--------------------------+--------
      30 | 9519b   | CANADA  | Wed Feb 01 00:00:00 2012 |  75000
      10 | 4519b   | FRANCE  | Tue Jan 17 00:00:00 2012 |  45000
      20 | 3788a   | INDIA   | Thu Mar 01 00:00:00 2012 |  75000
(3 rows)

Query a subpartition:

select * from sales subpartition(q3_europe);

Output:

 dept_no | part_no | country |           date           | amount
---------+---------+---------+--------------------------+--------
      10 | 9519b   | ITALY   | Sat Jul 07 00:00:00 2012 |  15000
      10 | 9519a   | FRANCE  | Sat Aug 18 00:00:00 2012 | 650000
      10 | 9519b   | FRANCE  | Sat Aug 18 00:00:00 2012 | 650000
(3 rows)