Configure a RANGE partitioned table with multiple partition key columns - PolarDB - Alibaba Cloud - PolarDB

When queries filter rows by comparison operators (greater than or less than) on a small set of columns, you can use those columns together as partition key columns in a RANGE partitioned table. At query time, PolarDB for Oracle uses the partition key columns in the WHERE clause to skip entire partitions—potentially scanning one partition instead of all partitions.

Specify multiple partition key columns

List the column names in a comma-separated list after the PARTITION BY RANGE clause. The following example partitions a sales table by both sale_year and sale_month, creating one partition per quarter:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  sale_year    number,
  sale_month   number,
  sale_day     number,
  amount      number
)
PARTITION BY RANGE(sale_year, sale_month)
(
  PARTITION q1_2012
    VALUES LESS THAN(2012, 4),
  PARTITION q2_2012
    VALUES LESS THAN(2012, 7),
  PARTITION q3_2012
    VALUES LESS THAN(2012, 10),
  PARTITION q4_2012
    VALUES LESS THAN(2013, 1)
);

Use partition pruning with multiple partition key columns

To take full advantage of partition pruning, include all partition key columns in the WHERE clause.

The following query filters on both sale_year and sale_month:

acctg=# EXPLAIN SELECT * FROM sales WHERE sale_year = 2012 AND sale_month = 8;

Output:

                                   QUERY PLAN
----------------------------------------------------------------------------- Result  (cost=0.00..14.35 rows=2 width=250)
   ->  Append  (cost=0.00..14.35 rows=2 width=250)
         ->  Seq Scan on sales  (cost=0.00..0.00 rows=1 width=250)
               Filter: ((sale_year = 2012::numeric) AND (sale_month = 8::numeric))
         ->  Seq Scan on sales_q3_2012 sales  (cost=0.00..14.35 rows=1 width=250)
               Filter: ((sale_year = 2012::numeric) AND (sale_month = 8::numeric))
(6 rows)

All rows where sale_year = 2012 and sale_month = 8 are stored in the q3_2012 partition. PolarDB for Oracle searches only that partition instead of scanning all four.