Partition selection and pruning

In this section, you will see how partitioning can optimize SQL statements clause execution with the optimizer known as partition pruning, and the use of SQL statements to effectively use partition data for selection and perform modification operations on the partitioning.

Partition pruning

Partition pruning is related to the optimization concept in partition. In partition pruning the concept described as Do not scan partitions where no possible matching values can be present is applied based on the query statements.

Suppose there is a partitioned table, tp1, created with the following statement:

CREATE TABLE tp1 (
    first_name VARCHAR (30) NOT NULL,
    last_name VARCHAR (30) NOT NULL,
    zone_code TINYINT UNSIGNED NOT NULL,
    doj DATE NOT NULL
)
PARTITION BY RANGE (zone_code) (
    PARTITION p0 VALUES LESS THAN (65),
    PARTITION p1 VALUES LESS THAN (129),
    PARTITION p2 VALUES LESS THAN (193),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

In the preceding...