Partition pruning

Partition pruning is a performance optimization technique used in databases that store data in partitioned tables. Partitioning is a technique used to divide a large table into smaller, more manageable parts called partitions, based on a partition key. Each partition is a separate physical segment of the table, and data is stored in each partition based on the partition key.

Partition pruning is the process of eliminating partitions that do not need to be scanned based on the conditions specified in a SQL query. When a query is executed on a partitioned table, the optimizer checks the WHERE clause of the query to see if it includes a predicate that matches the partition key. If a partition key is used in the WHERE clause, the optimizer can prune partitions that do not need to be scanned, based on the partition key values in the query.

For example, consider a partitioned table that is partitioned by date, with each partition representing a month of data. If a query is executed with a date range in the WHERE clause, the optimizer can prune partitions that fall outside of the date range, and only scan the partitions that contain data within the specified date range. This can significantly reduce the amount of data that needs to be scanned and improve query performance.

Partition pruning is a powerful technique that can significantly improve query performance on partitioned tables. It is important to design partitioning schemes and queries with partition pruning in mind, to ensure that the optimizer can take advantage of this optimization technique.