Create the Materialized View Syntax:
CREATE MATERIALIZED VIEW [SCHEMA.]MVIEW
MVIEW_OPTIONS
[USING INDEX STORAGE_OPTIONS]
[{REFRESH [REFRESH_OPTIONS] | NEVER REFRESH]
[FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
AS SUBBQUERY;
MVIEW_OPTIONS
Less useful.
Mainly about storage, partitioning, caching.
See below link for full details:
Create Materialized View Syntax
STORAGE_OPTIONS:
PCTFREE int
PCTUSED int
INITRANS int
MAXTRANS int
STORAGE STORAGE_CLAUSE
TABLESPACE TABLESPACE
REFRESH_OPTIONS
[FAST | COMPLETE | FORCE]
ON [DEMAND | COMMIT]
{NEXT | START WITH} DATE
WITH {PRIMARY KEY | ROWID}
USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT
USING {MASTER|LOCAL} ROLLBACK SEGMENT RB_SEGMENT
WITH PRIMARY KEY|ROWID clause
Specify PRIMARY KEY to create a primary key materialized view.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
This is the default and should be used in all cases whenever possible.
ROWID materialized views are useful if the materialized view does not include all primary key columns of the master tables.
If Master Table does not have a Primary Key WITH ROWID is mandatory. Without this Oracle raises error ORA-12014: table MY_MVIEW_SOURCE_TABLE does not contain a primary key constraint.
ON DEMAND|COMMIT clause
ON DEMAND is the default.
It indicates that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures.
ON COMMIT indicates that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view.
This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
Example:
View user Materialized Views
SELECT * FROM USER_SNAPSHOTS
SELECT * FROM USER_MVIEWS;
Run a refresh:
DBMS_REFRESH.refresh ( name IN VARCHAR2);
DBMS_SNAPSHOT.refresh('TABLE_NAME',OPTION)
Where OPTION can be either 'C', 'F', '?','A'
'C' - Complete.
Refresh the whole snapshot.
A complete refresh works by TRUNCATE and then INSERT /*+ APPEND */ the query.
'F' - Fast.
Refresh the delta changes since last refresh.
Per Tom Kyte - "Changes Only" is a more suitable name.
"Fast" refresh works by row by row by row by row by row...
With conventional SQL processing, with redo and logging..
'?' - Force.
'A' - Always refresh.
When handling large amount of changes - Complete is way faster.
When handling small amount of changes - Fast is suitable.See SQL behind the Materialized View
The SQL text is in field query, which in of type LONG.
SET LONG 2000;
SELECT owner, mview_name, query
FROM USER_MVIEWS
WHERE mview_name = 'SOME_VIEW';
Create Materialized View
CREATE MATERIALIZED VIEW MY_VIEW
START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+1/24
WITH PRIMARY KEY
AS SELECT * FROM BILLING_TAGS@CGW_NEW;
Refresh Snapshot one by one.
DBMS_SNAPSHOT.REFRESH('MY_SNAPSHOT','C');
=============================
Refresh Groups
=============================
It might be needed to sync data from several Materialized Views, so they are Refreshed in All or None way
The way to do it, is to group the Materialized Views into a Refresh Group, and run a refresh on the Refresh Group level.
DBMS_REFRESH reference
MVIEW_OPTIONS
[USING INDEX STORAGE_OPTIONS]
[{REFRESH [REFRESH_OPTIONS] | NEVER REFRESH]
[FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
AS SUBBQUERY;
MVIEW_OPTIONS
Less useful.
Mainly about storage, partitioning, caching.
See below link for full details:
Create Materialized View Syntax
STORAGE_OPTIONS:
PCTFREE int
PCTUSED int
INITRANS int
MAXTRANS int
STORAGE STORAGE_CLAUSE
TABLESPACE TABLESPACE
REFRESH_OPTIONS
[FAST | COMPLETE | FORCE]
ON [DEMAND | COMMIT]
{NEXT | START WITH} DATE
WITH {PRIMARY KEY | ROWID}
USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT
USING {MASTER|LOCAL} ROLLBACK SEGMENT RB_SEGMENT
WITH PRIMARY KEY|ROWID clause
Specify PRIMARY KEY to create a primary key materialized view.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
This is the default and should be used in all cases whenever possible.
ROWID materialized views are useful if the materialized view does not include all primary key columns of the master tables.
If Master Table does not have a Primary Key WITH ROWID is mandatory. Without this Oracle raises error ORA-12014: table MY_MVIEW_SOURCE_TABLE does not contain a primary key constraint.
ON DEMAND|COMMIT clause
ON DEMAND is the default.
It indicates that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures.
ON COMMIT indicates that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view.
This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
Example:
View user Materialized Views
SELECT * FROM USER_SNAPSHOTS
SELECT * FROM USER_MVIEWS;
Run a refresh:
DBMS_REFRESH.refresh ( name IN VARCHAR2);
DBMS_SNAPSHOT.refresh('TABLE_NAME',OPTION)
Where OPTION can be either 'C', 'F', '?','A'
'C' - Complete.
Refresh the whole snapshot.
A complete refresh works by TRUNCATE and then INSERT /*+ APPEND */ the query.
'F' - Fast.
Refresh the delta changes since last refresh.
Per Tom Kyte - "Changes Only" is a more suitable name.
"Fast" refresh works by row by row by row by row by row...
With conventional SQL processing, with redo and logging..
'A' - Always refresh.
When handling large amount of changes - Complete is way faster.
When handling small amount of changes - Fast is suitable.See SQL behind the Materialized View
The SQL text is in field query, which in of type LONG.
SET LONG 2000;
SELECT owner, mview_name, query
FROM USER_MVIEWS
WHERE mview_name = 'SOME_VIEW';
Create Materialized View
CREATE MATERIALIZED VIEW MY_VIEW
REFRESH COMPLETE
ON DEMAND START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+1/24
WITH PRIMARY KEY
AS SELECT * FROM BILLING_TAGS@CGW_NEW;
The Materialized Views could be refreshed one by one, or together, as a group.
DBMS_SNAPSHOT.REFRESH('MY_SNAPSHOT','C');
=============================
Refresh Groups
=============================
It might be needed to sync data from several Materialized Views, so they are Refreshed in All or None way
The way to do it, is to group the Materialized Views into a Refresh Group, and run a refresh on the Refresh Group level.
DBMS_REFRESH reference
Step 1. - Create Refresh Group
BEGIN
DBMS_REFRESH.MAKE
(NAME=>'MY_SCHEMA.MY_REFRESH_GROUP',
(NAME=>'MY_SCHEMA.MY_REFRESH_GROUP',
LIST=>'',
NEXT_DATE => TRUNC(SYSDATE+1)+1/24,
INTERVAL => 'TRUNC(SYSDATE+1)+1',
IMPLICIT_DESTROY => FALSE,
ROLLBACK_SEG => '',
PUSH_DEFERRED_RPC => FALSE,
REFRESH_AFTER_ERRORS => FALSE
);
commit;
commit;
END;
/
NOTE!!!
- NEXT_DATE is defined as DATE - NOT to be used with with ''.
- INTERVAL is defined as VARCHAR2 - need to use with ''.
NOTE!!!
- NEXT_DATE is defined as DATE - NOT to be used with with ''.
- INTERVAL is defined as VARCHAR2 - need to use with ''.
Step 2. - Add Materialized Views to the Refresh Group
BEGIN
DBMS_REFRESH.ADD(name=>'MY_SCHEMA.MY_REFRESH_GROUP',
list=>'MY_MVIEW_A',
LAX=>FALSE
);
COMMIT;
COMMIT;
END;
/
BEGIN
DBMS_REFRESH.ADD(name=>'MY_SCHEMA.MY_REFRESH_GROUP',
list=>'MY_MVIEW_B',
LAX=>FALSE
);
COMMIT;
COMMIT;
END;
/
NOTE the COMMIT!!! Without the COMMIT it is like an open transaction, causing locks when running DBMS_REFRESH package!!!
3. Query tables to see current status of Materialized View
To Query the Created snaphot Group and Children:Query existing Snapshot Group:
SELECT *
FROM USER_REFRESH
WHERE rname='MY_SCHEMA.MY_REFRESH_GROUP';
Query existing Snapshot Group Children:
SELECT *
FROM USER_REFRESH_CHILDREN
WHERE rname='REFRESH_GROUP_NAME';
Example of dropping a materialized view:
DROP MATERIALIZED VIEW ACTUAL_NETWORKS_VW
Example of creating a materialized view:
CREATE MATERIALIZED VIEW ACTUAL_NETWORKS_VW
REFRESH COMPLETE
START WITH sysdate
NEXT TRUNC(SYSDATE+1)
WITH PRIMARY KEY
AS
SELECT /*+ index(f TS_LAST_MODIFIED_IDX) */
A.dyn1 network_id,
AN.sub_network_id sub_network_id
FROM NETWORK_COUNTERS A ,
NETWORKS AN
WHERE 1=1
AND A.dyn1 = AN.network_id
========================
Get Materialied View DDL
========================
set long 200000
SET pages 0
SET lines 140
SET doc off
column txt format a140 word_wrapped
spool mview_sql.sql
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MY_MVIEW','MY_USER') mview_sql from dual;
spool off
========================
Check when Materialized View is refreshed
========================
SELECT rowner, rname, interval, next_date
FROM USER_REFRESH
WHERE rname = 'MV_NAME'
In oracle 12.2
SELECT owner, name, last_refresh
FROM USER_MVIEW_REFRESH_TIMES
WHERE name = 'MV_NAME';
SELECT rowner, rname, interval, last_refresh, next_date
FROM USER_REFRESH UR,
USER_MVIEW_REFRESH_TIMES UMVT
WHERE UR.rname = 'GSM_ROAMING_INFO_MV'
AND UMVT.name = UR.rname;
In oracle 19.0
In oracle 19, the field USER_MVIEW_REFRESH_TIMES.next_date is not refreshed
To see next_date, need to query USER_SCHEDULER_JOBS
SELECT repeat_interval, next_run_date, job_action
FROM USER_SCHEDULER_JOBS WHERE job_name IN
(SELECT job_name FROM USER_REFRESH
WHERE rname='GSM_ROAMING_INFO_MV');
To see refresh time, regardless of the version
CREATE OR REPLACE FUNCTION get_mv_refresh_time ( p_mview_name IN VARCHAR2 ) RETURN DATE IS
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_ret_value DATE;
v_last_run DATE;
v_orig_interval VARCHAR2(100);
v_interval VARCHAR2(100);
v_interval_num VARCHAR2(100);
BEGIN
v_module_name := 'get_mv_refresh_time';
SELECT last_refresh INTO v_last_run
FROM USER_MVIEW_REFRESH_TIMES
WHERE name = p_mview_name
AND owner = (SELECT user from DUAL);
SELECT interval INTO v_orig_interval
FROM USER_REFRESH
WHERE rname = p_mview_name
AND rowner = (SELECT user from DUAL);
v_interval := REPLACE(REPLACE(REPLACE(v_orig_interval,'SYSDATE',''),'+',''),' ''');
SGA_PKG.write_sga_w_log(v_module_name,'interval:'||v_interval);
EXECUTE IMMEDIATE 'SELECT TO_NUMBER('||v_interval||') FROM DUAL' INTO v_interval_num;
v_ret_value := v_last_run + v_interval_num;
RETURN v_ret_value;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Unexpected Error: '||SQLERRM;
SGA_PKG.write_sga_w_log(v_module_name,v_msg_text);
RETURN NULL;
END get_mv_refresh_time;
No comments:
Post a Comment