Monday, July 23, 2012

Simple way to simplify summary management in large databases

Oracle's materialized views are a way to simplify summary management in large databases. The beauty of Oracle's materialized view facility is that once the views are created, they are automatically updated by the database whenever there are changes in the underlying base tables on which the view is defined. The materialized views are completely transparent to the users. If users write queries using the underlying table, Oracle will automatically rewrite those queries to use the materialized views. The Oracle Optimizer will automatically decide to use the materialized view rather than the underlying tables and views if it would be more efficient to do so. Complex joins involve a lot of overhead and the use of the materialized views will avoid incurring this cost each time you need to perform such joins. Because the materialized views already have the summary information precomputed in them, your queries will run much faster.

You can also partition materialized views and create indexes on them if necessary. A major problem with the aggregate or summary tables is their maintenance, which involves keeping the tables in accord with the base tables that are being constantly modified. If you aren't sure about which materialized views to create, you can take advantage of Oracle's Summary Advisor, which can make specific recommendations based on its use of the DBMS_OLAP package.

No comments:

Post a Comment