Create Materialized Views

Materialized views gives you an opportunity to store precomputed data for faster access and better performance.
There are two types of materialized views: aggregate materialized views and only join materialized views.

Materialized views are managed with DBMS_MVIEW package, you can also use that package to manually refresh materialized views.
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_mview.htm

With creation of materialized views, depending on the type two or three objects are defined.
Materialized view object and container table for the data are always created for both aggregate and only joins mviews. If you create aggregate materialized view index is also created.

Before materialized views if you wanted to improve performance of your summaries you had to use CTAS and run your query against that table.

SQL> create table demo_tmv1 as
  2  select sum(s.amount_sold) t1, count(s.amount_sold) t2, c.cust_id
  3  from sales s, customers c
  4  where s.cust_id = c.cust_id
  5  group by c.cust_id;

Table created.

But, CTAS are very hard to maintenance, the DBA will have to manually refresh the table plus the developers will have to change the application code in order to use that table. This is where materialized view are great option and you can benefit from query rewrite mechanism of oracle, where optimizer rewrites user query to use the materialized view if it decides that will benefits from it. Before creating materialized view you can estimate the size and bytes for the mview using ESTIMATE_MVIEW_SIZE procedure. In this example I’m using SH scheme.

SQL> variable v_num_rows number;
SQL> variable v_num_bytes number;
SQL> 
SQL> begin
  2  dbms_mview.estimate_mview_size('stmt1',
  3  'select sum(s.amount_sold),count(s.amount_sold),c.cust_id 
  4  from sales s, customers c
  5  where s.cust_id = c.cust_id
  6  group by c.cust_id',:v_num_rows,:v_num_bytes);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :v_num_rows;

V_NUM_ROWS
----------
      7059

SQL> print :v_num_bytes;

V_NUM_BYTES
-----------
     465894

SQL>

When you create materialized view you can specify storage options, when the mview should be populated (immediate, or deferred), how to refresh data and enable/disable query rewrite.
If you specify BUILD DEFERRED in create statement, the mview will not populate until the next first complete refresh. You can query BUILD_MODE column from DBA_MVIEWS table to check build mode for specific materialized view.

SQL> create materialized view demo_mv1
  2  refresh force on demand
  3  enable query rewrite
  4  as
  5  select sum(s.amount_sold), count(s.amount_sold), c.cust_id
  6  from sales s, customers c
  7  where c.cust_id = s.cust_id
  8  group by c.cust_id;

Materialized view created.

If you already have created table as summary you can just register that table as materialized view with ON PREBUILT TABLE statement. This is good option because user-defined materialized view can be very big and recreating materialized view from scratch will take a lot of time and performance. Now, you can register that table as materialized view and you are good to go.

SQL> create materialized view demo_tmv1
  2  enable query rewrite
  3  as
  4  select sum(s.amount_sold) t1, count(s.amount_sold) t2, c.cust_id
  5  from sales s, customers c
  6  where s.cust_id = c.cust_id
  7  group by c.cust_id;
from sales s, customers c
     *
ERROR at line 5:
ORA-00955: name is already used by an existing object

I received ORA-00955 error because the name is already used, that is because I didn’t specify ON PREBUILT TABLE.
Let’s try again.

 
SQL> create materialized view demo_tmv1
  2  on prebuilt table
  3  enable query rewrite
  4  as
  5  select sum(s.amount_sold) t1, count(s.amount_sold) t2, c.cust_id
  6  from sales s, customers c
  7  where s.cust_id = c.cust_id
  8  group by c.cust_id;

Materialized view created.

Let’s check information in DBA_MVIEWS.

SQL> select owner,mview_name,build_mode,rewrite_enabled         
  2  from dba_mviews 
  3  where mview_name = 'DEMO_TMV1';

OWNER                          MVIEW_NAME                     BUILD_MOD R
------------------------------ ------------------------------ --------- -
SH                             DEMO_TMV1                      PREBUILT  Y

If I drop the materialized view created with ON PREBUILT TABLE the detail table won’t be dropped.

SQL> drop materialized view demo_tmv1;

Materialized view dropped.

SQL> select count(*) from demo_tmv1;

  COUNT(*)
----------
      7060

If you want to benefit from query rewrite mechanism just include ENABLE_REWRITE in create statement, but be careful when specifying column alias.
If you specify column alias in the query of create materialized view statement and later you run query against details table without those column alias you won’t use query rewrite.

As a solution to this is to specify column alias as list in create statement of the materialized view.

SQL> 
SQL> create materialized view demo_mv3
  2  (sum_amount,cnt_amount,customer_id)
  3  enable query rewrite
  4  as
  5  select sum(s.amount_sold), count(s.amount_sold), c.cust_id
  6  from sales s, customers c
  7  where s.cust_id = c.cust_id
  8  group by c.cust_id;

Materialized view created.

6 thoughts on “Create Materialized Views

  1. Hi Ivica,

    Just a question: to use the estimate_mview_size, do I need have Rewrite feature enabled?

    Thanks in advance,
    Anatoli

  2. Hi Anatoli,
    Thank you for your comment.

    The estimate_mview_size just gives you information about the size of the materialized view you want to create (number of rows and number of bytes).
    You don’t need to enable rewrite in order to call dbms_mview.estimate_mview_size.
    For more information : http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_mview.htm#ARPLS67189

    Rewrite feature needs to be enabled in order for the optimizer to consider rewriting queries if possible.

    Regards,
    Ivica

    • Ivica,

      I asked this because I need estimate a size of a mview, I tested the select of the mview and got a error of feature not enabled, after for test the procedure I used this:

      DECLARE
      out_rows NUMBER;
      out_bytes NUMBER;

      BEGIN

      dbms_mview.estimate_mview_size(‘stmt1′,’select sysdate from dual’, out_rows, out_bytes);

      dbms_output.put_line(out_rows);
      dbms_output.put_line(out_bytes);
      end;
      /
      And again got the error:

      ERROR at line 1:
      ORA-30475: feature not enabled: Materialized view rewrite

      Searching on the google I saw that rewrite is only for Enterprise Edition.

      Am I doing something wrong?

      • Materialized views are specific to Enterprise Edition.
        You need to have Enterprise Edition.

        [oracle@dgpc1 ~]$ oerr ora 30475
        30475, 00000, “feature not enabled: %s”
        // *Cause: The specified feature is not enabled.
        // *Action: Do not attempt to use this feature.
        [oracle@dgpc1 ~]$

        Regards,
        Ivica

  3. You’re welcome.

    If you have access to Oracle Support you can check the following document to see the difference between Oracle editions for 11.2 and their features.

    Differences Between Enterprise, Standard and Standard One Editions on Oracle 11.2 (Doc ID 1084132.1)

    Regards,
    Ivica

Leave a Reply