10.2.1 Example - Partition Pruning

The EXPLAIN statement displays the execution plan of a statement. You can use the EXPLAIN statement to confirm that Advanced Server is pruning partitions from the execution plan of a query.

To demonstrate the efficiency of partition pruning, first create a simple table:

CREATE TABLE sales

(

dept_no number,

part_no varchar2,

country varchar2(20),

date date,

amount number

)

PARTITION BY LIST(country)

(

PARTITION europe VALUES('FRANCE', 'ITALY'),

PARTITION asia VALUES('INDIA', 'PAKISTAN'),

PARTITION americas VALUES('US', 'CANADA')

);

Then, perform a constrained query that includes the EXPLAIN statement:

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';

The resulting query plan shows that the server will scan only the sales_asia table - the table in which a row with a country value of INDIA would be stored:

edb=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';

QUERY PLAN

---------------------------------------------------

Append

-> Seq Scan on sales_asia

Filter: ((country)::text = 'INDIA'::text)

(3 rows)

If you perform a query that searches for a row that matches a value not included in the partitioning key:

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';

The resulting query plan shows that the server must look in all of the partitions to locate the rows that satisfy the query:

edb=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';

QUERY PLAN

-------------------------------------------

Append

-> Seq Scan on sales_americas

Filter: (dept_no = '30'::numeric)

-> Seq Scan on sales_europe

Filter: (dept_no = '30'::numeric)

-> Seq Scan on sales_asia

Filter: (dept_no = '30'::numeric)

(7 rows)

Constraint exclusion also applies when querying subpartitioned tables:

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 "2011" VALUES LESS THAN('01-JAN-2012')

(

SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'),

SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'),

SUBPARTITION americas_2011 VALUES ('US', 'CANADA')

),

PARTITION "2012" VALUES LESS THAN('01-JAN-2013')

(

SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'),

SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'),

SUBPARTITION americas_2012 VALUES ('US', 'CANADA')

),
PARTITION "2013" VALUES LESS THAN('01-JAN-2015')

(

SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),

SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),

SUBPARTITION americas_2013 VALUES ('US', 'CANADA')

)

);

When you query the table, the query planner prunes any partitions or subpartitions from the search path that cannot possibly contain the desired result set:

edb=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

Append

-> Seq Scan on sales_americas_2012

Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))

(3 rows)