Completed the Preinstallation module

Completed the Install Oracle9i Database module

Completed the Postinstallation module

Documentation: Data Warehousing Guide

Individual materialized views must have the ENABLE QUERY REWRITE clause.

From a SQL*Plus session logged on to the SH schema, run set_rewrite_session.sql, or copy the following SQL statement into your SQL*Plus session:

@set_rewrite_session

ALTER SESSION SET query_rewrite_integrity=TRUSTED;
ALTER SESSION SET query_rewrite_enabled=FORCE;
show parameters query 

You are enabling query rewrite and are using the so-called "trusted" mode. This is the most commonly used integrity level. In trusted mode, the optimizer trusts that the data in the materialized views is fresh and the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer will also use prebuilt materialized views or materialized views based on views, and it will use relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary/unique key constraints and data relationships specified using dimensions.

From a SQL*Plus session logged on to the SH schema, run create_mv1.sql, or copy the following SQL statement into your SQL*Plus session:

@create_mv1.sql

DROP MATERIALIZED VIEW cust_sales_mv ;

CREATE MATERIALIZED VIEW cust_sales_mv
   PCTFREE 0
   STORAGE (initial 8k next 8k pctincrease 0)
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP  BY c.cust_id;

This statement fails and raises the following error:

ORA-23413: table "SH"."CUSTOMERS" does not have 
           a materialized view log.

Before Oracle9i, you would have started to correct the error and tried to create the materialized view again.

Even if the statement succeeded, you could not tell about the details of its fast refresh capabilities. But using the new dbms_mview.explain_mview package introduced with Oracle9i gives you more insight into the capabilities of the potential materialized view, so that you can address all issues before its creation.

@explain_mv1.sql

truncate table mv_capabilities_table;

