Administration Guide

Creating a Summary Table

A summary table is a table whose definition is based on the result of a query. As such, the summary table typically contains pre-computed results based on the data existing in the table or tables that its definition is based on. If the SQL compiler determines that a query will run more efficiently against a summary table than the base table, the query executes against the summary table, and you obtain the result faster than you otherwise would.

The creation of a summary table with the replication option can be used to replicate tables across all nodes in a partitioned database environment. These are known as "replicated summary tables". See Replicated Summary Tables for more information.
Note: Summary tables are not used with static SQL or nicknames.

In general a summary table, or a replicated summary table, is used for optimization of a query if the isolation level of the summary table, or the replicated summary table, is higher than or equal to the isolation level of the query. For example, if a query is running under the cursor stability (CS) isolation level, only summary tables, and replicated summary tables, that are defined under CS or higher isolation levels are used for optimization.

To create a summary table, you use the CREATE SUMMARY TABLE statement with the AS fullselect clause and the IMMEDIATE or REFRESH DEFERRED options.

You have the option of uniquely identifying the names of the columns of the summary table. The list of column names must contain as many names as there are columns in the result table of the full select. A list of column names must be given if the result table of the full select has duplicate column names or has an unnamed column. An unnamed column is derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result set of the full select.

When you create the summary table, you have the option of specifying whether the summary table is refreshed automatically when the base table is changed, or whether it is refreshed by using the REFRESH TABLE statement. To have the summary table refreshed automatically when changes are made to the base table or tables, specify the REFRESH IMMEDIATE keyword. An immediate refresh is useful when:

You have queries that take a long time to complete when run against a base table
The base table or tables are infrequently changed
The refresh is not expensive.

The summary table, in this situation, can provide pre-computed results. If you want the refresh of the summary table to be deferred, specify the REFRESH DEFERRED keyword. Summary tables specified with REFRESH DEFERRED will not reflect changes to the underlying base tables. You should use summary tables where this is not a requirement. For example, if you run DSS queries, you would use the summary table to contain legacy data.

A summary table defined with REFRESH DEFERRED may be used in place of a query when it:

Conforms to the restrictions for a fullselect of a refresh immediate summary table, except:
- The SELECT list is not required to include COUNT(*) or COUNT_BIG(*)
- The SELECT list can include MAX and MIN column functions
- A HAVING clause is allowed.

The SQL special register CURRENT REFRESH AGE SQL is set to ANY or has a value of 99999999999999. The collection of nines is the maximum value allowed in this special register which is a timestamp duration value with a data type of DECIMAL(20,6).
Note: Summary tables defined with REFRESH DEFERRED are not used to optimize static SQL.

You use the CURRENT REFRESH AGE special register to specify the amount of time that the summary table with deferred refresh can be used for a dynamic query before it must be refreshed. To set the value of the CURRENT REFRESH AGE special register, you can use the SET CURRENT REFRESH AGE statement. For more information about the CURRENT REFRESH AGE special register and the SET CURRENT REFRESH AGE statement, refer to the SQL Reference.

Summary tables defined with REFRESH IMMEDIATE are applicable to both static and dynamic queries and do not need to use the CURRENT REFRESH AGE special register.
Note: Setting the CURRENT REFRESH AGE special register to a value other than zero should be done with caution. By allowing a summary table that may not represent the values of the underlying base table to be used to optimize the processing of the query, the result of the query may not accurately represent the data in the underlying table. This may be reasonable when you know the underlying data has not changed, or you are willing to accept the degree of error in the results based on your knowledge of the data.

With activity affecting the source data, a summary table over time will no longer contain accurate data. You will need to use the REFRESH TABLE statement. Refer to the SQL Reference for more information.

If you want to create a new base table that is based on any valid fullselect, specify the DEFINITION ONLY keyword when you create the table. When the create table operation completes, the new table is not treated as a summary table, but rather as a base table. For example, you can create the exception tables used in LOAD and SET INTEGRITY as follows:

   CREATE TABLE XT AS
      (SELECT T.*, CURRENT TIMESTAMP AS TIMESTAMP,CLOB(",32K)
      AS MSG FROM T) DEFINITION ONLY

Here are some of the key restrictions regarding summary tables:

You cannot alter a summary table.
You cannot alter the length of a column for a base table if that table has a summary table.
You cannot import data into a summary table.
You cannot create a unique index on a summary table.
You cannot create a summary table based on the result of a query that references one or more nicknames.

Refer to the SQL Reference for a complete statement of summary table restrictions.

[ Top of Page | Previous Page | Next Page ]