oracle materialized view - ghdrako/doc_snipets GitHub Wiki

CREATE  MATERIALIZED VIEW MV_DINW8 
TABLESPACE ESPA_MV 
as select * from v_dinw8;

BEGIN
DBMS_SNAPSHOT.REFRESH('Name here');
END;

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_MY_VIEW');
EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_MY_VIEW',PARALLELISM=>4);

TRUNCATE TABLE <mv_name>;
 
DROP MATERIALIZED VIEW <mv_name>;
 
CREATE MATERIALIZED VIEW <mv_name> 
  TABLESPACE ESPA_MV 
  BUILD IMMEDIATE
  AS select /*+ parallel(4) */ * from <view_name>;

analyze table <mv_name>  estimate statistics;

dbms_mview.refresh('odsetki_mv');

alter table <materialized_view> nologging;

select 'alter table '||owner||'.'||table_name||' nologging;',logging from all_tables where table_name like '%MV%' and logging = 'YES' and owner not in ('SYSTEM','SYS') order by owner;

Originally called snapshots, materialized views were introduced in Oracle8i and are only available in the Enterprise Edition. Materialized views without query rewrite are supported in Standard editions.

From a database perspective, materialized views are treated like tables:

  • You can perform most DML and query commands such as insert, delete, update, and select. ??????
  • They can be partitioned.
  • They can be compressed.
  • They can be parallelized.
  • You can create indexes on them.

Query Rewrite - feature only in Enterprise Edition

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_MY_VIEW',PARALLELISM=>4);

Oracle 10g introduced the atomic refresh mechanism

When "atomic refresh" is set to TRUE (in dbms_mview.refresh_all_mviews), than the whole refresh is done in a single transaction.
SQL> exec dbms_mview.refresh('MY_MV',atomic_refresh=>TRUE);
If you do not specify an atomic refresh (by setting "atomic refresh = FALSE" in dbms_mview.refresh_all_mviews) then you can optimize the materialized view refresh with these mechanisms:

  • Parallel DML
  • Truncate DDL

In other words, setting atomic_refresh=false tells Oracle to truncate data instead of delete the rows, resulting in better performance than setting "atomic_refresh=true".

You can use the DBMS_MVIEW package to manually invoke either a fast refresh or a complete refresh, where F equals Fast Refresh and C equals Complete Refresh:

EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F');

https://docs.oracle.com/database/121/ARPLS/d_mview.htm#ARPLS67211

The DBMS_MVIEW package provides three types of refresh operations:

  • DBMS_MVIEW.REFRESH: Refreshes one or more Oracle materialized views
  • DBMS_MVIEW.REFRESH_ALL_MVIEWS: Refreshes all Oracle materialized views
  • DBMS_MVIEW.REFRESH_DEPENDENT: Refreshes all table-based Oracle materialized views

DBMS_MVIEW enables you to understand capabilities for materialized views and potential materialized views, including their rewrite availability. It also enables you to refresh materialized views that are not part of the same refresh group and purge logs.

REFRESH Procedures

This procedure refreshes a list of materialized views.

Syntax
DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}    #Comma-delimited list of materialized views that you want to refresh
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true,
   nested                 IN     BOOLEAN        := false,
   out_of_place           IN     BOOLEAN        := false);

atomic_refresh

If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated. If this parameter is set to false, then each of the materialized views is refreshed non-atomically in separate transactions. As part of complete refresh, if truncate is used (non-atomic refresh), unique index rebuild is executed. INDEX REBUILD automatically computes statistics. Thus, statistics are updated for truncated tables.

EXEC DBMS_MVIEW.REFRESH(LIST => 'SYSTEM.MV_MW', METHOD => 'C', ATOMIC_REFRESH => FALSE,PARALLELISM=>4);
⚠️ **GitHub.com Fallback** ⚠️