ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MV--MATERIALIZED VIEWS(>8i) or SNAPSHOTS(

MV--MATERIALIZED VIEWS(>8i) or SNAPSHOTS(

原创 Linux操作系统 作者:vongates 时间:2019-07-17 17:57:02 0 删除 编辑
Oracle Notes : MATERIALIZED VIEWS - SNAPSHOT

- $ORACLE_HOME/rdbms/admin/catsnap.sql & dbmssnap.sql

- dbms_mview, dbms_olap

, dbms_job

- data dict:

   DBA_MVIEWS, DBA_SNAPSHOTS, DBA_SNAPSHOT_LOGS, DBA_REGISTERED_SNAPSHOTS,

   DBA_REFRESH, DBA_RGROUP,

DBA_JOBS

- errors to alert log and trace files with ORA-12012

- for automatic mview refresh job_queue_processes must be > 0

- snapshots are automatically registered at the master site:

   use sys.dba_registered_snapshots at master site to see registered snapshots

- grant create materialized view to ;

- mview types: primary key, rowid, subquery

- refresh types:

  - fast (incremental):

    - needs mview log

    - if mview is based on multiple tables, each table should have a mview log

    - exec dbms_mview.refresh('', 'F');

  - complete:

    - fkeys referencing the mview must be disabled before refresh

    - if snapshot log:  exec dbms_mview.purge_log('', 1);

    - exec dbms_mview.refresh('', 'C');

- snapshot logs - materialized view log:

  - always create snap log before snapshot

  - mlog$_  prefixes

  - each log associated with a single master table

  - each log can support multiple snapshots on the master table

- export/import:

  snapshots and snap logs are exported with schema name explicitly set in DDL,

  therefore imports cannot be performed into different schemas.

  Even fromuser/touser will fail.

- primary key mview:

  - mview log:

     create materialized view log on .

     tablespace snap_log

     storage (initial 500k next 500k pctincrease 0)

     with primary key;

  - mview:

     create materialized view .

     tablespace snap_data

     storage (initial 500k next 500k pctincrease 0)

     using index

       tablespace snap_index

       storage (initial 500k next 500k pctincrease 0)

     refresh fast on demand as

     select * from .

@;

- rowid mview:

  - mview log:
     create materialized view log on customer
     tablespace snap_log 
     nologging
     storage (initial 50k next 50k pctincrease 0) with rowid;
  - mview
     create materialized view customer
     tablespace snap_data
     storage (initial 100k next 100k pctincrease 0)
     refresh fast on demand with rowid as
     select * from customer@proddb;

- refresh groups:

  - mviews in group are refreshed at the same time to ensure

     transactionally consistent data

  - mview group refreshes done via delete/re-insert (instead of truncate/re-insert)

     so other users will not see a gap where the mview is empty

  - there must always be 2 mviews in a refresh group. So a dummy mview must be created

     if the group exists just to avoid the "no data found" gap during refresh.

  - exec dbms_refresh.make(name => 'events_group',

                           list => 'events_mv,events_dual_mv',

                           next_date => sysdate,

                           interval => 'sysdate + (30/1440)',

                           implicit_destroy => FALSE);

  - removing a refresh group

     exec dbms_refresh.destroy('');
- steps for rebuilds after schema changes:

  1. TARGET: find grantee's granted table privileges on

  2. TARGET: drop materialized view

  3. SOURCE: drop materialized view log on

  4. SOURCE: create materialized view log on

  5. TARGET: create materialized view

  6. TARGET: grant select on to

- updateable snapshots:

  CREATE SNAPSHOT FOR UPDATE AS SELECT * FROM @;

- query rewrite:

  - disabled by default, must enable with ... enable query rewrite ...

  - init parameters:

     query_rewrite_enabled = true

     query_rewrite_integrity = trusted

     job_queue_processes = >= 1

     job_queue_interval = ?

     optimizer_mode = choose, first_rows, or all_rows

     compatible = >= 8.1

  - grants: grant rewrite;  OR  grant global rewrite;

- dbms_olap: mview analysis package

  - estimate mview size

  - recommend mviews

  - report on mview usage

- dimensions: hierarchial info about data

- Oracle7 to Oracle8 problem: ORA-12028

   create snapshots with 'refresh complete with rowid'

reference

===============

http://www.mabiii.com/notes_oracle.htm

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51662/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论