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.
SET AGGREGATE_AWARENESS {FRESH_ONLY|OLD_OK|OFF}
where:
Sets different values for the parameters associated with each RDBMS.
Sets different values for the parameters associated with each RDBMS.
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.
Adapter-specific parameters for which values are set by the FRESHONLY and OLD_OK settings:
These values can be set in the Server profile or as a part of an RPC using Direct Passthru.
RDBMS manuals should be reviewed for recommendations, but the most common checklist items are:
iWay Software |