Partition pruning is an optimization, limiting the scope of your query according to the criteria you have specified. It is the skipping of unnecessary data partitions in a query. By doing this, you can greatly reduce the amount of data output from the disk and time spent on processing data as well as improve query performance and resource availability.
The following is an example of creating the olympic2 table to be partitioned based on the year the Olympic Games were held, and retrieving the countries that participated in the Olympic Games since the 2000 Sydney Olympic Games.
In the WHERE clause, partition pruning takes place when equality or range comparison is performed between a partition key and a constant value. In this example, the before_1996 partition that has a smaller year value than 2000 is not scanned.
CREATE TABLE olympic2
( opening_date DATE, host_nation VARCHAR(40))
PARTITION BY RANGE ( EXTRACT (YEAR FROM opening_date) )
( PARTITION before_1996 VALUES LESS THAN (1996),
PARTITION before_MAX VALUES LESS THAN MAXVALUE );
SELECT opening_date, host_nation FROM olympic2 WHERE EXTRACT ( YEAR FROM (opening_date)) >= 2000;
The following is an example of showing the method of getting the effects of partition pruning by retrieving data with a specific partition when partition pruning does not occur. In the first query, partition pruning does not occur because the value compared is not in the same format as that of the partition expression.
Therefore, you can use the same effect of partition pruning by specifying the appropriate partition as shown in the second query.
SELECT host_nation FROM olympic2 WHERE opening_date >= '2000 - 01 - 01';
SELECT host_nation FROM olympic2__p__before_max WHERE opening_date >= '2000 - 01 - 01';
The following is an example of specifying the search condition to make a partition pruning in the hash partitioned table, called the manager table.
For hash partitioning, partition pruning occurs only when equality comparison is performed between a partition key and a constant value in the WHERE clause.
CREATE TABLE manager (
PARTITION BY HASH ( code) PARTITIONS 4;
SELECT * FROM manager WHERE code = 10053;