exec dbms_mview.explain_mview( -
   'SELECT   c.cust_id, SUM(amount_sold) AS dollar_sales -
    FROM     sales s, customers c -
    WHERE    s.cust_id= c.cust_id -
    GROUP BY c.cust_id');

set serveroutput on

begin
   for crec in ( select capability_name, possible, 
                        related_text, msgtxt
                 from   mv_capabilities_table order by 1) loop
    dbms_output.put_line(crec.capability_name ||': '||crec.possible);
    dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
   end loop;
end;
/

You can see in the output that the system indicates a missing materialized view log on the customers table. Rather than playing trial-and-error with the system, it is recommended that you always analyze potential materialized views before their creation, using the dbms_mview.explain_mview package shown above.

Besides the missing materialized view logs on customers and sales, the system also detects that you need to add additional aggregation functions to the materialized view to fully enable fast refresh capabilities for any kind of DML operation.

The aggregation functions that the system recommends are:

• COUNT(*)
• COUNT(amount_sold)

The output of the dbms_mview.explain_mview package is shown here. You can see that it not only covers the refresh capabilities of a materialized view, but also the rewrite and Partition-Change-Tracking (PCT) capabilities of the materialized view. These capabilities will be discussed later.

To correct this, first create the materialized view logs identified above.

@create_mv_logs1.sql

DROP MATERIALIZED VIEW LOG ON sales;

CREATE MATERIALIZED VIEW LOG ON sales 
   WITH ROWID, SEQUENCE
   (prod_id, cust_id, time_id, channel_id, promo_id, 
    quantity_sold, amount_sold)
   INCLUDING NEW VALUES ;

DROP MATERIALIZED VIEW LOG ON customers;

CREATE MATERIALIZED VIEW LOG ON customers 
   WITH ROWID, SEQUENCE
   (cust_id,cust_first_name,cust_last_name,cust_gender,
    cust_year_of_birth, cust_marital_status,cust_street_address,
    cust_postal_code,cust_city, cust_state_province,country_id,
    cust_main_phone_number,cust_income_level, 
    cust_credit_limit,cust_email)
   INCLUDING NEW VALUES;

This one will be used later.

DROP MATERIALIZED VIEW LOG ON products;

CREATE MATERIALIZED VIEW LOG ON products
   WITH ROWID, SEQUENCE
   (prod_id,prod_name,prod_desc,prod_subcategory,prod_subcat_desc,
    prod_category,prod_cat_desc,prod_weight_class,
    prod_unit_of_measure,prod_pack_size,supplier_id,prod_status,
    prod_list_price,prod_min_price)
   INCLUDING NEW VALUES;

Check the capabilities of the potential materialized view again.

@explain_mv1a.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC dbms_mview.explain_mview( -
   'SELECT   c.cust_id, SUM(amount_sold) AS dollar_sales, -
             COUNT(amount_sold) AS cnt_dollars, COUNT(*)  -
    FROM     sales s, customers c -
    WHERE    s.cust_id= c.cust_id -
    GROUP BY c.cust_id');

set serveroutput on

begin
   for crec in (select capability_name, possible, 
                       related_text, msgtxt
                from   mv_capabilities_table order by 1) loop
    dbms_output.put_line(crec.capability_name ||': '||crec.possible);
    dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
   end loop;
end;
/

The fast refresh capabilities of this potential materialized view have changed as expected.

Now create the materialized view.

@create_mv1b.sql

DROP MATERIALIZED VIEW cust_sales_mv ;

CREATE MATERIALIZED VIEW cust_sales_mv
   PCTFREE 0
   STORAGE (initial 8k next 8k pctincrease 0)
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales,
          COUNT(amount_sold) AS cnt_dollars,
          COUNT(*) AS cnt
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;

The explain_mview procedure also works with existing materialized views.

@explain_mv1b.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC dbms_mview.explain_mview('cust_sales_mv');

set serveroutput on

begin
   for crec in (select capability_name, possible, 
                       related_text, msgtxt
                from   mv_capabilities_table order by 1) loop
     dbms_output.put_line(crec.capability_name ||': '||crec.possible);
     dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
    end loop;
end;
/

Starting with the potential SQL statement for the materialized view, you were able to analyze its capabilities completely without creating it, leveraging the materialized view analysis capabilities of Oracle9i.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite1.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite1.sql

Rem REWRITE
TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

The plan below shows that the query is rewritten with the cust_sales_mv materialized view, using the partial text match rewrite mechanism; starting with the FROM clause, the SQL statement and the materialized view are identical.

While the query is rewritten, the access plan for a materialized view is investigated like the access of a normal table, so that any existing indexes might be used.

Next execute the query.

@do_rewrite1.sql

set timing on

SELECT c.cust_id,
       SUM(amount_sold) AS dollar_sales
FROM   sales s, customers c
WHERE  s.cust_id= c.cust_id
GROUP BY c.cust_id;

Executing the query delivers a result very quickly, because it only has to access the already joined and aggregated information in cust_sales_mv.

The plan for the nonwritten statement can be enforced by using the NOREWRITE hint. This gives you the control down to statement level, whether a query is rewritten or not.

@explain_norewrite1.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            c.cust_id,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_id;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Without Oracle’s query rewrite capabilities, you would have to do the full scan from SALES and the join with customers.

Note: This query will not be run due to time constraints.

From a SQL*Plus session logged on to the SH schema, run create_mv2.sql, or copy the following SQL statement into your SQL*Plus session:

@create_mv2.sql

DROP MATERIALIZED VIEW cust_id_sales_aggr;

CREATE MATERIALIZED VIEW cust_id_sales_aggr
   ON PREBUILT TABLE
   REFRESH FORCE
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales,
          COUNT(amount_sold) AS cnt_dollars,
          COUNT(*) AS cnt
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;

This statement is fairly fast. It doesn’t touch any data at all; it simply creates the meta information of a materialized view, which tables and columns are involved, which joins, and which aggregations.

Using materialized views on prebuilt tables is not possible with the highest level of data integrity for query rewrite (query_rewrite_integrity=ENFORCED), because the system "trusts" you as the creator with respect to the data integrity. As soon as you are going to leverage Oracle’s refresh capabilities, too, the system knows about the integrity of the data.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite2.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite2.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT c.cust_last_name,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_last_name
   ORDER BY 1;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

You can see in the plan that Oracle uses the cust_sales_mv materialized view and joins it back to the customers table using the cust_id column, which is part of the materialized view and represents the primary key-foreign key relationship between the sales and customers tables. Furthermore, the requested attribute cust_last_name in the query is a determined attribute from cust_id, so that the system knows that no additional aggregation on the dimension site must take place and it only has to join back the materialized view to the customers table. The information about hierarchies and determined attributes is part of customers_dim, the Oracle dimension object for the customers dimension. The important part of the dimension definition customers_dim is shown below:

LEVEL customer IS (customers.cust_id) 

...

ATTRIBUTE customer DETERMINES (cust_first_name,  . . .

Note: For more information about DIMENSION objects, see the Oracle Data Warehousing Guide.

Execute the query.

@do_rewrite2.sql

SELECT c.cust_last_name, SUM(amount_sold) AS dollar_sales 
FROM   sales s, customers c 
WHERE  s.cust_id= c.cust_id 
GROUP BY c.cust_last_name 
ORDER BY 1;

The plan for the non-rewritten query can be shown with the following statement:

@explain_norewrite2.sql

TRUNCATE TABLE plan_table; 

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */ c.cust_last_name, 
          SUM(amount_sold) AS dollar_sales 
   FROM   sales s, customers c 
   WHERE  s.cust_id= c.cust_id 
   GROUP BY c.cust_last_name 
   ORDER BY 1; 

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Without query rewrite, you have to process the join between your complete sales fact table and the customers dimension table.

From a SQL*Plus session logged on to the SH schema, run analyze_rewrite2.sql, or copy the following SQL statement into your SQL*Plus session: truncate table rewrite_table;

@analyze_rewrite2.sql

DECLARE
   querytxt VARCHAR2(1500) := 'select c.cust_last_name, ' ||
                              ' SUM(amount_sold) AS dollar_sales ' ||
                              'FROM   sales s, customers c ' ||
                              'WHERE  s.cust_id= c.cust_id ' ||
                              'GROUP BY c.cust_last_name';
BEGIN
   dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/

SELECT message 
FROM   rewrite_table 
ORDER BY sequence desc;

Note that the materialized view used above was not the only one possible; the cust_sales_aggr_id materialized view based on the prebuilt table would also have been eligible for rewrite. In such a case, the optimizer makes a cost-based decision.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite3.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite3.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   c.cust_state_province,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_state_province;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

You can see in the plan that Oracle uses the cust_sales_mv materialized view and joins it back to the customers table, using the cust_id column, which is part of the materialized view and represents the primary key-foreign key relationship between the sales and customers tables.

However, the existence of a possible join key column does not necessarily represent all the
information, the query rewrite mechanism needs for guaranteeing data integrity.

Examine the customers_dim dimension definition:

LEVEL customer  IS (customers.cust_id)
LEVEL city      IS (customers.cust_city)
LEVEL state     IS (customers.cust_state_province)
. . .
HIERARCHY geog_rollup (
      customer     CHILD OF
      city         CHILD OF
      state        CHILD OF
. . .

The requested cust_state_province attribute represents the so-called level state, a higher aggregation level than customer, represented by cust_id. Levels and hierarchies represent a declarative way for representing a 1:n relationship inside one table. In this case, it expresses the validity of aggregating all customer information to the level state without violating data integrity. For every distinct customer value, you will get one and only one state value.

Now issue the query. It will run fairly fast.

@do_rewrite3.sql

SELECT   c.cust_state_province,
         SUM(amount_sold) AS dollar_sales
FROM     sales s, customers c
WHERE    s.cust_id= c.cust_id
GROUP BY c.cust_state_province;

From a SQL*Plus session logged on to the SH schema, run explain_rewrite4.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite4.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   /*+ REWRITE(cust_sales_mv) */ co.country_name, 
            c.cust_state_province, SUM(amount_sold) AS dollar_sales 
   FROM     sales s, customers c, countries co
   WHERE    s.cust_id= c.cust_id 
     AND    c.country_id = co.country_id 
   GROUP BY co.country_name, c.cust_state_province 
   ORDER BY 1,2;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

The optimizer rewrites the query to take advantage of the cust_sales_mv materialized view and joins it back to customers and customers to countries to satisfy the query. In the same way that you enforced a query on the statement level not to be rewritten you can enforce the use of a specific materialized view.

Various data integrity checks must take place to guarantee the validity of using this materialized view. Besides the check for losslessness and nonduplicating joins, the evaluation of the dimension information plays an important role for the rewrite process.

Examine what the optimizer uses to rewrite this query. The following is an excerpt of the dimension definition of customers_dim. It shows the important parts for this query:

LEVEL customer    IS (customers.cust_id)
LEVEL . . .
LEVEL state       IS (customers.cust_state_province)
LEVEL country     IS (countries.country_id)
. . .
HIERARCHY geog_rollup (
       customer      CHILD OF
       . . .
       state         CHILD OF
       country       CHILD OF
. . .
JOIN KEY (customers.country_id) REFERENCES country
)
. . .
ATTRIBUTE country DETERMINES (countries.country_name)

The Oracle database has to determine whether or not it can derive all requested attributes based on the information that is stored in the materialized view. You are requesting countries.country_name and customers.cust_state_province in your query; the materialized view contains only cust_id as information.

Based on the information in the customers_dim dimension, the Oracle database determines the following:

• customers.cust_state_province can be determined by cust_id in the materialized view. It represents a higher aggregation level in the dimension than level customers.
• countries.country_id can also be determined by cust_id in the materialized view. countries.country_id describes a higher aggregation level than customers.
• countries.country_name is a determined attribute of the hierarchical level country and can therefore be determined based on countries.country_id.
• The customers_dim dimension describes a hierarchical dependency across two tables. The join condition is part of the dimension information.

The Oracle database uses all of this information to join the materialized view not only with customers, but customers also with countries to get the result for the query and to guarantee that the query result is correct.

The rewritten SQL statement looks like the following:

SELECT   co.country_name,
         mv.cust_state_province,
         SUM(mv.dollar_sales) AS dollar_sales
FROM     cust_sales_mv mv,
         ( SELECT DISTINCT cust_state_province, country_id 
           FROM customers
         ) c,
         countries co
WHERE    mv.cust_state_province = c.cust_state_province
AND      c.country_id = co.country_id
GROUP BY co.country_name, mv.cust_state_province
ORDER BY 1,2;

Now execute the query.

@do_rewrite4.sql

SELECT   c.cust_state_province,
         SUM(amount_sold) AS dollar_sales
FROM     sales s, customers c
WHERE    s.cust_id= c.cust_id
GROUP BY c.cust_state_province;

To get the plan without query rewrite, you can run explain_norewrite4.sql or copy the following SQL statement into your SQL*Plus session.

@explain_norewrite4.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */
          co.country_name,
          c.cust_state_province,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c, countries co
   WHERE  s.cust_id= c.cust_id
   AND    c.country_id = co.country_id
   GROUP BY co.country_name, c.cust_state_province;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

From a SQL*Plus session logged on to the SH schema, run create_mv3.sql, or copy the following SQL statement into your SQL*Plus session:

@create_mv3.sql

DROP MATERIALIZED VIEW some_cust_sales_mv;

CREATE MATERIALIZED VIEW some_cust_sales_mv
   BUILD IMMEDIATE
   REFRESH COMPLETE
   ENABLE QUERY REWRITE
   AS
   SELECT   c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
            sum(s.quantity_sold) as quantity
   FROM     sales s , customers c, products p
   WHERE    c.cust_id = s.cust_id
   AND      s.prod_id = p.prod_id
   AND      c.cust_state_province IN 
               ('Dublin','Galway','Hamburg','Istanbul')
   GROUP BY c.cust_id, p.prod_id;

Make note of how long it takes to create the materialized view.

Now create the same materialized view without a predicate to restrict the result set.

@create_mv3b.sql

DROP MATERIALIZED VIEW all_cust_sales_mv;

CREATE MATERIALIZED VIEW all_cust_sales_mv
   BUILD IMMEDIATE
   REFRESH COMPLETE
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
          sum(s.quantity_sold) as quantity
   FROM   sales s , customers c, products p
   WHERE  c.cust_id = s.cust_id
   AND    s.prod_id = p.prod_id
   GROUP BY c.cust_id, p.prod_id;

It takes longer to create the materialized view, because all data must be touched, joined, and aggregated.

From a SQL*Plus session logged on to the SH schema, run estimate_mv_size1.sql, or copy the following SQL statement into your SQL*Plus session. This will give you a size estimate for the materialized view containing all data.

@estimate_mv_size1.sql

set serveroutput on;

DECLARE
   no_of_rows NUMBER;
   mv_size NUMBER;
BEGIN
   no_of_rows :=555;
   mv_size :=5555;
   dbms_olap.estimate_summary_size ('MV 1',
      'SELECT   c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
                sum(s.quantity_sold) as quantity
       FROM     sales s , customers c, products p
       WHERE    c.cust_id = s.cust_id
         AND    s.prod_id = p.prod_id
       GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
   DBMS_OUTPUT.put_line ( '');
   DBMS_OUTPUT.put_line ( 'Complete MV');
   DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
   DBMS_OUTPUT.put_line ( 'Size of Materialized view (bytes): ' - 
                          || mv_size );
   DBMS_OUTPUT.put_line ( '');
END;
/

Determine the size of the materialized view containing the subset of data:

@estimate_mv_size2.sql

DECLARE
   no_of_rows NUMBER;
   mv_size NUMBER;
BEGIN
   no_of_rows :=555;
   mv_size :=5555;
   dbms_olap.estimate_summary_size ('MV 2',
      'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
              sum(s.quantity_sold) as quantity
       FROM   sales s , customers c, products p
       WHERE  c.cust_id = s.cust_id
       AND    s.prod_id = p.prod_id
       AND    c.cust_state_province IN

                 (''Dublin'',''Galway'',''Hamburg'',''Istanbul'')
       GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
       DBMS_OUTPUT.put_line ( 'Partial MV');
       DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
       DBMS_OUTPUT.put_line ( 'Size of Materialized view (bytes): ' -
                              || mv_size );
DBMS_OUTPUT.put_line ( '');
END;
/

Now look in the data dictionary to get the actual sizes of the two new materialized views.

@comp_mv_size.sql

SELECT   substr(segment_name,1,30) "MV name", bytes/1024 KB
FROM     user_segments 
WHERE    segment_name in ('SOME_CUST_SALES_MV','ALL_CUST_SALES_MV')
ORDER BY segment_name ;

The materialized view containing only the subset of data is about 9 times smaller than the view containing all data. Especially in very large environments, this provides a tremendous benefit and simplifies the use of materialized views for "special analysis purposes," touching only parts of the information in your data warehouse.

From a SQL*Plus session logged on to the SH schema, run analyze_subset_rewrite.sql, or copy the following SQL statement into your SQL*Plus session:

@analyze_subset_rewrite.sql

TRUNCATE TABLE rewrite_table;

DECLARE
   querytxt VARCHAR2(1500) := -
             'SELECT c.cust_id, ' ||
                    'sum(s.amount_sold) AS dollars,p.prod_id, ' ||
                    'sum(s.quantity_sold) as quantity ' ||
             'FROM   sales s , customers c, products p ' ||
             'WHERE  c.cust_id = s.cust_id ' ||
             'AND    s.prod_id = p.prod_id ' ||
             'AND    c.cust_state_province IN '|| 
                      ' (''Dublin'',''Galway'',''Hamburg'') ' ||
             'GROUP BY c.cust_id, p.prod_id';
BEGIN
   dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/

SELECT message 
FROM   rewrite_table 
ORDER BY sequence desc;

You can see that the subset materialized view is chosen over the one containing all the data, because of its lower cost.

From a SQL*Plus session logged on to the SH schema, run explain_subset_rewrite2.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_subset_rewrite2.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   c.cust_last_name, sum(s.amount_sold) AS dollars,
            sum(s.quantity_sold) as quantity
   FROM     sales s , customers c, products p
   WHERE    c.cust_id = s.cust_id
     AND    s.prod_id = p.prod_id
     AND    c.cust_state_province IN ('Dublin','Galway')
   GROUP BY c.cust_last_name;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Run the query also:

@run_subset_rewrite2.sql

SELECT   c.cust_last_name, sum(s.amount_sold) AS dollars,
         sum(s.quantity_sold) as quantity
FROM     sales s , customers c, products p
WHERE    c.cust_id = s.cust_id
  AND    s.prod_id = p.prod_id
  AND    c.cust_state_province IN ('Dublin','Galway')
GROUP BY c.cust_last_name;

From a SQL*Plus session logged on to the SH schema, run the following SQL statement:

@create_gby_mv.sql

drop materialized view sales_cube_mv;

create materialized view sales_cube_mv
   enable query rewrite
   as
   SELECT calendar_year year, calendar_quarter_desc quarter, 
          calendar_month_desc month, cust_state_province state, 
          cust_city city,
          GROUPING_ID (calendar_year,calendar_quarter_desc,
                       calendar_month_desc,
                       cust_state_province,cust_city) gid,
          GROUPING(calendar_year) grp_y,
          GROUPING(calendar_quarter_desc) grp_q,
          GROUPING(calendar_month_desc) grp_m,
          GROUPING(cust_state_province) grp_s,
          GROUPING(cust_city) grp_c,
          SUM(amount_sold) sum_sales
   FROM sales s, times t, customers c
   WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year, cust_city),
                           (calendar_year, cust_city, 
                            cust_state_province),
                           (calendar_year, calendar_quarter_desc, 
                            calendar_month_desc,cust_city));

exec dbms_stats.gather_table_stats('SH','sales_cube_mv');

drop materialized view sales_gby_mv;

create materialized view sales_gby_mv
   enable query rewrite
   as
   SELECT   calendar_year year, cust_state_province state,
            SUM(amount_sold) sum_sales
   FROM     sales s, times t, customers c
   WHERE    s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY (calendar_year, cust_state_province);

exec dbms_stats.gather_table_stats('SH','sales_gby_mv');

From a SQL*Plus session logged on to the SH schema run the following SQL statement:

@rewrite_gby1.sql

truncate table plan_table;

explain plan for
   SELECT calendar_year year, calendar_quarter_desc quarter,
          calendar_month_desc month,
          cust_state_province state,
          SUM(amount_sold) sum_sales
   FROM   sales s, times t, customers c
   WHERE  s.time_id=t.time_id
     AND  s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year, cust_city),
            (calendar_year, cust_city, cust_state_province),
            (calendar_year, calendar_quarter_desc,
             calendar_month_desc, cust_city));

PROMPT new output, using table function
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

When both materialized view and the query contain GROUP BY extensions, Oracle uses two strategies for rewrite: grouping match and UNION ALL rewrite. First, Oracle tries grouping match. The groupings in the query are matched against groupings in the materialized view and if all are matched with no rollup, Oracle selects them from the materialized view. The grouping match takes place in this example; a full table scan of the materialized view, without any filter conditions, satisfies our query.

From a SQL*Plus session logged on to the SH schema run the following SQL statement:

@rewrite_gby2.sql

PROMPT full access AND joinback to dimension customers
PROMPT decompose of grouping levels into UNION ALL

truncate table plan_table;

explain plan for
   SELECT   calendar_year year, calendar_quarter_desc quarter,
            cust_state_province state, country_id,
            SUM(amount_sold) sum_sales
   FROM     sales s, times t, customers c
   WHERE    s.time_id=t.time_id
     AND    s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year, country_id),
                           (calendar_year, cust_state_province),
                           (calendar_year, calendar_quarter_desc,
                            cust_state_province));

