Oracle Training Oracle News Oracle Forum Class Catalog Our Staff Our Prices Help Wanted! Remote DBA Oracle Tuning Emergency 911 RAC Support Apps Support Analysis Design Implementation Oracle Support
SQL Tuning Security UNIX Oracle UNIX Linux Oracle Linux Monitoring Remote help Remote plans Remote services Oracle C++ Oracle Java Apache JDeveloper App Server Applications Oracle Forms Oracle Portal 11i Upgrades SQL Server Oracle Concepts HTML-DB Tips Software Help Remote Help Development Implementation
Financials Training Oracle 11i Oracle Apps 11i Oracle Workflow Oracle AR 11i Class Oracle AP 11i class Oracle GL 11i class Oracle HR 11i class Oracle FA 11i class 11i Project Mgt 11i procurement 11i collections
Oracle Posters Oracle Books Oracle Tuning Book Oracle RAC Book Oracle Security Easy Oracle Books Oracle Scripts SQL Server DBA SQL Design Patterns Ion Excel-DB
BC Oracle News Rednecks! Dress code Arabian Stallion Burleson Arabians Guide Horses Don Burleson Blog Golf & Travel Privacy Policy
Rednecks! Dress code Arabian Stallion Burleson Arabians Guide Horses Don Burleson Blog Golf & Travel Privacy Policy
When using materialized views it is critical to properly enable query rewrite so that SQL is automatically transformed to use the materialized views instead of the base tables.
See my notes on the basics of materialized views here.
These parameters are required to enable materialized view query rewrite:
optimizer_mode = choose, first_rows, or all_rows job_queue_interval = 3600 job_queue_processes = 1 query_rewrite_enabled = true query_rewrite_integrity = enforced compatible = 8.1.5.0.0 (or greater)
However, getting query rewrite working can be tricks and you often need to create dimensions. The article the value of meta-data for enabling Query Rewrite, describes the technique of creating dimensions and including the dimension in the materialized view definition to allow query rewrite to function for a variety of related queries:
This article gives a very simple example of such a query rewrite operation and then continues to illustrate how providing some additional meta-data to the database - next to constraints and proper data-type definitions - in the form of Dimensions can make all the different to the Query Rewrite capabilities of the CBO.
Without materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over:
Here are my notes on materialized views query rewrite:
Materialized View Query Rewrite
Dynamically create complex objects with Oracle materialized views
Oracle tuning using materialized views
Oracle hidden optimizer parameters
Oracle DBMS_ADVISOR TUNE_MVIEW
Combine Relational And Multidimensional Data In A Single OLAP Cube
Oracle10g OLAP Query Equivalence - Oracle 10g
Oracle 10g materialized view advisor dbms_mview - Oracle10g
Data structure denormalization with Oracle
Oracle Using Function Based Indexes
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.
Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2009 by Burleson Enterprises, Inc. All rights reserved. Oracle © is the registered trademark of Oracle Corporation.
Burleson Consulting The Oracle of Database Support
Oracle Performance Tuning
Remote DBA Services
Copyright © 1996 - 2009 by Burleson Enterprises, Inc. All rights reserved.
Oracle
