Version 5
|
|
Version 5
|
The performance has been improved for queries that use columns which are key columns of different indexes over the same table. DB2 uses dynamic bitmap technology to efficiently combine multiple indexes.
The performance of queries involving star joins has been
improved. Star queries are characterized as multiway joins between
several small dimension tables and a large fact table. DB2's new
star join algorithm exploits dynamic bitmaps to join a large fact table with a
series of relatively small dimension tables, minimizing data I/O.
 |
Version 5.2
|
Users with the appropriate authority can now specify the size (granularity) of locks used when a table is accessed. By default row-level locks are used when tables are created. Changes to the ALTER TABLE statement allow locking to be pushed up to the table level. Using table-level locks may improve the performance of queries by reducing the number of locks that need to be obtained and released. See the Administration Guide and the SQL Reference for details.
The new Fetch First N Rows Only feature allows users to limit the size of the result set of a query to a specified value. This feature improves the performance of queries that have potentially large results, when only a limited number of rows are of interest. For example, a user might be interested in viewing only information on the 10 highest paid employees in an organization. In this case the user could issue a SELECT statement with a FETCH of only the first 10 rows. See "Fetch-first-clause" and the SQL Reference for details.
Index-only access means that queries can be satisfied by accessing only the index, providing that the SELECT matches the included columns. See "Index-Only Access" for more information.
The DB2 Universal Database optimizer has been enhanced with regards to choosing an execution plan for queries that contain joins with more than one join predicate joining two tables. The dependence or independence of predicates can affect the performance of optimizer's chosen plan. The new DB2_CORRELATED_PREDICATES registry variable helps the optimizer detect and compensate for the correlation of join predicates. When this variable is true, the optimizer will use the KEYCARD information of unique index statistics to detect cases of correlation and dynamically adjust the combined selectivities of the correlated predicates. This results in a more accurate estimate of the join size and cost. See the Administration Guide for details.
Summary tables can be used to improve query performance. You can create a summary table that holds a derived result and keeps that result updated. For example, you could monitor the highest salary in your company (select max(salary) from employee) and keep that information up-to-date in a summary table. Then, whenever a user requests the highest employee salary, the result from the summary table is returned, rather than recalculating it from the employee table.
Summary tables can be created to hold the results of simple queries, or a collection of joins involving multiple tables. See the Administration Guide and the SQL Reference for details.
| Note: | The REFRESH IMMEDIATE option is not available in Version 5.2. It will return SQLCODE SQL0628N if used. You can use the REFRESH TABLE statement to update your summary tables. |
DB2 Extended Enterprise Edition users can specify that the data stored in a table is physically replicated on each database partition of the nodegroup for the table space where the table is defined. Replicated tables are particularly useful for joins in which you have a large fact table and small dimension tables. A list of all customers or a list of all transactions (orders and sales) could be large fact tables, while a list of countries might be a small dimension table. If the customer table and the transaction table are stored on separate database partitions, and both are involved in joins with the country table, then the country table is a good candidate for replication in your multi-node environment. To create a replicated table, you use the CREATE TABLE statement with the REPLICATED parameter. See the SQL Reference and the Administration Guide for details.
A hash join will first compare hash codes before comparing predicates for tables involved in a join. In a hash join, one table (selected by the optimizer) is scanned and rows are copied into memory buffers drawn from the sort heap allocation. The memory buffers are divided into partitions based on a hash code computed from the columns of the join predicates. Rows of the other table involved in the join are matched to rows from the first table by comparing the hash code. If the hash codes match, the actual join predicate columns are compared.
Hash join requires one or more predicates in the form table1.columnX=table2.columnY, and for which the column types are the SAME. For columns of type CHAR, the length must be the same. For columns of type DECIMAL, the precision and scale must be the same. The column type cannot be a LONG field column, or a large object (LOB) column.
For more details on hash joins see the Administration Guide. For information on monitoring hash joins see "Hash Join".