PolarDB：查詢指定分區

文法

• 查詢指定分區：

  SELECT  ... FROM table_name PARTITION ( partition_name );

• 查詢指定子分區：

  SELECT  ... FROM table_name SUBPARTITION ( subpartition_name );

參數

樣本

建立一個用於測試的表sales，並在表中插入測試資料。樣本如下：

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');

• 查詢指定分區，樣本如下：

  select * from sales partition(q1_2012);

  返回樣本如下：

   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)

• 查詢指定子分區，樣本如下：

  select * from sales subpartition(q3_europe);

  返回樣本如下：

   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)