详细介绍 Oracle中的Materialized Views（物化视图/快照）

最新推荐文章于 2024-05-23 06:59:48 发布

DB架构

最新推荐文章于 2024-05-23 06:59:48 发布

阅读量1.3k

点赞数

分类专栏： DBA 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/u011868279/article/details/128064123

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

A materialized view (snapshot) is a table segment whose contents are periodically refereshed based on query (against a local or remote table)(针对的本地或者远程表)

The simplest way to achieve replication of data between sites for against remote tables:

materiralized views

Basic Syntax

create materialized view

-- Normal
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

The build clause options are shown below :

IMMEDIATE: mv is populated immediately.
DEFFERED: mv is populated on the first requested refresh.

The following refresh type are avaliable:

FAST : mv logs are not present for source tables in
COMPLETE
FORCE

A fresh can be triggered :

ON COMMIT: data change in one of the dependent tables be committed
ON DEMAND: manual request or a schedule task

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations.

The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

Check Privileges

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 15:18:35 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user;
USER is "SYS"
SQL> 
SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SQL> grant create materialized view to scott;

Grant succeeded.

SQL> grant create database link to scott;

Grant succeeded.

SQL> 

SQL> conn scott/tiger@PDB1
Connected.
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SET CONTAINER

13 rows selected.

SQL>

Create DBLINK

SQL> show user
USER is "SYS"
SQL> 
SQL> grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to scott;

Grant succeeded.

SQL> column owner for a40
SQL> column object_name for a40
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                                    OBJECT_NAME
---------------------------------------- ----------------------------------------
SYS                                      SYS_HUB

SQL> COLUMN owner FORMAT A30
SQL> COLUMN db_link FORMAT A30
SQL> COLUMN username FORMAT A30
SQL> COLUMN host FORMAT A30
SQL> select owner,db_link,username,host from dba_db_links order by owner,db_link;

OWNER                          DB_LINK                        USERNAME                       HOST
------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                            SYS_HUB                                                       SEEDDATA

SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            SYS_HUB

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> 
SQL> create public database link LINK_ORCLPDB1
  2  connect to SCOTT identified by "TIGER"
  3  using
  4  '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLPDB1)))';

Database link created.

SQL> 



SQL> show user
USER is "SYS"
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            SYS_HUB
PUBLIC                         LINK_ORCLPDB1

SQL> 

SQL> show user
USER is "SCOTT"
SQL> 
SQL> select * from SCOTT.emp@LINK_ORCLPDB1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

Create Materialized View

SQL> show user;
USER is "SCOTT"
SQL> 
SQL> CREATE MATERIALIZED VIEW emp_mv
  2  BUILD IMMEDIATE
  3  REFRESH FORCE
  4  ON DEMAND
  5  AS
  6  SELECT * FROM emp@link_orclpdb1;

Materialized view created.

SQL>

Remember to gather stats after building the materialized view.

SQL> BEGIN
  2    DBMS_STATS.gather_table_stats(
  3      ownname => 'SCOTT',
  4      tabname => 'EMP_MV');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

Create Materialized View Logs

Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view.

To take advantage of the of the fast refresh, connect to the master instance and create the materialized view log.

Code:

CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Options:

[oracle@MaxwellDBA ~]$ sqlplus SCOTT/TIGER@ORCLPDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 16:14:53 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 18 2022 19:37:15 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

scott@orclpdb1:orclcdb> show user
USER is "SCOTT"
scott@orclpdb1:orclcdb> CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP
  2  TABLESPACE users
WITH PRIMARY KEY
  4  INCLUDING NEW VALUES;

Materialized view log created.

scott@orclpdb1:orclcdb>

Refresh Materialized Views

If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.

For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.

The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.

Code :

BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

BEGIN
   DBMS_REFRESH.add(
     name => 'SCOTT.MINUTE_REFRESH',
     list => 'SCOTT.EMP_MV',
     lax  => TRUE);
END;
/

Options:

[oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 16:56:15 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 27 2022 16:50:40 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> BEGIN
  2     DBMS_REFRESH.make(
  3       name                 => 'SCOTT.MINUTE_REFRESH',
  4       list                 => '',
  5       next_date            => SYSDATE,
  6       interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
  7       implicit_destroy     => FALSE,
  8       lax                  => FALSE,
  9       job                  => 0,
 10       rollback_seg         => NULL,
 11       push_deferred_rpc    => TRUE,
 12       refresh_after_errors => TRUE,
 13       purge_option         => NULL,
 14       parallelism          => NULL,
 15       heap_size            => NULL);
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REFRESH.add(
  3       name => 'SCOTT.MINUTE_REFRESH',
  4       list => 'SCOTT.EMP_MV',
  5       lax  => TRUE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

Information about refresh groups and the materialize views in a refresh group can be queried from the DBA_RGROUP and DBA_RCHILD views respectively.

SQL> column owner for a20
SQL> column name for a20
SQL> column ROLLBACK_SEG for a20
SQL> column JOB_NAME for a20
SQL> select * from dba_rgroup;

  REFGROUP OWNER                NAME                 I P R ROLLBACK_SEG                JOB PURGE_OPTION PARALLELISM  HEAP_SIZE JOB_NAME
---------- -------------------- -------------------- - - - -------------------- ---------- ------------ ----------- ---------- --------------------
         5 SCOTT                MINUTE_REFRESH       N Y Y                               0                                     MV_RF$J_0_S_130

SQL> desc dba_rchild;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 REFGROUP                                                                                                                   NUMBER
 OWNER                                                                                                             NOT NULL VARCHAR2(128)
 NAME                                                                                                              NOT NULL VARCHAR2(128)
 TYPE#                                                                                                                      VARCHAR2(128)

SQL> column owner for a20
SQL> column name for a20
SQL> column REFGROUP for a20
SQL> column TYPE for a20
SQL> select * from dba_rchild;

  REFGROUP OWNER                NAME                 TYPE#
---------- -------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------
########## SCOTT                EMP_MV               SNAPSHOT

SQL> column REFGROUP default
SQL> select * from dba_rchild;

  REFGROUP OWNER                NAME                 TYPE#
---------- -------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------
         5 SCOTT                EMP_MV               SNAPSHOT

SQL>

befor 1 minutes & after 1 minutes;

SQL> select * from emp_mv;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select * from emp_mv;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        400                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1200        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81        850        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2575                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81        850       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2450                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2050                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       2600                    20
      7839 KING       PRESIDENT            17-NOV-81       4600                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1100          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87        700                    20
      7900 JAMES      CLERK           7698 03-DEC-81        550                    30
      7902 FORD       ANALYST         7566 03-DEC-81       2600                    20
      7934 MILLER     CLERK           7782 23-JAN-82        900                    10

14 rows selected.

SQL> 
SQL>

A materialized view can be manually refreshed using the DBMS_MVIEW package.

Code:

EXEC DBMS_MVIEW.refresh('EMP_MV');

Options:

SQL> select * from emp_mv;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

14 rows selected.

SQL>
SQL> EXEC DBMS_MVIEW.refresh('EMP_MV');

PL/SQL procedure successfully completed.

SQL> select * from emp_mv;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2350                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1500        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1150        500         30
      7788 SCOTT      ANALYST         7566 19-APR-87       2900                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1200                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1150       1400         30
      7902 FORD       ANALYST         7566 03-DEC-81       2900                    20
      7839 KING       PRESIDENT            17-NOV-81       4900                    10
      7369 SMITH      CLERK           7902 17-DEC-80        700                    20
      7566 JONES      MANAGER         7839 02-APR-81       2875                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1400          0         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2750                    30
      7900 JAMES      CLERK           7698 03-DEC-81        850                    30

14 rows selected.

SQL> show user;
USER is "SCOTT"
SQL>

Cleaning Up

code:

sqlplus scott/tiger@PDB1
DROP MATERIALIZED VIEW emp_mv;
DROP PUBLIC DATABASE LINK LINK_ORCLPDB1;

BEGIN
 DBMS_REFRESH.destroy(name=>'SCOTT.MINUTE_REFRESH'); 
END;
/

sqlplus SCOTT/TIGER@ORCLPDB1

DROP MATERIALIZED VIEW LOG ON SCOTT.EMP;

options:

[oracle@oracle-db-19c ~]$ sqlplus scott/tiger@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 17:27:17 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 27 2022 17:07:11 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> DROP MATERIALIZED VIEW emp_mv;

Materialized view dropped.

SQL> 

SQL> DROP PUBLIC DATABASE LINK LINK_ORCLPDB1;

Database link dropped.

SQL> BEGIN
  2   DBMS_REFRESH.destroy(name=>'SCOTT.MINUTE_REFRESH'); 
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> 


[oracle@MaxwellDBA ~]$ 
[oracle@MaxwellDBA ~]$ sqlplus SCOTT/TIGER@ORCLPDB1;

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 27 17:30:30 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 27 2022 17:27:27 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> DROP MATERIALIZED VIEW LOG ON SCOTT.EMP;

Materialized view log dropped.

scott@orclpdb1:orclcdb>

Aggregations and Transformations

Materialized views can be used to improve the performance of a variety of queries, including those performing aggregations and transformations of the data. This allows the work to be done once and used repeatedly by multiple sessions, reducing the total load on the server.

The following query does an aggregation of the data in the EMP table.

Code:

show user;
set autotrace trace explain

select deptno,sum(sal)
from emp
group by deptno;

Options:

SQL> show user;
USER is "SCOTT"
SQL> 
SQL> set autotrace trace explain
SQL> 
SQL> select deptno,sum(sal)
  2  from emp
  3  group by deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

Create a materialized view to perform the aggregation in advance, making sure you specify the ENABLE QUERY REWRITE clause.



SQL> show user;
USER is "SCOTT"
SQL> CREATE MATERIALIZED VIEW emp_aggr_mv
  2  BUILD IMMEDIATE 
  3  REFRESH FORCE
  4  ON DEMAND
  5  ENABLE QUERY REWRITE 
  6  AS
  7  SELECT deptno, SUM(sal) AS sal_by_dept
  8  FROM   emp
  9  GROUP BY deptno;

Materialized view created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

PL/SQL procedure successfully completed.

SQL>

The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.

Code:

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

Options:

SQL> set pagesize 500
SQL> set linesize 500
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT deptno, SUM(sal)
  2  FROM   emp
  3  GROUP BY deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2456459487

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    21 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV |     3 |    21 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL>