如何使用Joins和Aggregates在物化视图上快速刷新？

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)
);

假设它们都包含大量数据(数百万行)。我想要做的是创建一个物化视图，以反映每个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
);

这一切都很好，但是因为有太多的记录，所以构建这个物化视图需要很长时间(每次需要刷新时处理的时间都远远超过了可接受的时间)。我的直接想法是使用物化视图日志进行增量更新。这些都很容易创建。但是，当我试图构建MV来使用REFRESH FAST ON DEMAND时，这会给我带来一个ORA-12015: cannot create a fast refresh materialized view from a complex query错误，我猜想这是由于连接函数和聚合函数共存造成的。

还有别的办法吗？请注意，取消规范化或对父表的其他更改是不可行的。

Answer 1

您可以嵌套您的mview，您可以从文档中看到这些信息。

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放到同一个刷新组(文档)中，只需确保按照它们的依赖关系的顺序添加它们。换句话说，在本例中，在之前添加JOINMVIEW ，将CLIENT_FIRST_LAST_JOB添加到刷新组。