You can improve performance by specifying multiple key columns for a RANGE partitioned table.
If you often select rows by using comparison operators (based on a greater-than or less-than value) on a small set of columns, consider using these columns in RANGE partitioning rules.
Specify multiple key columns in a range-partitioned table
A range-partitioned table definition may include multiple columns in the partition
key. To specify multiple partition key columns for a range-partitioned table, you
must include the column names in a comma-separated list after the PARTITION BY RANGE
clause:
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)
);
If a table has multiple partition key columns, you must specify multiple key values
when querying the table to take full advantage of partition pruning.
acctg=# EXPLAIN SELECT * FROM sales WHERE sale_year = 2012 AND sale_month = 8;
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 with a value of 8 in the sale_month column and a value of 2012 in the sale_year column will be stored in the q3_2012 partition. PolarDB for Oracle will only search this partition.