Manage a fast refreshable materialized view

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] About Refreshing Materialized Views
How to Refresh Materialized Views?
About the Out-of-Place Refresh Option
When Fast Refresh is Possible
Monitoring a Refresh
Tips for Refreshing Materialized Views
[2] Using Materialized Views with Partitioned Tables

——–
Hands On
——–
[1] Create a simple materialized view and USER_* view to display it
[2] Updatable MATERIALIZED View
[3] Create a materialized view, make sure all rows are populated after the view
is created and make sure the view is truncated every time it is referenced.
[4] FAST REFRESH SENARIOS.
4.1 Create a materialized view and make sure it pull changes made to the
master table after last refresh.
4.2 Create a materialized view log and make sure to purge it every 3 hours.
4.3 Create a materialized view and make sure it is updated every time the master table changes.
[5] Tips for Fast Refresh with Commit SCN-Based Materialized View Logs
[6] Create a materialized view and make sure it is refreshed every 7 minutes.
Check the status of the refresh job. Disable auto refresh job.
[7] create two tables based on DBA_OBJECTS and DBA_EXTENS views.
create a query to join them and get the execution plan and the time that is
used to run the query. Next create a materialized view based on the same
query and make sure the query uses the materialized view next time it is
executed. Get the execution plan and timing.
[8] Use DBMS_MVIEW.EXPLAIN_REWRITE package to make sure if the query rewrite will be enabled for the specific query.
[9] Analyzing Materialized Views for Fast Refresh ( DBMS_MVIEW.EXPLAIN_MVIEW )
[10] Use DBMS_ADVISOR.TUNE_MVIEW package and recreate the MV with a different definition which
will allow for fast refreshes.
[11] Using TUNE_MVIEW and DBMS_MVIEW.EXPLAIN_REWRITE to tune mv fast refreshes and query rewrites.
[12] DBMS_REFRESH API
[13] Configure and manage distributed materialized views

Database Data Warehousing Guide
7 Refreshing Materialized Views

[1] About Refreshing Materialized Views

