Temporary Table Performance

Temporary Table Performance

Performance improvements have been made to the implementation of temporary sort tables in this release. To process certain queries, the database engine must generate temporary tables for internal use. The performance for many of these queries has been improved.

In general, the database engine generates at least one temporary table to process a given query if any of the following conditions is true:

•

The DISTINCT keyword precedes the selection list and the items in the selection list are not columns that are the leading segments of an index.

For example, a temporary table is generated to process the following query unless an index exists with columns c1 and c2 as leading segments:

SELECT DISTINCT c1, c2 FROM t1

•

A GROUP BY clause exists, and the items in the GROUP BY clause are not columns that are the leading segments of an index.

For example, a temporary table is generated to process the following query unless an index exists with columns c1 and c2 as leading segments:

SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1, c2

•

A static cursor is being used.

For example, a temporary table is generated if an application calls the ODBC API SQLSetStmtOption specifying the SQL_CURSOR_TYPE option and the SQL_CURSOR_STATIC value prior to creation of the result set.

•

The result set includes bookmarks.

For example, if the ODBC API SQLSetStmtOption is called specifying the SQL_USE_BOOKMARKS option and the SQL_UB_ON value prior to generating the result set.

•

A query contains a non-correlated subquery to the right of the IN or =ANY keywords.

SELECT c1 FROM t1 WHERE c2 IN (SELECT c2 FROM t2)