MQTs can contain pre computed and/or subsets of data.
MQTs can have indexes; the 
RUNSTATS
 command can be performed against 
them.
MQTs can use multi dimensionally clustered and regular tables as source 
tables.
MQTs can be refreshed incremental.
MQTs are automatically used by the optimizer if applicable.
The following MQT enhancements can result in improved query performance:
Query routing enhancements
Queries can now be routed to MQTs whose definitions contain a join that is 
not aggregated. Prior to Version 8, an MQT definition could only reference a 
join that was aggregated. For example, in Version 8 the table described in 
Example 10 9, which contains a join, can be created to store the customer 
and account information for bad accounts.
Example 10 9   Sample MQT
CREATE TABLE bad_account AS (
SELECT customer_name, customer_id, a.balance
FROM account a, customers c
WHERE
status IN ( delinquent ,  problematic ,  hot )
AND a.customer_id = c.customer_id)
DATA INITIALLY DEFERRED REFRESH DEFERRED
If a user asks whether an account is delinquent, the DB2 UDB optimizer 
recognizes that the MQT has cached the requested information, and instead 
of accessing the base table 
ACCOUNT
, DB2 accesses the MQT named 
BAD_ACCOUNT
, which provides a better response time and can be used to return 
customer information.
User maintained materialized query tables
Many custom applications maintain and load tables that are really 
precomputed data representing the result of a query. By identifying a table as 
a user maintained materialized query table, dynamic query performance can 
be improved. Such MQTs are maintained by users rather than by the system. 
UPDATE
, 
INSERT
, and 
DELETE
 operations are permitted against user maintained 
MQTs.
Setting appropriate special registers allows the query optimizer to take 
advantage of the precomputed query result that is already contained in the 
user maintained MQT.
Materialized query tables on nicknames
 Chapter 10. Advanced DB2 UDB features 
329






footer




 

 

 

 

 Home | About Us | Network | Services | Support | FAQ | Control Panel | Order Online | Sitemap | Contact

san diego web hosting

 

Our partners: PHP: Hypertext Preprocessor Cheap Web Hosting JSP Web Hosting Ontario Web Hosting  Jsp Web Hosting

Cheapest Web Hosting Java Hosting Cheapest Hosting

Visionwebhosting.net Business web hosting division of Vision Web Hosting Inc.. All rights reserved