Check constraint optimization

 

The optimizer uses check constraints (either user-added or the implicit check constraints added for the partition key) to reduce the partitions examined.

In the following example, assume that PAYROLL is partitioned by range:

SELECT LASTNAME, SALARY 	FROM PRODLIB.PAYROLL 
	WHERE EMPNUM = :hv1

The optimizer adds new predicates to each partition in the table:

SELECT LASTNAME, SALARY FROM 
	(SELECT LASTNAME, SALARY FROM  PRODLB.PAYROLL(PART00001)
	 WHERE EMPNUM=:hv1 AND :hv1 <= 500
	 UNION ALL   	SELECT LASTNAME, SALARY FROM  PRODLB.PAYROLL(PART00002)
	 WHERE EMPNUM=:hv1 AND :hv1 >= 501 AND :hv1 <=1000 
	UNION ALL 	SELECT LASTNAME, SALARY FROM  PRODLB.PAYROLL(PART00003)
	 WHERE EMPNUM=:hv1 AND (:hv1 IS NULL OR :hv1 >= 1001))
	X (LASTNAME, SALARY)

If the value of hv1 is 603, then only the rows of partition PART00002 are examined.

 

Parent topic:

Queries using SQL Query Engine

 

Related concepts

Creation of partitioned tables