Oracle Materialized views to optimize a Datawarehouse

Datawarehouse loadings are periodic, and summary tables are useful to improve efficiency and the answer time of our reports. A physical optimisation resource that can bring big improvements in our system is the utilization of materialized views.

The view materialized is not more that a view, defined with one sentences SQL, of which in addition to storing his definition, store the data that returns, realizing an initial load and afterwards each true time a freshen of the same.

Like this, if we have a Datawarehouse that update daily, could use views materialized to go updating intermediate tables that feed our diagrams of DWH, or directly to implement tables agregadas that will freshen from our tables base. 

The creation of this type of views no so complex as it can seem, the most important is to have clear each how much time want to update the information of the views, and which method of freshen to use.

Also will have to ensure us that our licence of base of data allows us use them (has to be a version Enterprise).

 

Sintaxis Basic for the creation of a view materialized

CREATE MATERIALIZED VIEW My_view_materialized
             [TABLESPACE My_tablespace]
             [BUILDunknown{^*IMMEDIATE | DEFERRED}]
             [REFRESHunknown{^*ON COMMIT | ON DEMAND | [START WITH Dates_start] NEXT dates_interval } |.Verbcj | FAST | FORCE} ]
             [unknown{^*ENABLE|DISABLE} QUERY REWRITE]
AS SELECT t1.Field1, t2.Field2
      FROM My_table1 t1  , my_table2 t2
      WHERE t1.Field_fk = t2.Field_pk
         AND … 

Comments on the different options: 

  • Load of data in the view

BUILD IMMEDIATE:
  The data of the view load in the same moment of the creation

BUILD DEFERRED:
  Only create the definition, the data will load more forward. To realize this load can use the function REFRESH of the package DBMS_MVIEW:
      begin
         dbms_mview.refresh('My_view_
materialized');
      end;
 

  • Method and temporalidad of the freshen of the data

    Each how much time will freshen :

    REFRESH ON COMMIT:
      Each time that do a commit in the objects origin defined in the select 

    REFRESH ON DEMAND:
      As with the option DEFERRED of the BUILD, will use the procedures REFRESH, REFRESH_ALL_MVIEWS or REFRESH_DEPENDENT of the package DBMS_MVIEW 

REFRESH [START WITH Dates_start] NEXT dates_interval:
  START WITH Indicate the date of the first freshen (dates_start suele be a SYSDATE)
  NEXT indicates each how much time will update (dates_interval could be SYSDATE +1 to realize the freshen once to the day)

 

  • Of which way will freshen

REFRESH COMPLETE:
The freshen will do of all the data of the view materialized, the recreará entirely each time that launch the freshen

REFRESH FAST:
The freshen will be incremental, is the option more recomendable, the of fast already gives an idea of the porqué.
This type of freshen has quite a lot of restrictions as the type of view that was creating .
They can consult generally Restrictions on Fast Refresh of the official documentation of Oracle

One of the important things to have in account is that to can use this method almost always is necessary to have created before a LOG of the View materialized, indicating the key fields in which will base the maintenance of the view.
It uses the instruction CREATE MATERIALIZED VIEW LOG ON:

   CREATE MATERIALIZED VIEW LOG ON My_table_origin
      WITH PRIMARY KEY
      INCLUDING NEW VALUES;
 

REFRESH FORCE:
With this method indicate that if it is possible use the metodo FAST, and if no the COMPLETE. 

To know if a view materialized can use the method FAST, the package DBMS_MVIEW provide the procedure EXPLAIN_MVIEW 

  • Activación Of the reescritura of queries

ENABLE QUERY REWRITE:
Allow to the base of data the reescritura of queries

DISABLE QUERY REWRITE:
desactiva The reescritura of queries

The option QUERY REWRITE is the one who more go to use if we love the views materialized to optimize ours Date warehouse.
This option allows to create tables agregadas in form of views materialized, and that when it launch a SELECT the base of data can reescribirla to consult the table or view that go to give back the data requested in less time, everything of way totally transparent to the user

The only that there is that do is to create the tables agregadas like views materialized with QUERY REWRITE enabled.

 

Examples of views materialized

They are a lot of combinations, but the final sentence is not so complex.

 

First step of the ETL

If we wanted to create a view materialized of a table that freshen a day to the week, and of way incremental would do the following: 

CREATE MATERIALIZED VIEW LOG ON My_table_origin
   WITH  PRIMARY KEY
   INCLUDING NEW VALUES; 
CREATE MATERIALIZED VIEW My_view_materialized
  REFRESH FAST  NEXT SYSDATE + 7
  AS SELECT field1, field2, field8
        FROM My_table_origin
    WHERE Field2 > 5000;

This view could serve us to feed the load of a Dates Mart that it realized weekly. We could programarla so that it freshened fair before the start of the process of load, or as first step in the ETL, and already would have the necessary data updated, and independent of the origin of data (would not have to bother more to the operacional). Another advantage to have in account is that if there is some problem with the access to the data origin, if we have not deleted them, in the view materialized still will have the data of the last freshen, with what although the freshen failed would not find us an error that truncated the load of ours Date Warehouse, or an empty table.
Of course, in the conditions of the WHERE could select only the necessary registers, only the ones of the last month, etc.

 

Tables agregadas

Another important example would be the utilization of views materialized for the creation of tables agregadas:

CREATE MATERIALIZED VIEW Sales_agregadas_mv
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE
AS
  SELECT Go_product, sum(amount) total_sales
  FROM Sales;

With this simple sentence would create a table agregada of total of sales by product of a supposed table of sales than serious the table of facts.

To level of session also there would be that ensure that the option QUERY_REWRITE was actuated. Just in case enable with

ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

If now inside this session executes the sentence

SELECT sum(Amount)
FROM Sales;

The base of data will prepare the plan of execution having in account the view materialized created and internally will realize the selection on the seen sales_agregadas_mv.

A simple way to check it, apart to examine the plan of execution, or to compare time before and after the creation of the view, or desactivando the QUERY_REWRITE, is to check that this sentence gives back resulted in the same time that the sentence

SELECT sum(Amount)
FROM Sales_agregadas_mv;

 

To consult more details, or the sintaxis complete of the creation of views materialized in the chapter Create Materialized View of the manual of reference SQL of Oracle

 With enterprise manager or with the console web of the base of data also can create the views materialized of a more assisted way, but equally is important to have clear the concepts before doing it.

Vistas materializadas en Oracle Enterprise Manager