通过连接视图和表来实现sql的物化视图

首先，不幸的是，对于oracle物化视图不允许使用ansi连接语法，请使用旧的oracle连接语法。我相信这是甲骨文的一个错误
任何我要用我的答案来证明的东西，都只是关于物质化的 FAST REFRESH 选项
回到您最初的问题，我们是否可以在具有增量刷新功能的物化视图中使用普通视图：
答案是否定的
话虽如此，如果我们尝试将以错误告终，并且无法创建它，我将在下面演示，
表结构：（仅用于演示示例，不用于实际的规范化表

CREATE TABLE emp(emp_id NUMBER primary key
                ,empname VARCHAR2(1000));
CREATE TABLE address_details(address_id NUMBER primary key
                            ,address_text VARCHAR2(1000)
                            ,emp_id NUMBER);
CREATE TABLE salary_details(sal_id NUMBER primary key
                           ,salary NUMBER
                           ,emp_id NUMBER);

物化视图日志：

--drop statements
DROP MATERIALIZED VIEW LOG ON emp;
DROP MATERIALIZED VIEW LOG ON address_details;
DROP MATERIALIZED VIEW LOG ON salary_details;

--create statements

--default
CREATE MATERIALIZED VIEW LOG ON emp;
CREATE MATERIALIZED VIEW LOG ON address_details;
CREATE MATERIALIZED VIEW LOG ON salary_details;

--with primary key (same as default above but I would stick to mention it explicitly for understanding and versioning (svn or git) purpose
CREATE MATERIALIZED VIEW LOG ON emp WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON address_details WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON salary_details WITH PRIMARY KEY;

--with primary key and rowid
CREATE MATERIALIZED VIEW log ON emp WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW log ON address_details WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW log ON salary_details WITH PRIMARY KEY, ROWID;

1.首先尝试使用普通视图创建mv：

CREATE OR REPLACE VIEW DETAILS_VIEW AS
SELECT sal_id
      ,salary
      ,address_id
      ,address_text
      ,sl.emp_id
      --,sl.rowid sl_rowid
      --,ad.rowid ad_rowid
FROM   salary_details sl
      ,address_details ad
WHERE  sl.emp_id = ad.emp_id;

DROP MATERIALIZED VIEW emp_mview;

CREATE MATERIALIZED VIEW EMP_MVIEW 
REFRESH FORCE ON COMMIT 
AS
SELECT emp.emp_id
      ,emp.empname
      ,dv.sal_id
      ,dv.salary
      ,dv.address_id
      ,dv.address_text
      --,emp.rowid emp_rowid
      --,dv.sl_rowid
      --,dv.ad_rowid
FROM   emp           emp
      ,details_view  dv
WHERE  emp.emp_id = dv.emp_id(+);

结果：

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

相信我，我在所有场景中都尝试过使用主键和rowid创建日志，并将rowid添加到select子句中，但没有任何效果
前进：
2.第二次尝试通过将所有单个基表放在from子句中，使用oracle的标准联接创建mv：
我已经用option primary key创建了物化视图日志，正如我在上面的物化视图日志部分中提到的那样。

CREATE MATERIALIZED VIEW EMP_MVIEW 
REFRESH FORCE ON COMMIT 
AS
SELECT emp.emp_id
      ,emp.empname
      ,sl.sal_id
      ,sl.salary
      ,ad.address_id
      ,ad.address_text
FROM   emp             emp
      ,salary_details  sl
      ,address_details ad
WHERE  emp.emp_id = sl.emp_id(+)
AND    emp.emp_id = ad.emp_id(+);

答对了： Materialized view created ，但是，等等。。。是不是说现在我可以 FAST REFRESH ? 让我们检查一下：
我们可以使用 DBMS_MVIEW.EXPLAIN_MVIEW 它将向名为 MV_CAPABILITIES_TABLE （可通过@oracle\u home/rdbms/admin/utlxmv.sql获得）。如果我们没有dba的脚本和授权，你需要得到它。无论如何，我会给下面的脚本以及
如何分析mv的性能：

--table structure
    CREATE TABLE mv_capabilities_table (
        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             INTEGER,
        msgtxt            VARCHAR2(2000),
        seq               NUMBER
    );

    --delete always before analyzing for a view to have only rows for a specific and not to have where clause to filter :)
    DELETE FROM mv_capabilities_table;

    --run this script which will analyze and insert into mv_capabilities_table
    BEGIN
       dbms_mview.explain_mview('EMP_MVIEW');
    END;
    /

