oracle - 使用物化 View 跟踪最新版本的记录

Question

我们有一个高度(也许结束了？)规范化的表来跟踪版本化的值。它只是插入，没有更新。

示例数据:

"ID"    "Version"   "Value"
1       0           "A_1"
2       0           "B_1"
1       1           "A_2"
3       0           "C_1"

我们经常运行查询以仅提取每个 ID 的最新值。当我们达到数百万行时，我们开始遇到性能问题。我已经能够使用物化 View 对改进进行原型(prototype)设计，但无法以它们自我刷新“ON COMMIT”的方式创建它们

到目前为止我得到的是这个(以下修订)

CREATE MATERIALIZED VIEW TABLE_LATEST 
    BUILD IMMEDIATE
    REFRESH FAST 
    ON COMMIT AS

SELECT  T.ID
       ,T.LAST_VERSION
FROM (
    SELECT  ID
           ,MAX(VERSION) OVER (PARTITION BY ID) LAST_VERSION
    FROM    TABLE
) T
GROUP BY T.ID, T.LAST_VERSION;

由于反馈，现在对其进行了修订:

CREATE MATERIALIZED VIEW TABLE_LATEST 
    BUILD IMMEDIATE
    REFRESH FAST 
    ON COMMIT AS

SELECT  ID
       ,MAX(VERSION)
FROM    TABLE
GROUP BY T.ID;

失败了:

ORA-12033: cannot use filter columns from materialized view log on "SCHEMA"."TABLE"

*Cause:    The materialized view log either did not have filter columns
           logged, or the timestamp associated with the filter columns was
           more recent than the last refresh time.

*Action:   A complete refresh is required before the next fast refresh.
           Add filter columns to the materialized view log, if required.

如果我更改 Refresh，它只会“工作”至 Force并删除 On Commit .我不知道这是否属于物化 View 的“无分析”规则，或者我是否首先错误地创建了日志？

CREATE MATERIALIZED VIEW LOG ON TABLE
LOGGING 
WITH SEQUENCE, ROWID, (VALUE) 
INCLUDING NEW VALUES;

表架构:

CREATE TABLE "TABLE"
(
  ID NUMBER(10, 0) NOT NULL 
, VERSION NUMBER(10, 0) NOT NULL 
, VALUE VARCHAR2(4000 CHAR) 
, CONSTRAINT MASTERRECORDFIELDVALUES_PK PRIMARY KEY 
  (
    ID
  , VERSION 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX TABLE_PK ON TABLE(ID ASC, VERSION ASC) 
      LOGGING 
      ...
  )
  ENABLE 
) 
LOGGING 

我什至走在正确的轨道上吗？是否有更好的方法来预先计算最新版本？还是我只需要拨入 Log & View 设置？

Answer 1

如果您不需要与最新版本关联的值，那么您可以简单地执行以下操作:

CREATE MATERIALIZED VIEW LOG ON t1 
LOGGING  
WITH SEQUENCE, ROWID, (val) 
INCLUDING NEW VALUES;

create materialized view t1_latest
refresh fast on commit
as
  select id,
         max(version) latest_version
  from   t1
  group by id;

可以找到此测试用例 over at Oracle LiveSQL .

否则，您需要创建三个单独的 MV(因为您无法在提交物化 View 中快速刷新，这涉及将 dense_rank 保持在第一个/最后一个) - 根据 http://www.sqlsnippets.com/en/topic-12926.html - 像这样:

主表物化 View 日志:

CREATE MATERIALIZED VIEW LOG ON t1
LOGGING 
WITH SEQUENCE, ROWID, (val)
INCLUDING NEW VALUES;

第一个物化 View :

create materialized view t1_sub_mv1
refresh fast on commit
as
  select id,
         max(version) latest_version,
         count(version) cnt_version,
         count(*) cnt_all
  from   t1
  group by id;

第一个物化 View 的物化 View 日志:

create materialized view log on t1_sub_mv1
with rowid, sequence (id, latest_version, cnt_version, cnt_all)
including new values;

第二个物化 View :

create materialized view t1_sub_mv2
refresh fast on commit
as
  select id,
         version,
         max(val) max_val_per_id_version,
         count(*) cnt_all
  from   t1
  group by id,
           version;

第一个物化 View 的物化 View 日志:

create materialized view log on t1_sub_mv2
with rowid, sequence (id, max_val_per_id_version, cnt_all)
including new values;

第三个也是最后一个物化 View :

create materialized view t1_main_mv
refresh fast on commit
as
  select mv1.id,
         mv1.latest_version,
         mv2.max_val_per_id_version val_of_latest_version,
         mv1.rowid mv1_rowid,
         mv2.rowid mv2_rowid
  from   t1_sub_mv1 mv1,
         t1_sub_mv2 mv2
  where  mv1.id = mv2.id
  and    mv1.latest_version = mv2.version;

可以找到支持此的测试用例 over at Oracle LiveSQL .