Completed the Configuring Linux for the Installation of Oracle Database 10g lesson

Completed the Installing the Oracle Database 10g on Linux lesson

Completed the Postinstallation Tasks lesson.

Downloaded and unzipped mvplans.zip into your working directory (for example, /home/oracle/wkdir)

Create the materialized view and gather statistics on the materialized view and its underlying tables. From your terminal window, execute the following commands:

cd wkdir
sqlplus sh/sh
@mvsetup

The query in the mvsetup.sql script is as follows:

drop materialized view sales_prod;

create materialized view sales_prod
       build immediate
       enable query rewrite
as
SELECT s.prod_id
,      t.fiscal_month_number 
,      sum(s.amount_sold) AS sum_amount
FROM   sales s, times t
WHERE  s.time_id = t.time_id
AND    t.fiscal_year = 2000
GROUP  BY s.prod_id, t.fiscal_month_number;

execute dbms_stats.gather_table_stats('SH','SALES_PROD');
execute dbms_stats.gather_table_stats('SH','SALES');
execute dbms_stats.gather_table_stats('SH','TIMES');

Now you can generate an execution plan for a query that will be rewritten. Execute the following script from your terminal window:

@execplan01

The query in the execplan01.sql script is as follows:

EXPLAIN PLAN FOR
SELECT s.prod_id
,      t.fiscal_month_number 
,      sum(s.amount_sold) AS sum_amount
FROM   sales s, times t
WHERE  s.time_id = t.time_id
AND    t.fiscal_year = 2000
GROUP  BY s.prod_id, t.fiscal_month_number
ORDER  BY s.prod_id, t.fiscal_month_number;

Now that the execution plan is generated, you can use the DBMS_XPLAN package to display the execution plan. From your terminal window, execute the following command:

SELECT * FROM table(dbms_xplan.display);

Note the execution plan explicitly shows materialized view usage and the purpose (REWRITE).

To see what would happen if the materialized view was explicitly accessed in the FROM component of a query, execute the following command:

EXPLAIN PLAN FOR SELECT * FROM sales_prod;

Now you can display the execution plan again to see the difference. From your terminal window, execute the following command:

SELECT * FROM table(dbms_xplan.display);

Note the execution plan still shows MAT_VIEW access (as opposed to TABLE access) but the REWRITE is gone.

Before you begin you need to re-create your rewrite table. The utlxrw.sql script creates the REWRITE table. You need this table to capture the output of the EXPLAIN_REWRITE procedure. From your terminal window, execute the following command:

@crewrt01

The command in the crewrt01.sql script is as follows:

drop table rewrite_table;
@$ORACLE_HOME/rdbms/admin/utlxrw

Typically when a query rewrite fails, the Oracle database executes the statement against the underlying base tables. The REWRITE_OR_ERROR hint changes the behavior, and generates an error message when query rewrite fails. To use a hint in a query, execute the following command from your terminal window:

@hint01

The command in the hint01.sql script is as follows:

SELECT   /*+ REWRITE_OR_ERROR */
         s.prod_id
,        sum(s.quantity_sold)
FROM     sales s
GROUP BY s.prod_id;

Note that an error message was generated.

You can use the EXPLAIN_REWRITE procedure to determine why query rewrite failed. The results will be captured in the REWRITE_TABLE you created previously. Execute the following script:

@exprewrt01

The command in the exprewrt01.sql script is as follows:

execute dbms_mview.EXPLAIN_REWRITE             -
( query        => 'SELECT s.prod_id            -
                   ,      sum(s.quantity_sold) -
                   FROM   sales s              -
                   GROUP BY s.prod_id'         -
, mv           => 'SH.SALES_PROD'              -
, statement_id => 'EXPLAIN_REWRITE demo'       -
);

Now you can query the REWRITE_TABLE to obtain results. Execute the following script:

@shresult01

The command in the shresult01.sql script is as follows:

SELECT message
FROM   rewrite_table
WHERE  statement_id = 'EXPLAIN_REWRITE demo';

Note that there are three issues with the query.

To see where the issues are, look at the materialized view definition you created at the beginning of this lesson as follows:

CREATE MATERIALIZED VIEW sales_prod
  build immediate
  enable query rewrite
  as
     SELECT s.prod_id
          , t.fiscal_month_number 
          , sum(s.amount_sold) AS sum_amount
     FROM sales s, times t
     WHERE s.time_id = t.time_id
     AND t.fiscal_year = 2000
     GROUP BY s.prod_id, t.fiscal_month_number;

Then you need to look at the query you executed as follows:

SELECT s.prod_id
     , sum(s.quantity_sold)
FROM sales s
GROUP BY s.prod_id;

As you can see, the materialized view utilizes the TIMES dimension, which is not used in the query, and the materialized view aggregates AMOUNT_SOLD while the query is aggregating QUANTITY_SOLD. Therefore query rewrite is impossible.