PolarDB for Oracle supports partition pruning. The optimizer automatically extracts the partitions that need to be scanned from the FROM and WHERE clauses based on partition keys. This prevents full table scans and reduces the number of data blocks to be scanned. This way, query performance is improved.

The partition pruning mechanism uses the following two pruning techniques:

  • Constraint exclusion
  • Fast pruning

Constraint exclusion

The constraint_exclusion parameter is used to control constraint exclusion. Valid values are on, off, and partition. The default value is partition. To enable constraint exclusion, set the constraint_exclusion parameter to partition or on. To disable constraint exclusion, set the constraint_exclusion parameter to off.

If constraint exclusion is enabled, the server checks the constraints that are defined for each partition to determine whether the partition can satisfy a query.
  • When you execute a SELECT statement that does not contain a WHERE clause, the query planner recommends an execution plan that searches the entire table.
  • When you execute a SELECT statement that contains a WHERE clause, the query planner determines a partition to store the record and sends query fragments to the partition. This excludes the partitions that cannot contain the record from the execution plan.

If you do not use partitioned tables, we recommend that you disable constraint exclusion to improve performance.

Fast pruning

The fast pruning technique can optimize query statements only if the statements contain WHERE clauses and the qualifiers in the WHERE clauses match a specific format. This limit is the same as the limit on the constraint exclusion technique.

The polar_comp_enable_pruning parameter is used to control fast pruning. Valid values are on and off. The default value is on. If the polar_comp_enable_pruning parameter is set to on, fast pruning is enabled to fast prune specific queries. If the polar_comp_enable_pruning parameter is set to off, fast pruning is disabled.

Note Fast pruning cannot optimize queries on subpartitioned tables or queries on range-partitioned tables that are partitioned on multiple columns.

The following part describes how to use fast pruning:

  • For queries on list-partitioned tables, PolarDB for Oracle can fast prune queries that contain WHERE clauses. The operators that can be used in the WHERE clauses are the equal sign (=), IS NULL, and IS NOT NULL.

    The following code provides an example:

    1. Create a list-partitioned table.
      CREATE TABLE sales_hist(..., country text, ...) 
          PARTITION BY LIST(country) (
          PARTITION americas VALUES('US', 'CA', 'MX'), 
          PARTITION europe VALUES('BE', 'NL', 'FR'), 
          PARTITION asia VALUES('JP', 'PK', 'CN'), 
          PARTITION others VALUES(DEFAULT)
      )                
    2. Use fast pruning to extract the partitions that satisfy each of the following WHERE clauses from the list-partitioned table.
      WHERE country = 'US' WHERE country IS NULL;

      If the first WHERE clause is used, fast pruning excludes the europe partition, the asia partition, and the others partition. This is because these partitions do not satisfy WHERE country = 'US'.

      If the second WHERE clause is used, fast pruning excludes the americas partition, the europe partition, and the asia partition. This is because these partitions do not satisfy WHERE country IS NULL.

  • For range-partitioned tables, PolarDB for Oracle can fast prune queries that contain WHERE clauses. The operators that can be used in the WHERE clauses are the equal sign (=), greater-than (>) sign, greater-than-or-equal-to (>=) sign, less-than (<) sign, less-than-or-equal-to (<=) sign, IS NULL, and IS NOT NULL. The WHERE clauses can also contain more complex expressions that contain the AND or BETWEEN operator. In the following WHERE clauses, the AND and BETWEEN operators are used to form complex expressions.

    WHERE size > 100 AND size <= 200 WHERE size BETWEEN 100 AND 200
    Note Fast pruning cannot be performed based on expressions that contain the OR or IN operator.

    The following code provides an example:

    1. Create a range-partitioned table.
      CREATE TABLE boxes(id int, size int, color text) 
        PARTITION BY RANGE(size)
      (
          PARTITION small VALUES LESS THAN(100),
          PARTITION medium VALUES LESS THAN(200),
          PARTITION large VALUES LESS THAN(300)
      )
    2. Use fast pruning to extract the partitions that satisfy each of the following WHERE clauses from the range-partitioned table.
      WHERE size > 100-- Scans the medium partition and the large partition
      WHERE size >= 100-- Scans the medium partition and the large partition
      WHERE size =100-- Scans the medium partition
      WHERE size <= 100-- Scans the small partition and the medium partition
      WHERE size < 100-- Scans the small partition
      WHERE size > 100 AND size < 199-- Scans the medium partition
      WHERE size BETWEEN 100 AND 199-- Scans the medium partition
      WHERE color = 'red' AND size = 100  -- Scans the medium partition
      WHERE color = 'red' AND (size > 100 AND size < 199) -- Scans the medium partition