Materialized views : Part 2

Purging Data from Data Warehouses

1. One approach to removing a large volume of data is to use parallel delete as shown in the following statement:

DELETE FROM sales WHERE sales_product_id IN (SELECT product_id

FROM product WHERE product_category = 'XYZ Software');

2. An alternative method is to re-create the entire sales table, keeping the data for all product categories except XYZ Software.

CREATE TABLE sales2 AS SELECT * FROM sales, product

WHERE sales.sales_product_id = product.product_id

AND product_category <> 'XYZ Software'

NOLOGGING PARALLEL (DEGREE 8)

#PARTITION ... ;

#create indexes, constraints, and so on

DROP TABLE SALES;

RENAME SALES2 TO SALES;

3. An alternative method to utilize less space is to re-create the sales table one partition at a time:

CREATE TABLE sales_temp AS SELECT * FROM sales WHERE 1=0;

INSERT INTO sales_temp

SELECT * FROM sales PARTITION (sales_99jan), product

WHERE sales.sales_product_id = product.product_id

AND product_category <> 'XYZ Software';

<create appropriate indexes and constraints on sales_temp>

ALTER TABLE sales EXCHANGE PARTITION sales_99jan WITH TABLE sales_temp;

Continue this process for each partition in the sales table

Data Dictionary Views that Store Materialized View Refresh Statistics

SELECT refresh_id, refresh_method, elapsed_time, initial_num_rows, final_num_rows

FROM dba_mvref_stats

WHERE mv_name = 'NEW_SALES_RTMV' and mv_owner = 'SH';

REFRESH_ID REFRESH_METHOD ELAPSED_TIME INITIAL_NUM_ROWS FINAL_NUM_ROWS

---------- -------------- ------------- ---------------- ----------------

49     FAST         0            766                 788

61     FAST         1            788                 788

81     FAST         1            788                 798

The following example displays the materialized view names, SQL statements used to refresh the materialized view, and execution time for the materialized view refresh operation with refresh ID is 1278.

SELECT mv_name, step, stmt, execution_time

FROM dba_mvref_stmt_stats

WHERE refresh_id = 1278

Query Rewrite for Materialized Views

Query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.

The DBMS_MVIEW.EXPLAIN_REWRITE procedure advises whether query rewrite is possible on a query and, if so, which materialized views are used.

• Individual materialized views must have the ENABLE QUERY REWRITE clause
• The session parameter QUERY_REWRITE_ENABLED must be set to TRUE (the default) or FORCE.
• Cost-based optimization must be used by setting the initialization parameter OPTIMIZER_MODE to ALL_ROWS, FIRST_ROWS, or FIRST_ROWS_n.

Verifying that Query Rewrite has Occurred

To confirm that query rewrite does occur, use the EXPLAIN PLAN statement or the DBMS_MVIEW.EXPLAIN_REWRITE procedure.

1. EXPLAIN PLAN statement:  You need to check that the operation shows MAT_VIEW REWRITE ACCESS. If it does, then query rewrite has occurred.

EXPLAIN PLAN FOR

SELECT t.calendar_month_desc, SUM(s.amount_sold)

FROM sales s, times t WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

However, PLAN_TABLE must first be created using the utlxplan.sql script. This script can be

found in the admin directory

SELECT OPERATION, OBJECT_NAME FROM PLAN_TABLE;

OPERATION                 OBJECT_NAME

-------------------- -----------------------

SELECT STATEMENT   

MAT_VIEW REWRITE ACCESS    CALENDAR_MONTH_SALES_MV

2. Using  the DBMS_MVIEW.EXPLAIN_REWRITE procedure

The following shows the basic syntax for using an output table:

DBMS_MVIEW.EXPLAIN_REWRITE (

query VARCHAR2,  -- text string representing the SQL query

mv VARCHAR2(30), -- fully-qualified materialized view name in the form of schema.mv

statement_id VARCHAR2(30));

You can create an output table called REWRITE_TABLE by executing the utlxrw.sql script.

SELECT message FROM rewrite_table;

/*+ NOREWRITE */

/*+ REWRITE (sum_sales_pscat_week_mv) */

/*+ REWRITE_OR_ERROR */