PROMPT new output, using table function
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

In this case the grouping match fails. Oracle tries a general rewrite mechanism called UNION ALL rewrite. Oracle first represents the query with the extended GROUP BY clause as an equivalent UNION ALL query. Every grouping of the original query is placed in a separate UNION ALL branch. The branch will have a simple GROUP BY clause.

To satisfy the above shown query with the existing materialized view, Oracle has rewrite the GROUPING SETS into three simple GROUP BY expressions, combined with a UNION ALL operator. It then investigates the rewrite capabilities for each of the UNION ALL branches independently. Each of the branches might be rewritten with a materialized view containing a simple or an extended GROUP BY condition.

As you can see, all basic rewrite mechanisms, such as JOIN BACK are being used.

From a SQL*Plus session logged on to the SH schema run the following SQL statement:

@rewrite_gby3.sql

PROMPT full access of MV and usage of SALES fact ofr missing level
PROMPT decompose of grouping levels into UNION ALL

truncate table plan_table;

explain plan for
   SELECT   calendar_year year, calendar_quarter_desc quarter,
            week_ending_day,cust_state_province state,
            SUM(amount_sold) sum_sales
   FROM     sales s, times t, customers c
   WHERE    s.time_id=t.time_id
     AND    s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year),
                           (calendar_year, week_ending_day),
                           (calendar_year, cust_state_province),
                           (calendar_year, calendar_quarter_desc,
                            cust_state_province));

