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.

  1. Ensure that the database parameter QUERY_REWRITE_ENABLED is set to True. Restart the database if necessary.
  2. Make sure the user creating the materialized view has the Query Rewrite system privilege.
  3. 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;

  4. Create the materialized view. In the code below, we create the view in the Region tablespace, and specify reasonable storage parameters. Note that we-ve included the Rowid values for the two tables that form the view. We need this to invoke the Fast (i.e., incremental) refresh method, which makes Oracle only add changed rows when it updates the materialized view.

    • Create Materialized View Emp_Sales_Region
      Tablespace Region
      Storage (Initial 1m next 5m pctincrease 0)
      Build immediate
      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;

  5. Finally, analyze the tables that form the materialized view.

Go back