The database maintains data in materialized views by refreshing them 
after changes to the base tables. Performing a refresh operation requires temporary space to rebuild the
indexes and can require additional space for performing the refresh operation
itself. Some sites might prefer not to refresh all of their materialized views
at the same time: as soon as some underlying detail data has been updated,
all materialized views using this data become stale. Therefore, if you defer refreshing your materialized views, you can either
rely on your chosen rewrite integrity level to determine whether or not a
stale materialized view can be used for query rewrite, or you can temporarily
disable query rewrite with an ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE
statement. After refreshing the materialized views, you can re-enable query
rewrite as the default for all sessions in the current database instance by
specifying ALTER SYSTEM SET QUERY_REWRITE_ENABLED as TRUE. Refreshing a materialized view automatically updates all of its indexes. In the case of full refresh, this requires temporary sort space to rebuild all indexes during refresh. This is because the full refresh truncates or deletes the table before inserting the new full data volume. --> About Types of Refresh for Materialized Views The refresh method can be incremental or a complete refresh. There are two incremental refresh methods [1] log-based refresh. [2] partition change tracking (PCT) refresh. =>> The incremental refresh is commonly called FAST refresh as it usually performs faster than the complete refresh. A complete refresh occurs when the materialized view is initially created when it is defined as BUILD IMMEDIATE. Users can perform a complete refresh at any time after the materialized view is created. The complete refresh involves executing the query that defines the materialized view. This process can be slow, especially if the database must read and process huge amounts of data. An incremental refresh eliminates the need to rebuild materialized views from scratch. Thus, processing only the changes can result in a very fast refresh time. Materialized views can be refreshed either on demand or at regular time intervals. Alternatively, materialized views in the same database as their base tables can be refreshed whenever a transaction commits its changes to the base tables. For materialized views that use the log-based fast refresh method, a materialized view log and/or a direct loader log keep a record of changes to the base tables. A materialized view log is a schema object that records changes to a base table so that a materialized view defined on the base table can be refreshed incrementally. Each materialized view log is associated with a single base table. The materialized view log resides in the same database and schema as its base table. The PCT refresh method can be used if the modified base tables are partitioned and the modified base table partitions can be used to identify the affected partitions or portions of data in the materialized view. When there have been some partition maintenance operations on the base tables, this is the only incremental refresh method that can be used. The PCT refresh removes all data in the affected materialized view partitions or affected portions of data and recomputes them from scratch. --> About Refresh Modes for Materialized Views When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh. In the case of ON COMMIT, the materialized view is changed every time a transaction commits,thus ensuring that the materialized view always contains the latest data. Alternatively, you can control the time when refresh of the materialized views occurs by specifying ON DEMAND. In the case of ON DEMAND materialized views, the refresh can be performed with refresh methods provided in either the DBMS_SYNC_REFRESH or the DBMS_MVIEW packages: The DBMS_SYNC_REFRESH package contains the APIs for synchronous refresh, a new refresh method introduced in Oracle Database 12c, Release 1. Database Data Warehousing Guide -> 8 Synchronous Refresh There are three basic types of refresh operations: complete refresh, fast refresh, and partition change tracking (PCT) refresh. These basic types have been enhanced in Oracle Database 12c, Release 1 with a new refresh option called out-of-place refresh. The DBMS_MVIEW package contains three APIs for performing refresh operations: DBMS_MVIEW.REFRESH -> Refresh one or more materialized views. DBMS_MVIEW.REFRESH_ALL_MVIEWS -> Refresh all materialized views. DBMS_MVIEW.REFRESH_DEPENDENT -> Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views. - We tried other ways to cool the MVIEW - Refresh rates of all dependent table MVIEWS SALES VARIABLE failures NUMBER; EXEC DBMS_MVIEW.REFRESH_DEPENDENT (:failures, 'SALES'); - Refresh rates of all MVIEWS (performed as SYSDBA) EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS (:failures); - We check that is already populated MVIEW SELECT * FROM SH.SALES_MV WHERE ROWNUM <5; ---------------------------------- How to Refresh Materialized Views? ---------------------------------- For each of these refresh options, you have two techniques for how the refresh is performed,namely in-place refresh and out-of-place refresh. The in-place refresh executes the refresh statements directly on the materialized view. The out-of-place refresh creates one or more outside tables and executes the refresh statements on the outside tables and then switches the materialized view or affected materialized view partitions with the outside tables. Both in-place refresh and out-of-place refresh achieve good performance in certain refresh scenarios. However, the out-of-place refresh enables high materialized view availability during refresh, especially when refresh statements take a long time to finish. Also adopting the out-of-place mechanism, a new refresh method called synchronous refresh is introduced in Oracle Database 12c, Release 1. It targets the common usage scenario in the data warehouse where both fact tables and their materialized views are partitioned in the same way or their partitions are related by a functional dependency. The refresh approach enables you to keep a set of tables and the materialized views defined on them to be always in sync. In this refresh method, the user does not directly modify the contents of the base tables but must use the APIs provided by the synchronous refresh package that will apply these changes to the base tables and materialized views at the same time to ensure their consistency. The synchronous refresh method is well-suited for data warehouses, where the loading of incremental data is tightly controlled and occurs at periodic intervals. -------------------------------------- About the Out-of-Place Refresh Option -------------------------------------- Beginning with Oracle Database 12c Release 1, a new refresh option is available to improve materialized view refresh performance and availability. This refresh option is called out-of-place refresh because it uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. The out-of-place refresh option works with all existing refresh methods, such as FAST ('F'), COMPLETE ('C'), PCT ('P'), and FORCE ('?'). Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well. It also enables you to achieve a very high degree of availability because the materialized views that are being refreshed can be used for direct access and query rewrite during the execution of refresh statements. In addition, it helps to avoid potential problems such as materialized view container tables becoming fragmented over time or intermediate refresh results being seen. In out-of-place refresh, the entire or affected portions of a materialized view are computed into one or more outside tables. For partitioned materialized views, if partition level change tracking is possible, and there are local indexes defined on the materialized view, the out-of-place method also builds the same local indexes on the outside tables. This refresh process is completed by either switching between the materialized view and the outside table or partition exchange between the affected partitions and the outside tables. Note that query rewrite is not supported during the switching or partition exchange operation. During refresh, the outside table is populated by direct load, which is efficient. This section contains the following topics: Types of Out-of-Place Refresh -> There are three types of out-of-place refresh: out-of-place fast refresh This offers better availability than in-place fast refresh. It also offers better performance when changes affect a large part of the
materialized view. out-of-place PCT refresh This offers better availability than in-place PCT refresh. There are two
different approaches for partitioned and non-partitioned materialized views.
If truncation and direct load are not feasible,you should use out-of-place
refresh when the changes are relatively large. If truncation and direct load are feasible, in-place refresh is preferable in terms of performance.
In terms of availability,out-of-place refresh is always preferable. out-of-place complete refresh This offers better availability than in-place complete refresh. DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', method => '?',
atomic_refresh => FALSE, out_of_place => TRUE); Restrictions and Considerations with Out-of-Place Refresh [1] Only materialized join views and materialized aggregate views are allowed [2] No ON COMMIT refresh is permitted [3] No remote materialized views, cube materialized views, object materialized
views are permitted [4] No LOB columns are permitted [5] Not permitted if materialized view logs, triggers, or constraints
(except NOT NULL) are defined on the materialized view [6] Not permitted if the materialized view contains the CLUSTERING clause [7] Not applied to complete refresh within a CREATE or ALTER MATERIALIZED VIEW
session or an ALTER TABLE session [8] Atomic mode is not permitted. If you specify atomic_refresh as TRUE and
out_of_place as TRUE, an error is displayed For out-of-place PCT refresh, there is the following restriction: -> No UNION ALL or grouping sets are permitted For out-of-place fast refresh, there are the following restrictions: -> No UNION ALL, grouping sets or outer joins are permitted -> Not allowed for materialized join views when more than one base table
is modified with mixed DML statements Out-of-place refresh requires additional storage for the outside table
and the indexes for the duration of the refresh. Thus, you must have enough
available tablespace or auto extend turned on. The partition exchange in out-of-place PCT refresh impacts the global index
on the materialized view. Therefore, if there are global indexes defined on
the materialized view container table, Oracle disables the global indexes
before doing the partition exchange and rebuild the global indexes after
the partition exchange. This rebuilding is additional overhead. ---------------------------------------------------- Refreshing Specific Materialized Views with REFRESH ---------------------------------------------------- DESC DBMS_MVIEW PROCEDURE REFRESH Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LIST VARCHAR2 IN METHOD VARCHAR2 IN DEFAULT ROLLBACK_SEG VARCHAR2 IN DEFAULT PUSH_DEFERRED_RPC BOOLEAN IN DEFAULT REFRESH_AFTER_ERRORS BOOLEAN IN DEFAULT PURGE_OPTION BINARY_INTEGER IN DEFAULT PARALLELISM BINARY_INTEGER IN DEFAULT HEAP_SIZE BINARY_INTEGER IN DEFAULT ATOMIC_REFRESH BOOLEAN IN DEFAULT NESTED BOOLEAN IN DEFAULT OUT_OF_PLACE BOOLEAN IN DEFAULT DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE, 0,0,0, FALSE, FALSE); DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV', 'CF', '', TRUE, FALSE, 0,0,0, FALSE, FALSE); Note: If the refresh method is not specified, the default refresh method
as specified in the materialized view definition is used. --------------------------------------------------------- Refreshing All Materialized Views with REFRESH_ALL_MVIEWS --------------------------------------------------------- PROCEDURE REFRESH_ALL_MVIEWS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NUMBER_OF_FAILURES BINARY_INTEGER OUT METHOD VARCHAR2 IN DEFAULT ROLLBACK_SEG VARCHAR2 IN DEFAULT REFRESH_AFTER_ERRORS BOOLEAN IN DEFAULT ATOMIC_REFRESH BOOLEAN IN DEFAULT OUT_OF_PLACE BOOLEAN IN DEFAULT DBMS_MVIEW.REFRESH_ALL_MVIEWS(failures,'C','', TRUE, FALSE, FALSE); -------------------------------------------------------------- Refreshing Dependent Materialized Views with REFRESH_DEPENDENT -------------------------------------------------------------- PROCEDURE REFRESH_DEPENDENT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NUMBER_OF_FAILURES BINARY_INTEGER OUT TAB TABLE OF VARCHAR2(227) IN METHOD VARCHAR2 IN DEFAULT ROLLBACK_SEG VARCHAR2 IN DEFAULT REFRESH_AFTER_ERRORS BOOLEAN IN DEFAULT ATOMIC_REFRESH BOOLEAN IN DEFAULT NESTED BOOLEAN IN DEFAULT OUT_OF_PLACE BOOLEAN IN DEFAULT DBMS_MVIEW.REFRESH_DEPENDENT(failures, 'CUSTOMERS', 'C', '', FALSE, FALSE, FALSE); ---------------------------------- About Using Job Queues for Refresh ---------------------------------- Job queues can be used to refresh multiple materialized views in parallel. If queues are not available, fast refresh sequentially refreshes each view
in the foreground process. To make queues available, you must set the JOB_QUEUE_PROCESSES parameter. This parameter defines the number of background job queue processes and determines how many materialized views can be refreshed concurrently. Oracle tries to balance the number of concurrent refreshes with the degree
of parallelism of each refresh. The order in which the materialized views
are refreshed is determined by dependencies imposed by nested materialized
views and potential for efficient refresh by using query rewrite against
other materialized views This parameter is only effective when atomic_refresh is set to FALSE. If the process that is executing DBMS_MVIEW.REFRESH is interrupted or the
instance is shut down,any refresh jobs that were executing in job queue
processes are requeued and continue running.
To remove these jobs, use the DBMS_JOB.REMOVE procedure. ----------------------------- When Fast Refresh is Possible ----------------------------- Not all materialized views may be fast refreshable. Therefore, use the
package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are
available for a materialized view. PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV VARCHAR2 IN STMT_ID VARCHAR2 IN DEFAULT If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure, which provides a script containing the statements required to create a fast refreshable
materialized view. PROCEDURE TUNE_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TASK_NAME VARCHAR2 IN/OUT MV_CREATE_STMT CLOB IN ----------------------------------------------------- Recommended Initialization Parameters for Parallelism ----------------------------------------------------- PARALLEL_MAX_SERVERS -> should be set high enough to take care of parallelism. You must consider
the number of slaves needed for the refresh statement. For example, with
a degree of parallelism of eight, you need 16 slave processes. PGA_AGGREGATE_TARGET -> should be set for the instance to manage the memory usage for sorts and
joins automatically. If the memory parameters are set manually,
SORT_AREA_SIZE should be less than HASH_AREA_SIZE. OPTIMIZER_MODE -> should equal all_rows. Note: Remember to analyze all tables and indexes for better optimization. -------------------- Monitoring a Refresh -------------------- While a job is running, you can query the V$SESSION_LONGOPS view to tell
you the progress of each materialized view being refreshed. SELECT * FROM V$SESSION_LONGOPS; To look at the progress of which jobs are on which queue, use: SELECT * FROM DBA_JOBS_RUNNING; #Confirm the refresh methods for each materialized view by querying USER_MVIEWS SELECT mview_name, refresh_mode, refresh_method,last_refresh_type,
last_refresh_date FROM user_mviews; #Checked for any existing materialized view logs: SELECT log_owner, master, log_table FROM dba_mview_logs; Checking the Status of a Materialized View SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME; Viewing Partition Freshness Several views are available that enable you to verify the status of base
table partitions and determine which ranges of materialized view data are
fresh and which are stale. The views are as follows: *_USER_MVIEWS -> To determine partition change tracking (PCT) information for the materialized view. *_USER_MVIEW_DETAIL_RELATIONS -> To display partition information for the detail table a materialized view is based on. *_USER_MVIEW_DETAIL_PARTITION -> To determine which partitions are fresh. *_USER_MVIEW_DETAIL_SUBPARTITION -> To determine which subpartitions are fresh. Examples of Using Views to Determine Freshness Example 7-1 Verifying the PCT Status of a Materialized View SELECT MVIEW_NAME, NUM_PCT_TABLES, NUM_FRESH_PCT_REGIONS, NUM_STALE_PCT_REGIONS FROM USER_MVIEWS WHERE MVIEW_NAME = MV1; Example 7-2 Verifying the PCT Status in a Materialized View's Detail Table SELECT MVIEW_NAME, DETAILOBJ_NAME, DETAILOBJ_PCT, NUM_FRESH_PCT_PARTITIONS,
NUM_STALE_PCT_PARTITIONS FROM USER_MVIEW_DETAIL_RELATIONS WHERE MVIEW_NAME = MV1; Example 7-3 Verifying Which Partitions are Fresh SELECT MVIEW_NAME,DETAILOBJ_NAME,DETAIL_PARTITION_NAME,DETAIL_PARTITION_POSITION,
FRESHNESS FROM USER_MVIEW_DETAIL_PARTITION WHERE MVIEW_NAME = MV1; Example 7-4 Verifying Which Subpartitions are Fresh SELECT MVIEW_NAME,DETAILOBJ_NAME,DETAIL_PARTITION_NAME, DETAIL_SUBPARTITION_NAME,
DETAIL_SUBPARTITION_POSITION,FRESHNESS FROM USER_MVIEW_DETAIL_SUBPARTITION WHERE MVIEW_NAME = MV1; -------------------------------------- Tips for Refreshing Materialized Views -------------------------------------- [1] Tips for Refreshing Materialized Views with Aggregates For fast refresh, create materialized view logs on all detail tables involved
in a materialized view with the ROWID,SEQUENCE and INCLUDING NEW VALUES clauses. Fast refresh may be possible even if the SEQUENCE option is omitted from
the materialized view log. If it can be determined that only inserts or
deletes will occur on all the detail tables, then the materialized view
log does not require the SEQUENCE clause. However, if updates to multiple
tables are likely or required or if the specific update scenarios are
unknown, make sure the SEQUENCE clause is included. Use Oracle's bulk loader utility or direct-path INSERT (INSERT with the
APPEND hint for loads). Starting in 12c, the database automatically gathers
table statistics as part of a bulk-load operation (CTAS and IAS) similar
to how statistics are gathered when an index is created. By gathering
statistics during the data load, you avoid additional scan operations and
provide the necessary statistics as soon as the data becomes available to
the users. Note that, in the case of an IAS statement, statistics are only
gathered if the table the data is being inserted into is empty. This is a lot more efficient than conventional insert. During loading,
disable all constraints and re-enable when finished loading. Note that
materialized view logs are required regardless of whether you use direct
load or conventional DML. [2] Tips for Refreshing Materialized Views Without Aggregates If a materialized view contains joins but no aggregates, then having an
index on each of the join column rowids in the detail table enhances refresh
performance greatly, because this type of materialized view tends to be much larger than materialized views containing aggregates. CREATE MATERIALIZED VIEW detail_fact_mv BUILD IMMEDIATE AS SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "cust_rid", c.cust_state_province, t.week_ending_day, s.amount_sold FROM sales s, times t, customers c WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id; Indexes should be created on columns sales_rid, times_rid and cust_rid. Partitioning is highly recommended, as is enabling parallel DML in the
session before invoking refresh, because it greatly enhances refresh performance. [3] Tips for Refreshing Nested Materialized Views All underlying objects are treated as ordinary tables when refreshing
materialized views. If the ON COMMIT refresh option is specified, then
all the materialized views are refreshed in the appropriate order at
commit time. If a refresh fails during commit time, the list of materialized views
that has not been refreshed is written to the alert log, and you must
manually refresh them along with all their dependent materialized views. [4] Tips for Fast Refresh with UNION ALL You can use fast refresh for materialized views that use the UNION ALL
operator by providing a maintenance column in the definition of the
materialized view. For example, a materialized view with a UNION ALL
operator can be made fast refreshable as follows: CREATE MATERIALIZED VIEW fast_rf_union_all_mv AS SELECT x.rowid AS r1, y.rowid AS r2, a, b, c, 1 AS marker FROM x, y WHERE x.a = y.b UNION ALL SELECT p.rowid, r.rowid, a, c, d, 2 AS marker FROM p, r WHERE p.a = r.y; The form of a maintenance marker column, column MARKER in the example,
must be numeric_or_string_literal AS column_alias,where each UNION ALL
member has a distinct value for numeric_or_string_literal. [5] Tips for Fast Refresh with Commit SCN-Based Materialized View Logs You can often improve fast refresh performance by ensuring that your
materialized view logs on the base table contain a WITH COMMIT SCN clause,
often significantly. By optimizing materialized view log processing
WITH COMMIT SCN, the fast refresh process can save time. CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold), COMMIT SCN INCLUDING NEW VALUES; The materialized view refresh automatically uses the commit SCN-based
materialized view log to save refresh time. Note that only new materialized view logs can take advantage of COMMIT SCN.
Existing materialized view logs cannot be altered to add COMMIT SCN unless
they are dropped and recreated. [6] Tips After Refreshing Materialized Views After you have performed a load or incremental load and rebuilt the detail
table indexes, you must re-enable integrity constraints (if any) and refresh
the materialized views and materialized view indexes that are derived from
that detail data. In a data warehouse environment, referential integrity constraints are
normally enabled with the NOVALIDATE or RELY options. An important decision to make before performing a refresh operation is
whether the refresh needs to be recoverable. Because materialized view data
is redundant and can always be reconstructed from the detail tables, it might be preferable to disable logging on the materialized view. To disable logging and run incremental refresh non-recoverably,
use the ALTER MATERIALIZED VIEW ... NOLOGGING statement prior to refreshing. If the materialized view is being refreshed using the ON COMMIT method,
then, following refresh operations, consult the alert log alert_SID.log
and the trace file ora_SID_number.trc to check that no errors have occurred. [2] Using Materialized Views with Partitioned Tables A major maintenance component of a data warehouse is synchronizing
(refreshing) the materialized views when the detail data changes. Partitioning the underlying detail tables can reduce the amount of time
taken to perform the refresh task. This is possible because partitioning
enables refresh to use parallel DML to update the materialized view. Also, it enables the use of partition change tracking. =>> Materialized View Fast Refresh with Partition Change Tracking In a data warehouse, changes to the detail tables can often entail
partition maintenance operations, such as DROP, EXCHANGE, MERGE, and
ADD PARTITION. To maintain the materialized view after such operations used to require manual maintenance (see also CONSIDER FRESH) or
complete refresh. You now have the option of using an addition to
fast refresh known as partition change tracking (PCT) refresh. For PCT to be available, the detail tables must be partitioned.
The partitioning of the materialized view itself has no bearing
on this feature. If PCT refresh is possible, it occurs automatically
and no user intervention is required in order for it to occur. The following examples illustrate the use of this feature: PCT Fast Refresh for Materialized Views: Scenario 1 PCT Fast Refresh for Materialized Views: Scenario 2 PCT Fast Refresh for Materialized Views: Scenario 3 ------------------------------- CREATE SIMPLE MATERIALIZED VIEW ------------------------------- [1] Create a simple materialized view and USER_* view to display it #Read Only MATERIALIZED View sqlplus oracle/oracle_4U@pdb1 SQL> create table scott.tbl_objects as select * from dba_objects; Table created. SQL> connect scott/tiger@pdb1 Connected. SQL> SQL> create materialized view mv_objects 2 AS select * from tbl_objects; Materialized view created. SQL> select owner,mview_name,query,updatable from user_mviews OWNER MVIEW_NAME QUERY UP ------------------------------------------------------------------------- SCOTT MV_OBJECTS select * from tbl_objects N [2] Updatable MATERIALIZED View SQL> alter table tbl_objects add primary key(object_id); Table altered. SQL> create materialized view log on tbl_objects; Materialized view log created. SQL> create materialized view mv_objects_uw for update 2 as select * from tbl_objects; Materialized view created. OWNER MVIEW_NAME U ---------------------------------------- SCOTT MV_OBJECTS_UW Y [3] Create a materialized view, make sure all rows are populated after the view is created and make sure the view is truncated every time it is referenced. #Refresh Complete. SQL> create materialized view mv_test01 2 build immediate 3 refresh complete 4 as 5 select * from tbl_extents; Materialized view created. SQL> select count(*) from mv_test01; COUNT(*) ---------- 6498 SQL> insert into tbl_extents 2 select * from tbl_extents; 6498 rows created. SQL> commit; Commit complete. SQL> select count(*) from mv_test01; COUNT(*) ---------- 6498 SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST01'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_test01; COUNT(*) ---------- 12996 [4] FAST REFRESH SENARIOS. 4.1 Create a materialized view and make sure it pull changes made to the master table after last refresh. SQL> create materialized view log on emp with primary key; Materialized view log created. SQL> create materialized view mv_emp 2 refresh fast on demand 3 as 4 select * from emp; Materialized view created. SQL> select count(*) from mv_emp; COUNT(*) ---------- 14 SQL> desc emp Name Null? Type ----------------------------------------------------- -------- ------------------------------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> run 1 BEGIN 2 FOR I IN 1..10 3 LOOP 4 INSERT INTO EMP 5 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 6 VALUES(I,'Hitesh'||I,'DBA'||I,100,SYSDATE,100,0,10); 7 COMMIT; 8 END LOOP; 9* END; PL/SQL procedure successfully completed. SQL> select count(*) from emp; COUNT(*) ---------- 24 SQL> select count(*) from mv_emp; COUNT(*) ---------- 14 SQL> EXEC DBMS_MVIEW.REFRESH('MV_EMP','F'); PL/SQL procedure successfully completed. SQL> select count(*) from mv_emp; COUNT(*) ---------- 24 SQL> 4.2 Create a materialized view log and make sure to purge it every 3 hours. SQL> ALTER MATERIALIZED VIEW LOG ON EMP PURGE REPEAT INTERVAL '3' HOUR Materialized view log altered. 4.3 Create a materialized view and make sure it is updated every time
the master table changes.
SQL> create table table1 2 (ID NUMBER, 3 NAME VARCHAR2(100),PRIMARY KEY(ID)); Table created. SQL> create materialized view log on table1; Materialized view log created. SQL> create materialized view mv_table1 2 refresh fast on commit 3 as 4 select * from table1; SQL> INSERT INTO TABLE1 2 SELECT ROWNUM,'Hitesh'||ROWNUM FROM DUAL CONNECT BY ROWNUM <= 5; 5 rows created. SQL> commit; Commit complete. SQL> SELECT COUNT(*) FROM TABLE1; COUNT(*) ---------- 5 SQL> SELECT COUNT(*) FROM MV_TABLE1; COUNT(*) ---------- 5 [5] Force refresh #Create a materialized view and refresh it manually with parallelism degree 6. SQL> create table scott.tbl_extents as select * from dba_extents; Table created. SQL> connect scott/tiger@pdb1 SQL> create materialized view mv_test02 2 build deferred 3 refresh force on demand 4 as 5 select * from tbl_extents; Materialized view created. SQL> select count(*) from mv_test02; COUNT(*) ---------- 0 SQL> SQL> exec dbms_mview.refresh(list=>'MV_TEST02',parallelism=>6); PL/SQL procedure successfully completed. SQL> select count(*) from mv_test02; COUNT(*) ---------- 12996 [6] Create a materialized view and make sure it is refreshed every 7 minutes. Check the status of the refresh job. Disable auto refresh job. #Automatic refresh periodically. SQL> create materialized view mv_test03 refresh complete start with sysdate next sysdate + '7' minute as select * from tbl_extents; / Materialized view created. SQL> desc dba_jobs Name Null? Type ----------------------------------------- -------- ---------------------------- JOB NOT NULL NUMBER LOG_USER NOT NULL VARCHAR2(128) PRIV_USER NOT NULL VARCHAR2(128) SCHEMA_USER NOT NULL VARCHAR2(128) LAST_DATE DATE LAST_SEC VARCHAR2(8) THIS_DATE DATE THIS_SEC VARCHAR2(8) NEXT_DATE NOT NULL DATE NEXT_SEC VARCHAR2(8) TOTAL_TIME NUMBER BROKEN VARCHAR2(1) INTERVAL NOT NULL VARCHAR2(200) FAILURES NUMBER WHAT VARCHAR2(4000) NLS_ENV VARCHAR2(4000) MISC_ENV RAW(32) INSTANCE NUMBER SQL> select job,LAST_DATE,NEXT_DATE,INTERVAL from dba_jobs; JOB LAST_DATE NEXT_DATE INTERVAL ----------------------------------------------------------------------------------------------- 1 Aug 19 2019 18:05:54 Aug 19 2019 18:12:54 sysdate + interval '7' minute SQL> select count(*) from mv_test03; COUNT(*) ---------- 12996 SQL> exec DBMS_JOB.BROKEN(1,true); PL/SQL procedure successfully completed. SQL> select job,LAST_DATE,NEXT_DATE,INTERVAL,BROKEN from dba_jobs; JOB LAST_DATE NEXT_DATE INTERVAL B ------------------------------------------------------------------------------------------------------- 1 Aug 19 2019 18:10:15 Jan 01 4000 00:00:00 sysdate + interval '7' minute Y SQL> alter materialized view mv_test03 refresh complete next sysdate + 1; Materialized view altered. SQL> select job,LAST_DATE,NEXT_DATE,INTERVAL,BROKEN from dba_jobs; JOB LAST_DATE NEXT_DATE INTERVAL B -------------------------------------------------------------------------------- 1 Aug 19 2019 18:17:12 Aug 20 2019 18:17:59 sysdate + 1 N [7] create two tables based on DBA_OBJECTS and DBA_EXTENS views. create a query to join them and get the execution plan and the time that is used to run the query. Next create a materialized view based on the same query and make sure the query uses the materialized view next time it is executed. Get the execution plan and timing. sqlplus oracle/oracle_4U@pdb1 SQL> create table scott.tbl_objects as select * from dba_objects where object_id is not null; Table created. SQL> create table scott.tbl_extents as select * from dba_extents; Table created. SQL>SET AUTOTRACE TRACEONLY EXP SQL>select a.object_name,count(extent_id) from tbl_objects a, tbl_extents b where a.object_name = b.segment_name group by a.object_name Execution Plan ---------------------------------------------------------- Plan hash value: 1831272611 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3701 | 173K| 448 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 3701 | 173K| 448 (1)| 00:00:01 | |* 2 | HASH JOIN | | 11132 | 521K| 447 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| TBL_EXTENTS | 6522 | 146K| 20 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| TBL_OBJECTS | 91425 | 2232K| 427 (1)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME") SQL> create materialized view log on tbl_objects
with rowid,sequence(object_name) including new values; Materialized view log created. SQL> create materialized view log on tbl_extents
with rowid,sequence(extent_id,segment_name) including new values; Materialized view log created. SQL> create materialized view mv_seg_ext 2 refresh fast with rowid 3 enable query rewrite 4 as 5 select a.object_name,count(extent_id) 6 from tbl_objects a,tbl_extents b 7 where a.object_name = b.segment_name 8 group by a.object_name; Materialized view created. SQL> select a.object_name,count(extent_id) 2 from tbl_objects a,tbl_extents b 3 where a.object_name = b.segment_name 4 group by a.object_name 5 / Execution Plan ---------------------------------------------------------- Plan hash value: 3855756668 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3700 | 88800 | 7 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_SEG_EXT | 3700 | 88800 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------------- SQL> alter session set query_rewrite_enabled=false; Session altered. SQL> select a.object_name,count(extent_id) 2 from tbl_objects a,tbl_extents b 3 where a.object_name = b.segment_name 4 group by a.object_name 5 / Execution Plan ---------------------------------------------------------- Plan hash value: 1831272611 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3701 | 173K| 448 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 3701 | 173K| 448 (1)| 00:00:01 | |* 2 | HASH JOIN | | 11132 | 521K| 447 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| TBL_EXTENTS | 6522 | 146K| 20 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| TBL_OBJECTS | 91425 | 2232K| 427 (1)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME") During exam, if you failed to use query rewrite feature [1] First of all, Check the parameter QUERY_REWRITE_ENABLED parameter and
make sure it is true. [2] Make sure QUERY REWRITE setting is enabled on the materialized view
from USER_MVIEWS as follow.
SELECT mview_name,rewrite_enabled from user_mviews where mview_name = 'MV_SEG_EXT' [3] Moreover,you can use API DBMS_MVIEW.EXPLAIN_REWRITE procedure to learn
why query rewrite failed. [8] Use DBMS_MVIEW package to make sure if the query rewrite will be
enabled for the specific query. SQL> connect scott/tiger@pdb1 Connected. SQL> desc rewrite_table ERROR: ORA-04043: object rewrite_table does not exist SQL> @?/rdbms/admin/utlxrw.sql Table created. SQL> desc rewrite_table Name Null? Type ----------------------------------------- -------- ---------------------------- STATEMENT_ID VARCHAR2(30) MV_OWNER VARCHAR2(128) MV_NAME VARCHAR2(128) SEQUENCE NUMBER(38) QUERY VARCHAR2(4000) QUERY_BLOCK_NO NUMBER(38) REWRITTEN_TXT VARCHAR2(4000) MESSAGE VARCHAR2(512) PASS VARCHAR2(3) MV_IN_MSG VARCHAR2(128) MEASURE_IN_MSG VARCHAR2(30) JOIN_BACK_TBL VARCHAR2(4000) JOIN_BACK_COL VARCHAR2(4000) ORIGINAL_COST NUMBER(38) REWRITTEN_COST NUMBER(38) FLAGS NUMBER(38) RESERVED1 NUMBER(38) RESERVED2 VARCHAR2(10) SQL> alter session set query_rewrite_enabled=false; Session altered. SQL> exec DBMS_MVIEW.EXPLAIN_REWRITE('select a.object_name,count(extent_id) from tbl_objects a,tbl_extents b where a.object_name = b.segment_name group by a.object_name','MV_SEG_EXT','SCOTT'); PL/SQL procedure successfully completed. SQL> select message from rewrite_table order by sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01150: query did not rewrite QSM-01001: query rewrite not enabled SQL> truncate table rewrite_table; Table truncated. SQL> alter session set query_rewrite_enabled=true; Session altered. SQL> exec DBMS_MVIEW.EXPLAIN_REWRITE('select a.object_name,count(extent_id) from tbl_objects a,tbl_extents b where a.object_name = b.segment_name group by a.object_name','MV_SEG_EXT','SCOTT'); PL/SQL procedure successfully completed. SQL> select message from rewrite_table order by sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01151: query was rewritten QSM-01209: query rewritten with materialized view, MV_SEG_EXT, using text match algorithm SQL> [9] Analyzing Materialized Views for Fast Refresh Not all materialized views may be fast refreshable. Therefore, use the
package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are
available for a materialized view. PROCEDURE EXPLAIN_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MV VARCHAR2 IN STMT_ID VARCHAR2 IN DEFAULT #How materialized views can be analyzed and optimized to ensure they can
be FAST REFRESHed. As tools, the DBMS_MVIEW.explain_mview procedure and
the MV_CAPABILITIES_TABLE are used. 9.1 <- Senario SQL> create table tbl_test04 as select * from tbl_objects; Table created. SQL> alter table tbl_test04 add primary key (object_id); Table altered. SQL> create materialized view mv_test04 2 refresh with primary key 3 as 4 select * from tbl_test04; Materialized view created. SQL> select count(*) from mv_test04; COUNT(*) ---------- 91423 SQL> r 1 BEGIN 2 FOR I IN 1..100 3 LOOP 4 INSERT INTO TBL_TEST04 5 (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID) 6 VALUES('SCOTT','SCOTT'||I,'SCOTT'||I,I-555); 7 COMMIT; 8 END LOOP; 9* END; PL/SQL procedure successfully completed. SQL> select count(*) from tbl_test04; COUNT(*) ---------- 91523 SQL> select count(*) from mv_test04; COUNT(*) ---------- 91423 SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_TEST04','SCOTT'); BEGIN DBMS_MVIEW.EXPLAIN_MVIEW('MV_TEST04','SCOTT'); END; * ERROR at line 1: ORA-30377: table SCOTT.MV_CAPABILITIES_TABLE not found ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_XRWMV", line 22 ORA-06512: at "SYS.DBMS_XRWMV", line 42 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3624 ORA-06512: at line 1 SQL> @?/rdbms/admin/utlxmv.sql Table created. SQL> desc MV_CAPABILITIES_TABLE Name Null? Type ----------------------------------------- -------- ---------------------------- STATEMENT_ID VARCHAR2(30) MVOWNER VARCHAR2(30) MVNAME VARCHAR2(30) CAPABILITY_NAME VARCHAR2(30) POSSIBLE CHAR(1) RELATED_TEXT VARCHAR2(2000) RELATED_NUM NUMBER MSGNO NUMBER(38) MSGTXT VARCHAR2(2000) SEQ NUMBER SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_TEST04','SCOTT'); PL/SQL procedure successfully completed. SQL> COMMIT; Commit complete. COL CAPABILITY_NAME FORMAT A15 COL POSSIBLE FORMAT A2 COL RELATED_TEXT FORMAT A15 COL MSGNO FORMAT 99999 COL MSGTXT FORMAT A30 SELECT CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGNO, MSGTXT FROM mv_capabilities_table WHERE statement_id = 'SCOTT'; CAPABILITY_NAME PO RELATED_TEXT MSGNO MSGTXT --------------- -- --------------- ------ ------------------------------ PCT N REFRESH_COMPLET Y E REFRESH_FAST N REWRITE N PCT_TABLE N TBL_TEST04 2068 relation is not a partitioned table REFRESH_FAST_AF N SCOTT.TBL_TEST0 2162 the detail table does not have TER_INSERT 4 a materialized view log REFRESH_FAST_AF N 2146 see the reason why REFRESH_FAS TER_ONETAB_DML T_AFTER_INSERT is disabled REFRESH_FAST_AF N 2161 see the reason why REFRESH_FAS TER_ANY_DML T_AFTER_ONETAB_DML is disabled REFRESH_FAST_PC N 2157 PCT is not possible on any of T the detail tables in the materialized view REWRITE_FULL_TE N 2159 query rewrite is disabled on t XT_MATCH he materialized view REWRITE_PARTIAL N 2159 query rewrite is disabled on t _TEXT_MATCH he materialized view REWRITE_GENERAL N 2159 query rewrite is disabled on t he materialized view REWRITE_PCT N 2158 general rewrite is not possibl e or PCT is not possible on an y of the detail tables PCT_TABLE_REWRI N TBL_TEST04 2068 relation is not a partitioned TE table 14 rows selected. 9.2 <- Senario SQL> truncate table mv_capabilities_table; Table truncated. Elapsed: 00:00:00.06 SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_SEG_EXT','SCOTT'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SQL> COL CAPABILITY_NAME FORMAT A15 COL POSSIBLE FORMAT A2 COL RELATED_TEXT FORMAT A15 COL MSGNO FORMAT 99999 COL MSGTXT FORMAT A30 SELECT CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGNO, MSGTXT FROM mv_capabilities_table WHERE statement_id = 'SCOTT'; SQL> set pagesize 1000 SQL> / CAPABILITY_NAME PO RELATED_TEXT MSGNO MSGTXT --------------- -- --------------- ------ ------------------------------ PCT N REFRESH_COMPLET Y E REFRESH_FAST Y REWRITE Y PCT_TABLE N TBL_OBJECTS 2068 relation is not a partitioned table PCT_TABLE N TBL_EXTENTS 2068 relation is not a partitioned table REFRESH_FAST_AF Y TER_INSERT REFRESH_FAST_AF N 2142 COUNT(*) is not present in the TER_ONETAB_DML select list REFRESH_FAST_AF N 2161 see the reason why REFRESH_FAS TER_ANY_DML T_AFTER_ONETAB_DML is disabled REFRESH_FAST_PC N 2157 PCT is not possible on any of T the detail tables in the mater ialized view REWRITE_FULL_TE Y XT_MATCH REWRITE_PARTIAL Y _TEXT_MATCH REWRITE_GENERAL Y REWRITE_PCT N 2158 general rewrite is not possibl e or PCT is not possible on an y of the detail tables PCT_TABLE_REWRI N TBL_OBJECTS 2068 relation is not a partitioned TE table PCT_TABLE_REWRI N TBL_EXTENTS 2068 relation is not a partitioned TE table 16 rows selected. [10] Use TUNE_MVIEW package and recreate the MV with a different definition
which will allow for fast refreshes.
PROCEDURE TUNE_MVIEW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TASK_NAME VARCHAR2 IN/OUT MV_CREATE_STMT CLOB IN VAR TASK_NAME VARCHAR2(255); SQL> exec DBMS_ADVISOR.TUNE_MVIEW
( :TASK_NAME ,MV_CREATE_STMT=>'create materialized view mv_tune_test as
select object_type,count(*) from tbl_objects group by object_type'); PL/SQL procedure successfully completed. SQL> print :TASK_NAME; TASK_NAME -------------------------------------------------------------------------------- TASK_15 SQL> select * from dba_tune_mview where task_name='TASK_15'; OWNER TASK_NAME ACTION_ID SCRIPT_TYPE STATEMENT -------------------------------------------------------------------------------- SCOTT TASK_15 3 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."TBL_OBJECTS" ADD ROWID, SEQUENCE ("OBJECT_TYPE") INCLUDING NEW VALUES SCOTT TASK_15 4 IMPLEMENTATION CREATE MATERIALIZED VIEW SCOTT.MV_TUNE_TEST REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SCOTT.TBL_OBJECTS.OBJECT_TYPE C1, COUNT(*) M1 FROM SCOTT.TBL_OBJECTS GROUP BYSCOTT.TBL_OBJECTS.OBJECT_TYPE SCOTT TASK_15 5 UNDO DROP MATERIALIZED VIEW SCOTT.MV_TUNE_TEST SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."TBL_OBJECTS"
ADD ROWID, SEQUENCE ("OBJECT_TYPE") INCLUDING NEW VALUES; Materialized view log altered. SQL> CREATE MATERIALIZED VIEW SCOTT.MV_TUNE_TEST
REFRESH FAST WITH ROWID DISABLE QUERY REWRITE
AS SELECT SCOTT.TBL_OBJECTS.OBJECT_TYPE C1, COUNT(*) M1 FROM SCOTT.TBL_OBJECTS GROUP BY SCOTT.TBL_OBJECTS.OBJECT_TYPE
/ Materialized view created. SQL> DROP MATERIALIZED VIEW SCOTT.MV_TUNE_TEST; Materialized view dropped. SQL> DROP MATERIALIZED VIEW LOG ON TBL_OBJECTS; Materialized view log dropped. [11] Using tune_mview and explain_rewrite to enable us to tune mv fast
refreshes and query rewrites
-bash-4.1$ sqlplus sh/oracle_4U@pdb1 create materialized view sales_mv build immediate enable query rewrite as SELECT p.prod_name, SUM(amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id AND prod_name > 'B%' AND prod_name < 'C%' GROUP BY prod_name; SQL> select count(*) from sales_mv; COUNT(*) ---------- 1 SQL> @?/rdbms/admin/utlxrw.sql Table created. SQL> alter session set query_rewrite_enabled=false; Session altered. SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE - ('SELECT p.prod_name, SUM(amount_sold)- FROM sales s, products p - WHERE s.prod_id = p.prod_id - AND prod_name > ''B%''- AND prod_name < ''C%''- GROUP BY prod_name',- 'SALES_MV','SH');> > > > > > > PL/SQL procedure successfully completed. SQL> select message from rewrite_table order by sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01150: query did not rewrite QSM-01001: query rewrite not enabled SQL> SQL> SQL> alter session set query_rewrite_enabled=true; Session altered. SQL> truncate table rewrite_table; Table truncated. SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE - ('SELECT p.prod_name, SUM(amount_sold)- FROM sales s, products p - WHERE s.prod_id = p.prod_id - AND prod_name > ''B%''- AND prod_name < ''C%''- GROUP BY prod_name',- 'SALES_MV','SH');> > > > > > > PL/SQL procedure successfully completed. SQL> select message from rewrite_table order by sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01151: query was rewritten QSM-01209: query rewritten with materialized view, SALES_MV, using text match al gorithm SQL> truncate table rewrite_table; Table truncated. #Let’s now see what happens if we alter our SQL query as shown below. #Query Rewrite does not happen in this case and we can see the reasons why. SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE - ('SELECT p.prod_name, SUM(amount_sold)- FROM sales s, products p - WHERE s.prod_id = p.prod_id - AND prod_name NOT LIKE ''A%'' - GROUP BY prod_name',- 'SALES_MV','SH');> > > > > > PL/SQL procedure successfully completed. SQL> select message from rewrite_table order by sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01150: query did not rewrite QSM-01112: WHERE clause of mv, SALES_MV, is more restrictive than query QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENF ORCED integrity mode #Another package TUNE_MVIEW can also help us in another way by actually
rewriting the # CREATE MATERIALIZED VIEW statement for us to enable things
like Fast Refreshes and also Query Rewrites. Let us suppose we have a
materialized view CUST_MV defined with a fast refresh and we then go an
update some rows on the base table. When we try and refresh fast that MV,
we will see that we are faced with an ORA-32314 error which states that a
Refresh Fast is not supported on this MV after deletes/updates. SQL> create materialized view log on customers
with rowid,sequence(cust_id) including new values; Materialized view log created. SQL> create materialized view log on sales
with rowid,sequence(prod_id,cust_id,amount_sold) including new values; Materialized view log created. SQL> create materialized view mv_cust 2 refresh fast with rowid 3 enable query rewrite 4 as 5 select s.prod_id,s.cust_id,sum(s.amount_sold) sum_amount 6 from sales s , customers cs 7 where s.cust_id = cs.cust_id 8 group by s.prod_id,s.cust_id; Materialized view created. SQL> select count(*) from mv_cust; COUNT(*) ---------- 279954 SQL> update sales set QUANTITY_SOLD=100 where prod_id=13; 6002 rows updated. SQL> commit; Commit complete. SQL> exec dbms_mview.refresh('MV_CUST'); BEGIN dbms_mview.refresh('MV_CUST'); END; * ERROR at line 1: ORA-32314: REFRESH FAST of "SH"."MV_CUST" unsupported after deletes/updates ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017 ORA-06512: at line 1 SQL> VARIABLE task_cust_mv varchar2(30); SQL> VARIABLE create_mv_ddl varchar2(4000); SQL> EXECUTE :task_cust_mv := 'TEST_TUNE_MV'; PL/SQL procedure successfully completed. SQL> EXECUTE :create_mv_ddl := '- CREATE MATERIALIZED VIEW cust_mv- REFRESH FAST- ENABLE QUERY REWRITE AS - SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount- FROM sales s, customers cs- WHERE s.cust_id = cs.cust_id- GROUP BY s.prod_id, s.cust_id';> > > > > > > PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv,:create_mv_ddl); BEGIN DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv,:create_mv_ddl); END; * ERROR at line 1: ORA-13616: The current user SH has not been granted the ADVISOR privilege. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_ADVISOR", line 941 ORA-06512: at "SYS.DBMS_ADVISOR", line 758 ORA-06512: at line 1 SQL> quit -bash-4.1$ sqlplus sys/oracle_4U@pdb1 as sysdba SQL> grant execute on dbms_advisor to sh; Grant succeeded. SQL> grant all on dbms_advisor to sh; Grant succeeded. SQL> grant advisor to sh; Grant succeeded. SQL> create directory DATA_PUMP_DIR_PDB1 AS '/u01/app/oracle/admin/pdb1'; Directory created. SQL> grant all on directory DATA_PUMP_DIR_PDB1 to sh; Grant succeeded. SQL> QUIT -bash-4.1$ sqlplus sh/oracle_4U@pdb1 SQL> VARIABLE task_cust_mv varchar2(30); SQL> EXECUTE :task_cust_mv := 'TEST_TUNE_MV'; PL/SQL procedure successfully completed. SQL> set long 500000 SQL> SELECT STATEMENT FROM USER_TUNE_MVIEW WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION'; 2 STATEMENT -------------------------------------------------------------------------------- CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWI D ENABLE QUERY REWRITE AS SELECT SH.SALE S.CUST_ID C1, SH.SALES.PROD_ID C2, SUM(" SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH "."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SA LES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID SQL> EXECUTE DBMS_ADVISOR.CREATE_FILE
(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv),'DATA_PUMP_DIR_PDB1','create_mv.sql'); PL/SQL procedure successfully completed. SQL> quit -bash-4.1$ cat create_mv.sql Rem SQL Access Advisor: Version 12.1.0.2.0 - Production Rem Rem Username: SH Rem Task: TEST_TUNE_MV Rem Execution date: Rem CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID; -bash-4.1$ sqlplus sh/oracle_4U@pdb1 SQL> set long 100000 SQL> select QUERY from user_mviews where mview_name='MV_CUST'; QUERY -------------------------------------------------------------------------------- select s.prod_id,s.cust_id,sum(s.amount_sold) sum_amount from sales s , customers cs where s.cust_id = cs.cust_id group by s.prod_id,s.cust_id SQL> drop materialized view MV_CUST; Materialized view dropped. SQL> @create_mv.sql Materialized view created. SQL> update sales set QUANTITY_SOLD=10 where prod_id=13; 6002 rows updated. SQL> commit; Commit complete. SQL> exec dbms_mview.refresh('CUST_MV','FAST'); PL/SQL procedure successfully completed. #What has happened to our other MV SALES_MV because we had updated the
base table SALES. Let us see if the query rewrite is still happening. SQL> truncate table rewrite_table; Table truncated. SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE - ('SELECT p.prod_name, SUM(amount_sold)- FROM sales s, products p - WHERE s.prod_id = p.prod_id - AND prod_name > ''B%''- AND prod_name < ''C%''- GROUP BY prod_name',- 'SALES_MV','SH');> > > > > > > PL/SQL procedure successfully completed. SQL> SELECT message FROM rewrite_table ORDER BY sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01150: query did not rewrite QSM-01106: materialized view, SALES_MV, is stale with respect to some partition( s) in the base table(s) QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENF ORCED integrity mode QSM-01029: materialized view, SALES_MV, is stale in ENFORCED integrity mode SQL> exec dbms_mview.refresh('SALES_MV'); PL/SQL procedure successfully completed. SQL> truncate table rewrite_table; Table truncated. SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE - ('SELECT p.prod_name, SUM(amount_sold)- FROM sales s, products p - WHERE s.prod_id = p.prod_id - AND prod_name > ''B%''- AND prod_name < ''C%''- GROUP BY prod_name',- 'SALES_MV','SH');> > > > > > > PL/SQL procedure successfully completed. SQL> SELECT message FROM rewrite_table ORDER BY sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01151: query was rewritten QSM-01209: query rewritten with materialized view, SALES_MV, using text match al gorithm SQL> set autotrace traceonly exp SQL> SELECT p.prod_name, SUM(amount_sold) FROM sales s, products p
WHERE s.prod_id = p.prod_id AND prod_name > 'B%' AND prod_name < 'C%' GROUP BY prod_name; Execution Plan ---------------------------------------------------------- Plan hash value: 1420257564 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 1 | 13 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- SQL> [12] DBMS_REFRESH API In some circumstances we want to have a set of materialized views that are
consistent within a given data for us to consult among them also are consistent
with each other now. For this,the DBMS_REFRESH package, which lets you create
groups of materialized views is used. - Create a pair of materialized views CREATE MATERIALIZED VIEW SALES_G AS SELECT prod_id , CUST_ID , TIME_ID , Channel_id , promo_id , Quantity_Sold , AMOUNT_SOLD FROM SH.SALES ;
CREATE MATERIALIZED VIEW PRODUCTS_G AS SELECT prod_id , PROD_NAME , PROD_DESC FROM SH.PRODUCTS ; - Create the refresh group - Although specify the refresh rate of the MVIEWS , it is not taken into account
because they are ON DEMAND EXEC DBMS_REFRESH.MAKE('TEST_GROUP','SALES_G,PRODUCTS_G',SYSDATE , 'SYSDATE + 1'); - We launched a manual refresh EXEC DBMS_REFRESH.REFRESH ('TEST_GROUP'); - Consulted the date of last refresh SELECT MVIEW_NAME ,TO_CHAR ( LAST_REFRESH_DATE , 'YYYY/MM/DD HH24:MI:SS') from DBA_MVIEWS WHERE MVIEW_NAME IN ('SALES_G','PRODUCTS_G'); - Eliminated the beverages group EXEC DBMS_REFRESH.DESTROY('TEST_GROUP'); - We erased materialized views DROP MATERIALIZED VIEW SALES_G; DROP MATERIALIZED VIEW PRODUCTS_G; [13] Configure and manage distributed materialized views -bash-4.1$ tnsping orcl TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-AUG-2019 10:59:52 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = racnode2.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl.oraclelearn.com))) OK (10 msec) -bash-4.1$ tnsping pdb1 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-AUG-2019 10:59:58 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = racnode2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = pdb1.example.com))) OK (10 msec) -bash-4.1$ -bash-4.1$ sqlplus sh/oracle_4U@pdb1 SQL> create database link ORACLELEARN.COM connect to scott identified by tiger 2 using 'ORCL'; Database link created. SQL> select * from dual@ORACLELEARN.COM; DUM --- X SQL> select count(*) from emp@ORACLELEARN.COM; COUNT(*) ---------- 13 SQL> quit -bash-4.1$ sqlplus scott/tiger@orcl SQL> create materialized view log on emp; Materialized view log created. SQL> create materialized view log on dept; Materialized view log created. SQL> quit -bash-4.1$ sqlplus sh/oracle_4U@pdb1 SQL> create materialized view mv_emp 2 refresh fast 3 as 4 select * from emp@ORACLELEARN.COM; Materialized view created. SQL> create materialized view mv_dept 2 refresh fast 3 as 4 select * from dept@ORACLELEARN.COM; Materialized view created. SQL> select count(*) from mv_emp; COUNT(*) ---------- 13 SQL> select count(*) from mv_dept; COUNT(*) ---------- 4 SQL> EXEC DBMS_MVIEW.REFRESH('MV_EMP','F'); PL/SQL procedure successfully completed. SQL> select mview_name,last_refresh_type from user_mviews where mview_name='MV_EMP'; MVIEW_NAME LAST_REFRESH_TYPE -------------------------------- MV_EMP FAST SQL> BEGIN 2 DBMS_REFRESH.MAKE( 3 NAME => 'REFRESH_GROUP_TEST', 4 LIST => 'MV_EMP,MV_DEPT', 5 NEXT_DATE => SYSDATE, 6 INTERVAL => 'SYSDATE + 1/1440', 7 IMPLICIT_DESTROY => TRUE); 8 END; 9 / PL/SQL procedure successfully completed. SQL> EXEC DBMS_REFRESH.REFRESH('REFRESH_GROUP_TEST'); PL/SQL procedure successfully completed. SQL> EXEC DBMS_REFRESH.DESTROY ( ' REFRESH_GROUP_TEST '); PL/SQL procedure successfully completed. ======================= Patch to Documentation ======================= Database Data Warehousing Guide Part II Optimizing Data Warehouses 5 Basic Materialized Views 6 Advanced Materialized Views 7 Refreshing Materialized Views 8 Synchronous Refresh 10 Basic Query Rewrite for Materialized Views 11 Advanced Query Rewrite for Materialized Views

Thank you for visiting this blog …

Advertisements