Data Partition Query Optimization

Partitioned tables help you query data by using predicates based on partition keys. For example, a table uses Month as the partition key, as shown in Figure 1. If the table structure is designed in ordinary table mode, full table scan is required. If the table is redesigned using the date as the partition key, the original full table scan is optimized to partition scan. When a table contains a large amount of data and spans a long period of time, the performance improvement brought by reduction of data to be scanned is obvious, as shown in Figure 2.

Figure 1 Example of a partitioned table

Figure 2 Example of partition pruning