Oracle DBMS - Quickly Speed Up Huge, Joined Table Queries By Letting Oracle Swap In A Materialized View
Large data warehouse applications often require queries against huge tables joined together. One way to speed up these operations is to denormalize the data using a materialized view. Like a table, a materialized view actually stores data (not just the SQL code to produce the data, which is what a normal view contains). But unlike a table, Oracle automatically manages the rebuilding of a materialized view (based on parameters you set) so the data is up to date.
Of course, the SQL may come from an application that didn't consider the use of materialized views. Since Oracle 8i, you can remedy that situation with the query rewrite feature, which lets the optimizer decide to run your improved version of the query (i.e., with the materialized view) instead of the original SQL.
The following steps show an example of creating a materialized view for query rewrite.
- Ensure that the database parameter QUERY_REWRITE_ENABLED is set to True. Restart the database if necessary.
- Make sure the user creating the materialized view has the Query Rewrite system privilege.
- Create materialized view logs on the tables that constitute the materialized view:
- Create Materialized View Log
on Sales with Rowid;
Create Materialized View Log
on Region with Rowid;
- Create Materialized View Emp_Sales_Region
Storage (Initial 1m next 5m pctincrease 0)
Refresh Fast on Demand
Enable query rewrite
Select Region_Name, Sales_Amt, S.Rowid, R.Rowid
>From Sales S, Region R
Where S.Region_id = R.Region_id
And S.Trans_Date > Sysdate -1;