/***ANALYSIS RESULT:***/

    --I am intersted only with data related to FAST REFRESH category
    SELECT capability_name
         ,possible
         ,substr(msgtxt
                ,1
                ,60) AS msgtxt
    FROM   mv_capabilities_table
    WHERE  capability_name LIKE '%FAST%';

    /**
    CAPABILITY_NAME                    POSSIBLE    MSGTXT
    REFRESH_FAST                            N 
    REFRESH_FAST_AFTER_INSERT               N      the SELECT list does not have the rowids of all the detail t
    REFRESH_FAST_AFTER_INSERT               N      mv log must have ROWID
    REFRESH_FAST_AFTER_INSERT               N      mv log must have ROWID
    REFRESH_FAST_AFTER_INSERT               N      mv log must have ROWID
    REFRESH_FAST_AFTER_ONETAB_DML           N      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
    REFRESH_FAST_AFTER_ANY_DML              N      see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
    REFRESH_FAST_PCT                        N      PCT FAST REFRESH is not possible if query contains LEFT OUTE
  **/

如果你用文字看结果 **mv log must have ROWID** 上面的分析结果告诉我们物化视图日志缺少 ROWID 选择，因此 FAST REFRESH 这是不可能的。
注：中的其他列 mv_capabilities_table 也会告诉你确切的表格，我没有包括，你可以检查和测试自己看看。
向前看。。
3.第三次尝试使用oracle的标准联接和包含选项rowid的mv日志创建mv：
steps：（不再提供脚本，只提供模拟的步骤
我将删除并用选项重新创建mv日志 ROWID 如上面的物化视图日志部分所述。
然后我将删除并重新创建相同的mv定义，我在我的第二次尝试，这将最终创建
接下来，我将尝试重复步骤来分析中压，如how to Analysis capability of mv section中所述
我在分析报告中得到了什么：

/**
CAPABILITY_NAME                         POSSIBLE  MSGTXT
REFRESH_FAST                            N 
REFRESH_FAST_AFTER_INSERT               N         the SELECT list does not have the rowids of all the detail t
REFRESH_FAST_AFTER_ONETAB_DML           N         see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML              N         see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                        N         PCT FAST REFRESH is not possible if query contains LEFT OUTE

**/

ufffff：我其实很累，而且mv又没有了 FAST REFRESH 但原因告诉我们 "the SELECT list does not have the rowids of all the detail tables" 它意味着：下一个标准 FAST REFRESH 是 Rowids of all the tables in the FROM list must appear in the SELECT list of the query 所以，
4.第四次也是最后一次尝试使用oracle的标准联接创建mv，并使用包含选项rowid的mv日志以及详细信息表rowid现在包含在select子句中：
步骤：
与3rd try一样，第一次刷新所需的mv日志已经就绪，我将删除并重新创建mv，但这次添加了rowids of detail表。
mv最终剧本：

CREATE MATERIALIZED VIEW EMP_MVIEW 
REFRESH FORCE ON COMMIT 
AS
SELECT emp.emp_id
      ,emp.empname
      ,sl.sal_id
      ,sl.salary
      ,ad.address_id
      ,ad.address_text
      ,emp.rowid emp_rowid
      ,sl.rowid sl_rowid
      ,ad.rowid ad_rowid
FROM   emp             emp
      ,salary_details  sl
      ,address_details ad
WHERE  emp.emp_id = sl.emp_id(+)
AND    emp.emp_id = ad.emp_id(+);

现在随着mv的创建，让我们再次分析mv的能力，如how to Analysis capability of mv section中所述(（祈祷吧）
结果：

SELECT capability_name
      ,possible
      ,substr(msgtxt,1,60) AS msgtxt
FROM   mv_capabilities_table
WHERE  capability_name LIKE '%FAST%';

/**
CAPABILITY_NAME                         POSSIBLE  MSGTXT
REFRESH_FAST                            Y 
REFRESH_FAST_AFTER_INSERT               Y 
REFRESH_FAST_AFTER_ONETAB_DML           Y 
REFRESH_FAST_AFTER_ANY_DML              Y 
REFRESH_FAST_PCT                        N         PCT FAST REFRESH is not possible if query contains LEFT OUTE

**/

最后 REFRESH_FAST 如我们所见，能力是可能的 POSSIBLE -> Y 抱歉回答太长，但我想我应该把我在mv里学到的东西，在过去，这可能是有用的分享。
我发现一些关于oracle物化视图的链接总是有用的：
甲骨文官方网站
甲骨文官方网站
甲骨文库
我最喜欢的阿尔贝托·戴尔
关于mv的一个很好的答案
干杯！！

通过连接视图和表来实现sql的物化视图

1条答案

相关问题

热门标签

最新问答