Jaycognos

 Profile Answers by Jaycognos Questions by Jaycognos

• Feb 12th, 2008

 

Aggregate tables are pre-computed totals in the form of hierarchical mutidimensional structure.,whereas materliazed view ,is an database object which caches the query result in a concrete table and updates it from the original database table from time to time .Aggregate tables are used to speed up the query computing whereas materialized view speed up the data retrieval .

joshmohit

 Profile Answers by joshmohit 

• Nov 3rd, 2010

 

Aggregate table is a generic term to represent the summaries built in a warehouse. MAterilaized view is the technical implementation of these aggregate tables. Though, Materialized views come with the database specific properties which might not be covered with the generic definition of aggregate tables.

Tom Haughey

 Profile Answers by Tom Haughey Questions by Tom Haughey

• Apr 29th, 2012

 

?An aggregate table is a table that contains data that is summarized somehow, for example, it contains a summary of Customer by Product by Week. A more granular level of detail would be the transaction. An aggregate can be maintained by the DBMS or the user (DBA). An aggregate that is maintained by the DBMS is called (variously by different vendors) an Automatic Summary Table, a Materialized Query table or a Materialized View (and others). So the basic difference is that one is maintained by the user, the other by the DBMS. A DBMS can be instructed to update the aggregate in two main ways: REFRESH IMMEDIATE, which updates the aggregate every time the underlying data changes, or REFRESH DEFERRED, which changes at some interval but is usually done as a total refresh. A materialized view is also an aggregate but it is maintained by the DBMS. There is another significant difference. Assume for this you had written SQL that went after the transactions, but now you want to access an aggregate. In a user-maintained aggregate, the SQL has to be re-written by you to take advantage of the aggregate (assuming your original query went after the details) rather than going after the details directly. In a MV, you do not have to change the original (detailed) SQL. The DBMS itself will rewrite the detailed original query to take advantage of the aggregate. There is one other difference. A MV can be used for other purposes than just aggregates. If you wanted to create a replica of data (aggregated or not) you could use an MV. You could take one whole table and create a DBMS-maintained replica of it just by creating a MV of it at the detailed level. Any time the first one changed, the DBMS would change the second one (assuming you use REFRESH IMMEDIATE). That is why they are no longer called Automatic Summary Tables (AST)-- the result doesn't have to be a summary. E.g., DB2 sometimes uses a MQT within other things they do in the DBMS.