本文介绍如何查询指定分区和指定子分区。

语法

  • 查询指定分区:
    SELECT  ... FROM table_name PARTITION ( partition_name );
  • 查询指定子分区:
    SELECT  ... FROM table_name SUBPARTITION ( subpartition_name );

参数

参数 说明
table_name 表名。
partition_name 指定分区名。
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)