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 available using SQL on DB2 Universal Database™ for iSeries™. For general information about query optimization, see the Database performance and 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^® UDB Symmetric Multiprocessing feature is installed, the parallel data access methods are available for the optimizer to consider 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.

• Queries using SQL Query Engine
  The SQL Query Engine (SQE) provides targeted optimization for partitioned tables using dynamic partition expansion optimization.

• Queries using Classic Query Engine
  When queries are implemented using Classic Query Engine (CQE), be aware of how the query is optimized including materialization and index usage.

 

Parent topic:

Partitioned tables

 

Related concepts

Indexes with partitioned tables
SQE and CQE Engines

 

Related information

Performance and query optimization