In partitioned databases, collocation of tables is recognized by the optimizer and used when determining the best access plan for a query. The assumption is that tables that are frequently involved in join queries should, when divided among partitions in a partitioned database, ideally have the rows from each table being joined located on the same database partition. During the join operation, the collocation of the data from both tables that are part of the join would prevent the need to move data from one partition to another. Placing both tables in the same nodegroup ensures that the data from the tables is collocated together.
See "Table Collocation" for more information on collocating tables.
Also, within a partitioned database, the spreading of the data over more partitions reduces the estimated time (or cost) to execute a query. The number of tables, the location of the data in those tables, and the type of query (whether a join is required as noted above) all affect the cost of the query.
[ DB2 List of Books | Search the DB2 Books ]