Query performance and optimization

Queries that reference partitioned tables need to be carefully considered because partitioned tables are often very large. It is important to understand the effects of accessing multiple partitions on your system and applications.

Partitioned tables can take advantage of all optimization and parallel processing that is available with SQL on Db2® for i. The Database performance and optimization topic contains general information about query optimization. For partitioned tables, all the data access methods described in the Database performance and optimization topic can be used to access the data in each partition. In addition, if the DB2® Symmetric Multiprocessing feature is installed, the parallel data access methods are available for the optimizer when implementing the query.

If queries need to access only an individual partition in a partitioned table, creating an alias for that individual partition and then using that alias in the query can enhance performance. The query acts as a nonpartitioned table query.

Partitioned tables conceptually implemented as a nested table where each partition is unioned to the other partitions.

For example, if you perform the following query:

SELECT LASTNAME, SALARY FROM PRODLIB.PAYROLL
	WHERE SALARY > 20000

The implementation of the query can be described as:

SELECT LASTNAME, SALARY FROM 
		(SELECT LASTNAME, SALARY FROM  PRODLB.PAYROLL(PART00001)
		 UNION ALL
  		SELECT LASTNAME, SALARY FROM  PRODLB.PAYROLL(PART00002)
		UNION ALL
		SELECT LASTNAME, SALARY FROM  PRODLB.PAYROLL(PART00003)) 	
	X (LASTNAME, SALARY)
	WHERE X.SALARY > 20000

The implementation of partitioned table queries depends on which query engine is used: the Classic Query Engine (CQE) or the SQL Query Engine (SQE). You can find more information about the query engines in the SQE and CQE Engines topic in the Database performance and optimization topic collection. There are different considerations for each engine.