PROMPT new output, using table function
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Creating a Materialize View Containing the Partitioning Key

Analyzing the Materialized View Containing the Partitioning Key

From a SQL*Plus session logged on to the SH schema, run cleanup_for_pmop1.sql, or copy the following SQL statement into your SQL*Plus session:

@cleanup_for_pmop1.sql

ALTER TABLE costs DROP PARTITION costs_q1_2001;
ALTER TABLE costs DROP PARTITION costs_q2_2001;
ALTER TABLE costs DROP PARTITION costs_1_2001;

We need the materialized view log for fast refresh capabilities of our next materialized view.

DROP MATERIALIZED VIEW LOG ON costs;

CREATE MATERIALIZED VIEW LOG ON costs
   WITH ROWID, SEQUENCE
   (prod_id, time_id, unit_cost, unit_price )
   INCLUDING NEW VALUES ;

You can ignore any ORA-2149 and ORA-12002 SQL errors.

From a SQL*Plus session logged on to the SH schema, run create_pkey_mv.sql, or copy the following SQL statement into your SQL*Plus session:

@create_pkey_mv.sql

DROP MATERIALIZED VIEW costs_mv;

CREATE MATERIALIZED VIEW costs_mv
   PCTFREE 0
   STORAGE (initial 8k next 8k pctincrease 0)
   BUILD IMMEDIATE
   REFRESH FAST ON DEMAND
   ENABLE QUERY REWRITE 
   AS
   SELECT time_id, prod_name, SUM( unit_cost) AS sum_units,
          COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
   FROM   costs c, products p
   WHERE  c.prod_id = p.prod_i