Aggregate Awareness in an RDBMS

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.



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.



Reference: Usage Notes for Aggregate Awareness

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

  • For Db2, CURRENT REFRESH AGE and CURRENT QUERY OPTIMIZATION.
  • For Oracle, QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED.

    These values can be set in the Server profile or as a part of an RPC using Direct Passthru.

  • Teradata handles aggregate awareness internally.

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

  • Updating statistics on the pre-aggregated object and the master tables: (RUNSTATS in Db2, COLLECT STATISTICS in Teradata, ANALYZE TABLE in Oracle).
  • Confirming compatibility of the generated SQL query and the query used for creating the pre-aggregated object. The most common items are compatibility of:
    • Aggregators (columns functions) and aggregation objects (column function arguments)
    • GROUP BY clauses
    • Joins

Information Builders