IBM Books

Administration Guide


Accounting for Column Correlation

You may have applications which contain queries constructed with joins that have more than one join predicate joining two tables. While this may sound complicated, such a situation is not unusual where you are attempting to determine relationships between similar, related columns between tables.

For example, a manufacturer makes products from raw material of various colors, elasticities and qualities. The finished product has the same color and elasticity as the raw material from which it is made. The manufacturer issues the query:

     SELECT PRODUCT.NAME, RAWMATERIAL.QUALITY FROM PRODUCT, RAWMATERIAL
        WHERE PRODUCT.COLOR       =  RAWMATERIAL.COLOR
          AND PRODUCT.ELASTICITY  =  RAWMATERIAL.ELASTICITY

This query returns the names and raw material quality of all products. There are two join predicates:

   PRODUCT.COLOR       =  RAWMATERIAL.COLOR
   PRODUCT.ELASTICITY  =  RAWMATERIAL.ELASTICITY

When the DB2 UDB optimizer chooses a plan for executing this query, it calculates how selective each of the two predicates are, and assumes that they are independent, that is, that all variations of elasticity occur for each color, and that conversely for each level of elasticity there is raw material of every color. It then uses statistics on how many levels of elasticity and how many different colors there are in each table to calculate the overall selectivity of the pair of predicates. Based on this it may choose, for example, a Nested Loop Join in preference to a Merge Join, or vice versa.

However, it may be that these two predicates are not independent. For example, it may be that the highly elastic materials are available in only a few colors, and the very inelastic materials are only available in a few other colors (different from the elastic ones). Then the combined selectivity of the predicates is less (eliminates fewer rows) so the query will return more rows. To see this, imagine the extreme case where there is just one level of elasticity for each color and vice versa. Now either one of the predicates logically could be omitted entirely since it is implied by the other. The optimizer's choice of plan may no longer be the best, for example it may be that the Nested Loop join plan is selected but the Merge Join would be faster.

With other database products, database administrators have tried to solve this performance problem by updating statistics in the catalog to try to make one of the predicates appear to be less selective, but this approach can cause unwanted side-effects on other queries.

DB2 UDB's optimizer attempts to detect and compensate for correlation of join predicates if you:

  1. Set the DB2 registry variable DB2_CORRELATED_PREDICATES=Y (or any value indicating true). This registry variable will take effect after issuing a db2start.

  2. Define unique indexes on the correlated columns, that is, on the columns of a table which appear in the correlated predicates.

In the above example, you could define a unique index covering either:

   PRODUCT.COLOR, PRODUCT.ELASTICITY

or

   RAWMATERIAL.COLOR, RAWMATERIAL.ELASTICITY

or both.

In order for correlation to be detected, the non-include columns of this index must be correlated columns, and no other columns. The index may optionally contain include columns.

In general there may be more than 2 correlated columns in join predicates so you should ensure that you define the unique index to cover all of them.

In many cases the correlated columns in one table form its primary key. A primary key is always unique so if there's a primary key on the correlated columns, there's no need to define another unique index.

After doing this, ensure that statistics on tables are up to date and that they have not been altered away from the true values for any reason, for example to attempt to influence the optimizer.

When DB2_CORRELATED_PREDICATES is true, the optimizer will use the KEYCARD information of unique index statistics to detect cases of correlation, and dynamically adjust combined selectivities of the correlated predicates, thus obtaining a more accurate estimate of the join size and cost.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]