Aggregate Awareness in an RDBMS

How to:

Reference:

When aggregation is delegated to the RDBMS, the RDBMS can perform additional optimization of the query by using a mechanism commonly called Aggregate Awareness. Aggregate Awareness is implemented in DB2, Teradata, Oracle, and some other RDBMSs.

The implementation is similar in most RDBMSs. Based on most common types of reports performed on a database, the database administrator creates one or more objects (named SUMMARY TABLES in DB2, JOIN INDEXES in Teradata, and MATERIALIZED VIEWS or SNAPSHOTS in Oracle) that are populated with pre-aggregated data. The size of the pre-aggregated object (summary table, join index, or snapshot) is usually much smaller than the combined size of the involved tables. The RDBMS optimizer evaluates the incoming queries and, if possible, reconstructs the incoming query so that the pre-aggregated data is used for forming the answer set. This significantly reduces both CPU time and I/O operations.

The aggregated SQL generated by the relational adapter is fully suitable for an RDBMS optimizer to use pre-aggregated data. However, DB2, Teradata, and Oracle optimizers have specific rules for when to use pre-aggregated data.


Top of page

x
Syntax: How to Set Aggregate Awareness
SET AGGREGATE_AWARENESS {FRESH_ONLY|OLD_OK|OFF}

where:

FRESH_ONLY

Sets different values for the parameters associated with each RDBMS.

OLD_OK

Sets different values for the parameters associated with each RDBMS.

OFF

If no option is selected, the behavior of the target RDBMS is determined by the database configuration options. There is no default for this setting.


Top of page

x
Reference: Usage Notes for Aggregate Awareness

Adapter-specific parameters for which values are set by the FRESHONLY and OLD_OK settings:

RDBMS manuals should be reviewed for recommendations, but the most common checklist items are:


iWay Software