Pro Oracle Database 11g Administration (64 page)
379
CHAPTER 15 ■ MATERIALIZED VIEWS
You can verify the refresh mode and method with the following query:
select
mview_name
,refresh_mode
,refresh_method
from user_mviews;
Creating Materialized Views for Query-Rewrite
Query rewrite allows the optimizer to recognize that an MV can be used to fulfill the requirements of a query instead of using the underlying master (base) tables. If you have an environment where users frequently write their own queries and are unaware of the available MVs, this feature can help greatly with performance. There are three prerequisites for enabling query-rewrite:
• Oracle Enterprise Edition
• Database initialization parameter QUERY_REWRITE_ENABLED must be set to TRUE
(which is the default in Oracle Database 10
g
or higher
• MV either created or altered with the ENABLE QUERY REWRITE clause
This example creates an MV with query-rewrite enabled:
create materialized view sales_mv
tablespace mv_data
using index tablespace mv_index
enable query rewrite
as
select
sum(sales_amt)
,b.reg_desc
from sales a
,region b
where a.region_id = b.region_id
group by b.reg_desc;
You can tell if query-rewrite is enabled for an MV by selecting the REWRITE_ENABLED column from USER_MVIEWS:
select
mview_name
,rewrite_enabled
,rewrite_capability
from user_mviews
where mview_name = 'SALES_MV';
Here’s the output:
MVIEW_NAME R REWRITE_C
---------- - ---------
SALES_MV Y GENERAL
If for any reason a query isn’t using the query-rewrite functionality, and you think it should be, use the EXPLAIN_REWRITE procedure of the DBMS_MVIEW package to diagnose issues.
380
CHAPTER 15 ■ MATERIALIZED VIEWS
Creating a Fast-Refreshable MV Based on a Complex Query
In many situations, when you base an MV on a query that joins multiple tables, it’s deemed complex, and therefore is available only for a complete refresh. However, in some scenarios, you can create a fast-refreshable MV when you reference two tables that are joined together in the MV query.
This section describes how to use the EXPLAIN_MVIEW procedure of the DBMS_MVIEW to determine whether it’s possible to fast-refresh a complex query. To help you completely understand the example, this section shows the SQL used to create the base tables. Say you have two base tables, REGION and INV, which are defined as follows:
create table region(
region_id number
,reg_desc varchar2(30)
,constraint region_pk primary key(region_id));
--
create table inv(
inv_id number
,inv_desc varchar2(30)
,region_id number
,constraint inv_pk primary key(inv_id)
,constraint dept_fk foreign key (region_id) references region(region_id)); Additionally, REGION and INV have MV logs created on them as follows: SQL> create materialized view log on region with primary key;
SQL> create materialized view log on inv with primary key;
Also, for this example, the base tables have the following data inserted into them: insert into region values(10,'East');
insert into region values(20,'West');
insert into region values(30,'South');
insert into region values(40,'North');
--
insert into inv values(1,'book',10);
insert into inv values(2,'table',20);
insert into inv values(3,'chair',30);
Suppose you want to create an MV that joins the REGION and INV base tables as follows: create materialized view inv_mv
tablespace mv_data
using index tablespace mv_index
as
select
a.inv_desc
,b.reg_desc
from inv a
,region b
where a.region_id = b.region_id;
Next, let’s attempt to fast-refresh the MV:
SQL> exec dbms_mview.refresh('INV_MV','F');
This error is thrown:
ORA-12032: cannot use rowid column from materialized view log on "MV"."REGION"
381
CHAPTER 15 ■ MATERIALIZED VIEWS
The error indicates that the MV has issues and can’t be fast-refreshed. To determine whether this MV can become fast-refreshable, use the output of the EXPLAIN_MVIEW procedure of the DBMS_MVIEW
package. This procedure requires that you first create MV_CAPABILITIES_TABLE first be created. Oracle provides a script to do this. Run this as the owner of the MV:
SQL> @?/rdbms/admin/utlxmv.sql
After you create the table, run the EXPLAIN_MVIEW procedure to populate it: SQL> exec dbms_mview.explain_mview(mv=>'INV_MV',stmt_id=>'100'); Now, query MV_CAPABILITIES_TABLE to see what potential issues this MV may have: select
capability_name
,possible
,msgtxt
,related_text
from mv_capabilities_table
where capability_name like 'REFRESH_FAST_AFTER%'
and statement_id = '100'
order by 1;
Listed next is the output. The P (POSSIBLE) column contains an N (NO) for every fast-refresh possibility:
CAPABILITY_NAME P MSGTXT RELATED_TEXT
------------------------------ - ------------------------------ ---------------
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_INSERT N mv log must have ROWID MV.REGION
REFRESH_FAST_AFTER_INSERT N the SELECT list does not have B
the rowids of all the detail t
ables
REFRESH_FAST_AFTER_INSERT N mv log must have ROWID MV.INV
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled
MSGTXT indicates one issue: the MV logs need to be ROWID based. Let’s drop and re-create the MV logs with ROWID (instead of a primary key):
drop materialized view log on region;
drop materialized view log on inv;
--
create materialized view log on region with rowid;
create materialized view log on inv with rowid;
Next, reset MV_CAPABILITIES_TABLE and repopulate it via the EXPLAIN_MVIEW procedure: SQL> delete from mv_capabilities_table where statement_id=100;
SQL> exec dbms_mview.explain_mview(mv=>'INV_MV',stmt_id=>'100'); Re-running the previous query that selects from MV_CAPABILITIES_TABLE (not reproduced here) shows the output:
CAPABILITY_NAME P MSGTXT RELATED_TEXT
------------------------------ - ------------------------------ ---------------
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
382
CHAPTER 15 ■ MATERIALIZED VIEWS
T_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_INSERT N mv log is newer than last full MV.REGION
refresh
REFRESH_FAST_AFTER_INSERT N the SELECT list does not have B
the rowids of all the detail t
ables
REFRESH_FAST_AFTER_INSERT N mv log is newer than last full MV.INV
refresh
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled
From the previous output, the MV SELECT statement needs to contain the ROWIDs of the base tables.
The next few lines of code drop the MV and re-create it with the SQL statement rewritten to contain the ROWIDs:
drop materialized view inv_mv;
--
create materialized view inv_mv
as
select
a.rowid inv_rowid
,b.rowid region_rowid
,a.inv_desc
,b.reg_desc
from inv a
,region b
where a.region_id = b.region_id;
Next, reset MV_CAPABILITIES_TABLE and repopulate it via the EXPLAIN_MVIEW procedure: SQL> delete from mv_capabilities_table where statement_id=100;
SQL> exec dbms_mview.explain_mview(mv=>'INV_MV',stmt_id=>'100'); The output shows that it’s now possible to fast-refresh the MV:
CAPABILITY_NAME P MSGTXT RELATED_TEXT
------------------------------ - ------------------------------ ---------------
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
Execute the following statement to see if the fast refresh works:
SQL> exec dbms_mview.refresh('INV_MV','F');
PL/SQL procedure successfully completed.
The EXPLAIN_MVIEW procedure is a powerful tool that allows you to determine whether a refresh capability is possible and, if it’s not possible, why it isn’t and how to potentially resolve the issue.
Viewing Materialized View DDL
To quickly view the SQL query on which an MV is based, select from the QUERY column of DBA/ALL/USER_MVIEWS. If you’re using SQL*Plus, first set the LONG variable to a value large enough to display the entire contents of a LONG column:
383
CHAPTER 15 ■ MATERIALIZED VIEWS
SQL> set long 5000
SQL> select query from dba_mviews where mview_name=UPPER('&&mview_name'); To view the entire Data Definition Language ()DDL required to re-create an MV, use the DBMS_METADATA package (you also need to set the LONG variable to a large value if using SQL*Plus): SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','INV_MV') from dual; Here’s the output for this example:
CREATE MATERIALIZED VIEW "MV"."INV_MV" ("INV_ROWID", "REGION_ROWID", "INV_DESC",
"REG_DESC") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select
a.rowid inv_rowid
,b.rowid region_rowid
,a.inv_desc
,b.reg_desc
from inv a
,region b
where a.region_id = b.region_id
This output shows the DDL that Oracle thinks is required to re-create the MV. This is usually the most reliable way to generate the DDL associated with an MV.
Dropping a Materialized View
You may occasionally need to drop an MV. Perhaps a view is no longer being used, or you need to drop and re-create an MV to change the underlying query on which the MV is based (such as adding a column to it). Use the DROP MATERIALIZED VIEW command to drop an MV. This example drops a view named ORDERS_MV:
SQL> drop materialized view orders_mv;
When you drop an MV, the MV object, the table object, and any corresponding indexes are also dropped. Dropping an MV doesn’t affect any MV logs—an MV log is dependent only on the master table.
You can also specify that the underlying table be preserved. You may want to do this if you’re troubleshooting and need to drop the MV definition but keep the MV table and data. For example: SQL> drop materialized view inv_mv preserve table;
In this scenario, you can also use the underlying table later as the basis for an MV by building the MV using the ON PREBUILT TABLE clause.
384
CHAPTER 15 ■ MATERIALIZED VIEWS
If the MV was originally built using the ON PREBUILT TABLE clause, then when you drop the MV, the underlying table isn’t dropped. If you want the underlying table dropped, you must use a DROP TABLE
statement:
SQL> drop materialized view inv_mv;
SQL> drop table inv_mv;
Modifying Materialized Views
This section describes common maintenance tasks associated with MVs. Topics covered include how to modify an MV to reflect column changes that have been applied to the base table some time after the MV
was initially created, and modifying attributes such as logging and parallelism.
Modifying Base-Table DDL and Propagating to Materialized Views
A common task involves adding a column to or dropping a column from a base table (because business requirements have changed). After the column is added to or dropped from the base table, you want those DDL changes to be reflected in any dependent MVs. You have a few options for propagating base-table column changes to dependent MVs:
• Drop and re-create the MV with the new column definitions.
• Drop the MV, but preserve the underlying table, modify the MV table, and then recreate the MV (with the new column changes) using the ON PREBUILT TABLE clause.
• If the MV was originally created using the ON PREBUILT TABLE clause, drop the MV
object, modify the MV table, and then re-create the MV (with the new column changes) using the ON PREBUILT TABLE clause.
With any of the previous options, you have to drop and re-create the MV so that it’s aware of the column changes in the base table. These approaches are described next.
Re-creating a Materialized View to Reflect Base-Table Modifications
Suppose you make a modification to a base table, such as adding a column: SQL> alter table inv add(inv_loc varchar2(30));
You also have a simple MV named INV_MV that is based on this table. You want the base-table modification to be reflected in the MV. How do you accomplish this task? You know that the MV
contains an underlying table that stores the results. You decide to modify the underlying MV table directly:
SQL> alter table inv_mv add(inv_loc varchar2(30));
The alteration is successful. You next refresh the MV but realize that the additional column isn’t being refreshed. To understand why, recall that an MV is a SQL query that stores its results in an underlying table. Therefore, to modify an MV, you have to change the SQL query that the MV is based on. Because there is no ALTER MATERIALIZED VIEW ADD/DROP/MODIFY