Quick Beginnings

Cost-Based Query Optimization

DB2 Enterprise - Extended Edition (DB2 EEE) uses a cost-based query optimizer, which compares different methods for doing a unit of work, and selects the most efficient one. The optimizer provides the following features:

Transparent parallelism

Both new and existing applications that use data-manipulating SQL statements do not have to be changed when they are migrated to DB2 EEE. You only have to rebind them so the optimizer can generate the best plans for existing SQL queries.

Comprehensive use of data partitioning information

The optimizer uses information about how base tables, and the intermediate tables that result from queries, are partitioned across database partitions. This information is used to determine the best execution strategy.

Full-fledged cost-based SQL optimization

The optimizer has information about how the data is partitioned. With this information, the optimizer considers different execution plans and chooses the one with the lowest cost. While comparing different strategies, it accounts for the inherent parallelism of different operations, and the costs introduced by messaging between database partition servers.

When generating plans, the optimizer considers different parallel joining methods, including collocated, directed, and broadcast joins. For more information on joins, refer to the Administration Guide.

Inter-partition and Intra-partition parallelism of all relational operations.

All operations, such as index and table scans, aggregation, set operations, joins, inserts, deletes, and updates can employ both Inter-partition parallelism and Intra-partition parallelism.

Inter-partition parallelism means that the operator is executed in parallel by each database partition server. For example, assume that you issue a SELECT statement to fetch data that meets some condition. The coordinator node sends this request to the other database partition servers to select this data set from that data that is stored on each database partition. Each database partition server then sends this data back to the coordinator node which does the final processing and returns a resulting set.

Intra-partition parallelism means that different operators in the same query can be executed in parallel by the same database partition server. For example, if a SQL query included a scan, join, and sort, the database partition server would process these operators, to the best of its abilities, in parallel.