Quick Beginnings for DB2 Extended Enterprise Edition for UNIX

Cost-Based Query Optimization

DB2 Extended Enterprise Edition 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 Extended Enterprise Edition. 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 optimization

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 messages.

Inter-partition parallelism of all relational operations.

All operations, including index and table scans, aggregation, set operations, joins, inserts, deletes, and updates can employ inter-partition parallelism. (Inter-partition parallelism means that the operator is executed in parallel by each database partition server; intra-partition parallelism means that different operators in the same query can be executed in parallel by the same database partition server.) When generating plans, the optimizer considers different parallel joining methods, including collocated, directed, and broadcast joins.

As a simple example, assume that you want to fetch specific columns from rows that meet some condition. The coordinator node sends a request to the other database partition servers to select the columns from rows that meet that condition. The other database partition servers then send this data to the coordinator node, which does the final processing.

If a row is being added to a table, the database partition server checks the partitioning map, which specifies the database partition server where the row is stored. The row is only sent to that database partition server, with the result that only the interested database partition servers take part in the insert. This keeps communications overhead as low as possible.

[ DB2 List of Books | Search the DB2 Books ]