oracle - 如何使用联接和聚合快速刷新物化 View ？

Question

假设我有两个表job和batch:

CREATE TABLE batch
(
  batch_id   NUMBER(20) PRIMARY KEY,
  batch_type NUMBER(20),
  [some other values] ...
);

CREATE TABLE job
(
  job_id NUMBER(20) PRIMARY KEY,
  job_batch_id NUMBER(20),
  job_usr_id NUMBER(20),
  job_date DATE,
  [some other values] ...
  CONSTRAINT fk_job_batch
    FOREIGN KEY (job_batch_id) REFERENCES batch(batch_id),
  CONSTRAINT fk_job_usr
    FOREIGN KEY (job_usr_id) REFERENCES client(usr_id)
);

假设它们每个都包含大量数据(数百万行)。我想要做的是创建一个物化 View 来反射(reflect)每个 usr_id 针对特定类型的批处理运行的第一个和最后一个作业。例如:

CREATE MATERIALIZED VIEW client_first_last_job
(usr_id, first_job_date, last_job_date)
AS
(
  SELECT
    job_usr_id    AS usr_id,
    MIN(job_date) AS first_job_date,
    MAX(job_date) AS last_job_date
  FROM job, batch
  WHERE job_batch_id=batch_id
    AND batch_type IN (1,3,5,9)
  GROUP BY job_usr_id
);

这一切都很好，但是由于记录太多，构建这个物化 View 需要很长时间(远远超过每次需要刷新时可接受的处理时间)。我立即想到的是使用物化 View 日志进行增量更新。这些很容易创建。但是，当我尝试构建 MV 以使用REFRESH FAST ON DEMAND时，会出现ORA-12015:无法从复杂查询创建快速刷新物化 View 错误，根据一些谷歌搜索，我猜测这是由于连接函数和聚合函数的共存所致。

还有其他方法可以做到这一点吗？请注意，对父表进行反规范化或其他更改是不可行的。

Answer 1

您可以嵌套您的 mview，您可以从 docs 中阅读有关内容。 :

CREATE MATERIALIZED VIEW joinmview
(usr_id, job_date)
REFRESH FORCE ON DEMAND
AS
(
  SELECT
    job_usr_id    AS usr_id,
    job_date
  FROM job, batch
  WHERE job_batch_id=batch_id
    AND batch_type IN (1,3,5,9)
);

CREATE MATERIALIZED VIEW LOG ON JOINMVIEW 
  WITH ROWID (usr_id, JOB_DATE) including new values;

CREATE MATERIALIZED VIEW client_first_last_job
(usr_id, first_job_date, last_job_date)
REFRESH FORCE ON DEMAND
AS
(
  SELECT
    usr_id,
    MIN(job_date) AS first_job_date,
    MAX(job_date) AS last_job_date
  FROM joinmview
  GROUP BY usr_id
);

验证两个mview都可以快速刷新:

exec dbms_mview.refresh('JOINMVIEW', 'C');
exec dbms_mview.refresh('JOINMVIEW', 'F');

exec dbms_mview.refresh('CLIENT_FIRST_LAST_JOB', 'C');    
exec dbms_mview.refresh('CLIENT_FIRST_LAST_JOB', 'F');

您可以将两个 mview 放入同一个刷新组 ( docs )，只需确保按照其依赖关系的顺序添加它们即可。换句话说，在此示例中，在将 CLIENT_FIRST_LAST_JOB 添加到刷新组之前添加 JOINMVIEW。