Re: Materialized Views

I think there are a couple of issues here. One is whether the MQT(Materialized Query Table)/Matview/Indexed view is refreshed occasionally or immediately. Another question is whether the MQT is routed to transparently to the user or whether the MQT is picked by the user explicitly. Triggers are viable where the user explictly chooses the MQT and the MQT is refreshed immediately (i.e. it's up to date at all times). Explicit reference of the MQT and no immediate refresh is essentially a normal sidetable usage. Things are getting interesting when the user is not aware of the existence of the MQT and, further more, the MQT is not an obvious syntactic subset of the query. At this point the DBA can trade disk space and update cost for query speed without the applications knowledge. In DB2 the technology used to maintain MQTs is identical to the one used to execute triggers, but sinec teh query compiler knows much better what to do for MQT maintenance than it does of an arbitrary coded trigger dedicated maintenace is a lot faster than explicit maintenance through a trigger. I have of course no idea whether the same would hold true for IDS. -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab

Just so everyone is aware... MQT (Materialized query table) is the 8.x evolution of the 7.x AST (Automatic Summary Table). The MQT is roughly equivalent to the Oracle Materialized View. The key thing about the MQT is the routing aspect. That means that the query is matched against any MQTs based on the base tables and the query will be dynamically routed to the MQT if it appears that it will reduce the query cost. By using a trigger based approch to maintain a seperate table, there is no virtualization of the query which means that you must make code modifications to take advantage of it. The MQT can be static, manually refreshed, or continuously refreshed as updates are made against the base table. "Serge Rielau" <srielau@ca.eye-be-em.com> wrote in message news:c8leje$ih8$1@hanover.torolab.ibm.com... > I think there are a couple of issues here. > One is whether the MQT(Materialized Query Table)/Matview/Indexed view is > refreshed occasionally or immediately. > Another question is whether the MQT is routed to transparently to the > user or whether the MQT is picked by the user explicitly. > > Triggers are viable where the user explictly chooses the MQT and the MQT > is refreshed immediately (i.e. it's up to date at all times). > Explicit reference of the MQT and no immediate refresh is essentially a > normal sidetable usage. > Things are getting interesting when the user is not aware of the > existence of the MQT and, further more, the MQT is not an obvious > syntactic subset of the query. > At this point the DBA can trade disk space and update cost for query > speed without the applications knowledge. > > In DB2 the technology used to maintain MQTs is identical to the one used > to execute triggers, but sinec teh query compiler knows much better what > to do for MQT maintenance than it does of an arbitrary coded trigger > dedicated maintenace is a lot faster than explicit maintenance through a > trigger. > I have of course no idea whether the same would hold true for IDS. > > -- > Serge Rielau > DB2 SQL Compiler Development > IBM Toronto Lab