Forum Stats

  • 3,855,262 Users
  • 2,264,493 Discussions
  • 7,905,953 Comments

Discussions

Categories

Why my mview is not refreshing after commit?

JackK
JackK Member Posts: 703 Bronze Badge
edited Feb 7, 2022 8:37AM in SQL & PL/SQL

Hi All.

I have tables:

create table OS_OUTAGES (
 out_id       NUMBER(22) generated by default on null as identity (nocache),
 out_number     VARCHAR2(40) not null,
 out_status     VARCHAR2(10) not null,
 out_dscr      VARCHAR2(4000),
 out_is_proposition VARCHAR2(1),
 out_usr_id     INTEGER not null,
 out_oki_code    VARCHAR2(20) not null,
 out_bup_code    NUMBER(12) not null,
 out_oup_id     NUMBER(20),
 out_gel_mrid    VARCHAR2(70) not null,
 out_vl_mrid    VARCHAR2(70),
 out_oty_code    VARCHAR2(20) not null,
 out_ouv_id     NUMBER(22) not null,
 out_swl_id     NUMBER(4),
 out_locked_by   NUMBER(22),
 out_locked_until  DATE,
 out_out_id     NUMBER(22),
 out_sch_id     NUMBER(22),
 out_ois_id     NUMBER(22),
 out_number__sort  as ("OMSW"."OS_UTIL"."OBJNUMBER2SORT"("OUT_NUMBER")),
 out_root_out_id  NUMBER(22),
 out_usid      VARCHAR2(55) invisible
);

alter table OS_OUTAGES add constraint OUT_PK primary key (OUT_ID);

alter table OS_OUTAGES
 add constraint OUT_OUV_FK foreign key (OUT_OUV_ID)
 references OS_OUTAGE_VERSIONS (OUV_ID) on delete set null
 deferrable initially deferred;

---

create table OS_OUTAGE_VERSIONS (
 ouv_id           NUMBER(22) generated by default on null as identity (nocache),
 ouv_change_time       DATE default sysdate not null,
 ouv_start          TIMESTAMP(0) WITH TIME ZONE not null,
 ouv_end           TIMESTAMP(0) WITH TIME ZONE not null,
 ouv_readiness        NUMBER,
 ouv_cycle          VARCHAR2(2) not null,
 ouv_break_possibility    CHAR(1),
 ouv_night_work       CHAR(1),
 ouv_saturday_work      CHAR(1),
 ouv_sunday_work       CHAR(1),
 ouv_out_id         NUMBER(22) not null,
 ouv_usr_id         INTEGER not null,
 ouv_status         VARCHAR2(10),
 ouv_version         VARCHAR2(43) not null,
 ouv_ouv_id         NUMBER(22),
 ouv_readiness_u       VARCHAR2(1),
 ouv_required_time      NUMBER,
 ouv_required_time_u     VARCHAR2(1),
 ouv_required_time__sort   as ("OUV_REQUIRED_TIME"*DECODE("OUV_REQUIRED_TIME_U",'d',1440,'h',60,'m',1)),
 ouv_prefered_start     TIMESTAMP(0) WITH TIME ZONE,
 ouv_prefered_end      TIMESTAMP(0) WITH TIME ZONE,
 ouv_cost          NUMBER,
 ouv_refusing_cost      NUMBER,
 ouv_readiness__sort     as ("OUV_READINESS"*DECODE("OUV_READINESS_U",'d',1440,'h',60,'m',1)),
 ouv_crr_id         NUMBER(4),
 ouv_rb2_09_valid      CHAR(1) default 'n' not null,
 ouv_in_cartesian      CHAR(1) default 'y' not null,
 ouv_weekend_work      CHAR(1),
 ouv_holiday_work      CHAR(1),
 ouv_priority        NUMBER(1) default 1,
 ouv_night_readiness     NUMBER(3),
 ouv_night_readiness_u    CHAR(1),
 ouv_busbar_work_possibility CHAR(1),
 ouv_schedule_required    CHAR(1),
 ouv_programme_required   CHAR(1),
 ouv_temp_ver        NUMBER(3),
 ouv_length         as ("OMSW"."OS_OUTAGE"."OUV_LENGTH_CALC"("OUV_START","OUV_END"))
);

alter table OS_OUTAGE_VERSIONS add constraint OUV_PK primary key (OUV_ID);

alter table OS_OUTAGE_VERSIONS
 add constraint OUV_OUT_FK foreign key (OUV_OUT_ID)
 references OS_OUTAGES (OUT_ID) on delete cascade;

---

create table OS_OUTAGE_PACKAGES (
 opa_id      NUMBER(22) generated by default on null as identity (nocache),
 opa_usr_id    NUMBER(22) not null,
 opa_opv_id    NUMBER(22),
 opa_locked_by  NUMBER(22),
 opa_locked_until DATE,
 opa_status    VARCHAR2(5) not null,
 opa_opa_id    NUMBER(22),
 opa_number    VARCHAR2(40),
 opa_sch_id    NUMBER(22),
 opa_pis_id    NUMBER(22),
 opa_bup_code   NUMBER(12) not null,
 opa_number__sort as ("OMSW"."OS_UTIL"."OBJNUMBER2SORT"("OPA_NUMBER")),
 opa_root_opa_id NUMBER(22),
 opa_type     CHAR(1) default 'P' not null,
 opa_category   CHAR(1)
);

alter table OS_OUTAGE_PACKAGES add constraint OPA_PK primary key (OPA_ID);

alter table OS_OUTAGE_PACKAGES
 add constraint OPA_OPV_FK foreign key (OPA_OPV_ID)
 references OS_OUTAGE_PACKAGE_VERSIONS (OPV_ID)
 deferrable initially deferred;

---

create table OS_OUTAGE_PACKAGE_VERSIONS (
 opv_id     NUMBER(22) generated by default on null as identity (nocache),
 opv_change_time DATE default sysdate not null,
 opv_opa_id   NUMBER(22) not null,
 opv_opv_id   NUMBER(22),
 opv_crr_id   NUMBER(4),
 opv_usr_id   NUMBER(22) not null,
 opv_version   VARCHAR2(43) not null,
 opv_management VARCHAR2(4),
 opv_pp_demand  CHAR(1),
 opv_h_demand  CHAR(1),
 opv_bw_demand  CHAR(1),
 opv_pca_code  VARCHAR2(10),
 opv_out_id   NUMBER(22),
 opv_temp_ver  NUMBER(3),
 opv_dscr    VARCHAR2(1500)
);

alter table OS_OUTAGE_PACKAGE_VERSIONS add constraint OPV_PK primary key (OPV_ID);

alter table OS_OUTAGE_PACKAGE_VERSIONS
 add constraint OPV_OPA_FK foreign key (OPV_OPA_ID)
 references OS_OUTAGE_PACKAGES (OPA_ID) on delete cascade;

alter table OS_OUTAGE_PACKAGE_VERSIONS
 add constraint OPV_OUT_FK foreign key (OPV_OUT_ID)
 references OS_OUTAGES (OUT_ID);

---

create table OS_OUTS_IN_PACKAGE (
 oip_id   NUMBER(22) generated by default on null as identity (nocache),
 oip_opv_id NUMBER(22) not null,
 oip_ouv_id NUMBER(22) not null,
 oip_status CHAR(1),
 oip_created DATE default sysdate
);

alter table OS_OUTS_IN_PACKAGE
 add constraint OIP_OPV_FK foreign key (OIP_OPV_ID)
 references OS_OUTAGE_PACKAGE_VERSIONS (OPV_ID) on delete cascade;

alter table OS_OUTS_IN_PACKAGE
 add constraint OIP_OUV_FK foreign key (OIP_OUV_ID)
 references OS_OUTAGE_VERSIONS (OUV_ID) on delete cascade;

There are of course some indexes created on FK columns.

I create materialized view log created:

create materialized view log on OS_OUTAGES
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
out_number
,out_status
,out_dscr
,out_is_proposition
,out_usr_id
,out_oki_code
,out_bup_code
,out_gel_mrid
,out_vl_mrid
,out_oty_code
,out_ouv_id
,out_swl_id
,out_locked_by
,out_locked_until
,out_out_id
,out_sch_id
,out_ois_id
--out_number__sort
,out_root_out_id
--out_usid
) including new values;

create materialized view log on OS_OUTAGE_VERSIONS
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
--ouv_id
ouv_change_time
,ouv_start
,ouv_end
,ouv_readiness
,ouv_cycle
,ouv_break_possibility
,ouv_night_work
,ouv_saturday_work
,ouv_sunday_work
,ouv_out_id
,ouv_usr_id
,ouv_status
,ouv_version
,ouv_ouv_id
,ouv_readiness_u
,ouv_required_time
,ouv_required_time_u
--ouv_required_time__sort
,ouv_prefered_start
,ouv_prefered_end
,ouv_cost
,ouv_refusing_cost
--ouv_readiness__sort
,ouv_crr_id
,ouv_rb2_09_valid
,ouv_in_cartesian
,ouv_weekend_work
,ouv_holiday_work
,ouv_priority
,ouv_night_readiness
,ouv_night_readiness_u
,ouv_busbar_work_possibility
,ouv_schedule_required
,ouv_programme_required
,ouv_temp_ver
--ouv_length
) including new values;

create materialized view log on OS_OUTAGE_PACKAGES
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
--opa_id
opa_usr_id
,opa_opv_id
,opa_locked_by
,opa_locked_until
,opa_status
,opa_opa_id
,opa_number
,opa_sch_id
,opa_pis_id
,opa_bup_code
--,opa_number__sort
,opa_root_opa_id
,opa_type
,opa_category
) including new values;

create materialized view log on OS_OUTAGE_PACKAGE_VERSIONS
 with primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
--opv_id
opv_change_time
,opv_opa_id
,opv_opv_id
,opv_crr_id
,opv_usr_id
,opv_version
,opv_management
,opv_pp_demand
,opv_h_demand
,opv_bw_demand
,opv_pca_code
,opv_out_id
,opv_temp_ver
,opv_dscr
) including new values;

create materialized view log on OS_OUTS_IN_PACKAGE
 with --primary key, -- uncomment if table has PK
    rowid,
    sequence( -- all but PK columns
 oip_id
,oip_opv_id
,oip_ouv_id
,oip_status
,oip_created
) including new values;

Then I create mview:

create materialized view OS_OPV_AGGR_MVW
refresh fast on commit
as
SELECT opa_id, opv_id,
    min(ouv_start) AS min_start,
    max(ouv_end)  AS max_end
   ,count(*)    AS cnt
 FROM OS_OUTAGE_PACKAGES
   ,OS_OUTAGE_PACKAGE_VERSIONS
   ,OS_OUTS_IN_PACKAGE
   ,OS_OUTAGES
   ,OS_OUTAGE_VERSIONS
 WHERE opv_id = opa_opv_id
  AND oip_opv_id = opa_opv_id
  AND out_ouv_id = oip_ouv_id
  AND ouv_id = out_ouv_id
 GROUP BY opa_id, opv_id;

Mview is created correctly. However, when transaction is commited, mview is not being refreshed and rows in mvlogs persist.

I don't know where is the problem.

Could anyone help me?

I am using Oracle 19c.

Best regards,

Jacek

Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond
    edited Feb 8, 2022 11:08AM Answer ✓

    The report tells you the answer: you can get fast commit on insert, but not general DML.

    [email protected] trace]$ oerr qsm 2086
02086, 00000, "mv uses the MIN, MAX or ANY_VALUE aggregate functions"
// *Cause:    The capability in question is not supported when the materialized
//            view uses the MIN, MAX, or ANY_VALUE function.
// *Action:   Re-phrase the query to avoid the use of the MIN, MAX or
//            ANY_VALUE functions.

    Insert is okay - if I insert a new row the new value is either larger than the current max (in which case it becomes the max) or not larger than the current max, in which case the max doesn't change.

    update, delete, etc: would require a check on the existing table to determine the state. e.g. if I delete a row which holds the current max what's the new max? [If] It might not change, but it might need to change.

    Regards

    Jonathan Lewis


    Edit: corrected if/it typo.

    CORRECTION: my comments were a long way from complete. In the case of SINGLE table it is possible to define the materialized view to handle aggregation during a fast refresh provided you also include count()'s as well. [See also the comments by @mathguy ]

    There may be cases where MVs that include aggregates AND joins can work - but they may have to follow very stringent rules to make it possible for Oracle to work out the effect of (say) one deleted row in one table affecting a min/max() from another table. It's also possible that in some patterns a fast refresh that is theoretically possible has not been implemented because the workload of the refresh has been considered to be too large.

    JackK
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    Answer ✓

    As others already mentioned you need to split materialized view into 2:

    CREATE MATERIALIZED VIEW OS_OPV_AGGR_MVW1
 BUILD IMMEDIATE
 REFRESH FAST
 ON COMMIT
 WITH ROWID
 AS
  SELECT OPA_ID,
      OPV_ID,
      OUV_START,
      OUV_END,
      OS_OUTAGE_PACKAGES.ROWID RID1,
      OS_OUTAGE_PACKAGE_VERSIONS.ROWID RID2,
      OS_OUTS_IN_PACKAGE.ROWID RID3,
      OS_OUTAGES.ROWID RID4,
      OS_OUTAGE_VERSIONS.ROWID RID5
   FROM OS_OUTAGE_PACKAGES,
      OS_OUTAGE_PACKAGE_VERSIONS,
      OS_OUTS_IN_PACKAGE,
      OS_OUTAGES,
      OS_OUTAGE_VERSIONS
   WHERE OPV_ID = OPA_OPV_ID
    AND OIP_OPV_ID = OPA_OPV_ID
    AND OUT_OUV_ID = OIP_OUV_ID
    AND OUV_ID = OUT_OUV_ID
/
CREATE MATERIALIZED VIEW LOG ON OS_OPV_AGGR_MVW1
 WITH ROWID,
   SEQUENCE(
        OPA_ID,
        OPV_ID,
        OUV_START,
        OUV_END,
        RID1,
        RID2,
        RID3,
        RID4,
        RID5
       )
 INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW OS_OPV_AGGR_MVW2
 BUILD IMMEDIATE
 REFRESH FAST ON COMMIT
 AS
  SELECT OPA_ID,
      OPV_ID,
      MIN(OUV_START) MIN_START,
      MAX(OUV_END) MAX_END,
      COUNT(*) CNT
   FROM OS_OPV_AGGR_MVW1
   GROUP BY OPA_ID,
        OPV_ID
/

    Now:

    SQL> INSERT
  2    INTO OS_OUTAGES(out_id,out_number,out_status,out_usr_id,out_oki_code,out_bup_code,out_gel_mrid,out_oty_code,out_ouv_id)
  3    VALUES(1,'1','C',1,'C','1','C','X',1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTAGE_PACKAGES(opa_usr_id,opa_status,opa_bup_code,opa_opv_id,opa_id)
  3    VALUES(1,'S',1,1,1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTAGE_VERSIONS(ouv_start,ouv_end,ouv_cycle,ouv_out_id,ouv_usr_id,ouv_status,ouv_version,ouv_id)
  3    VALUES(SYSTIMESTAMP,SYSTIMESTAMP + 1,'C',1,1,'S','1',1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTAGE_PACKAGE_VERSIONS(opv_opa_id,opv_usr_id,opv_version,opv_id)
  3    VALUES(1,1,1,1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTS_IN_PACKAGE(oip_opv_id,oip_ouv_id)
  3    VALUES(1,1)
  4  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  OS_OPV_AGGR_MVW2
  3  /

    OPA_ID     OPV_ID MIN_START                   MAX_END                            CNT
---------- ---------- ---------------------------- ---------------------------- ----------
         1          1 07-FEB-22 05.20.34 PM -05:00 08-FEB-22 05.20.33 PM -05:00          1

SQL> UPDATE OS_OUTAGE_VERSIONS
  2     SET ouv_end = SYSTIMESTAMP + INTERVAL '10' DAY
  3  /

1 row updated.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  OS_OPV_AGGR_MVW2
  3  /

    OPA_ID     OPV_ID MIN_START                   MAX_END                            CNT
---------- ---------- ---------------------------- ---------------------------- ----------
         1          1 07-FEB-22 05.20.34 PM -05:00 17-FEB-22 05.20.34 PM -05:00          1

SQL> DELETE OS_OUTAGES
  2  /

1 row deleted.

SQL> DELETE OS_OUTAGE_VERSIONS;

0 rows deleted.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  OS_OPV_AGGR_MVW2
  3  /

no rows selected

SQL>

    SY.

    JackK
    · Share on TwitterShare on Facebook
«1

Answers

  • JackK
    JackK Member Posts: 703 Bronze Badge

    Mview's properties:

    image.png


    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    What does the mv_capabilities table tell you after a call to dbms_mview.explain_mview() for that materialzied view?

    (see, for example: https://jonathanlewis.wordpress.com/2020/08/18/explain-rewrite/ )


    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • JackK
    JackK Member Posts: 703 Bronze Badge
    image.png

    Sorry for the Polish output.

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond
    edited Feb 8, 2022 11:08AM Answer ✓

    The report tells you the answer: you can get fast commit on insert, but not general DML.

    [email protected] trace]$ oerr qsm 2086
02086, 00000, "mv uses the MIN, MAX or ANY_VALUE aggregate functions"
// *Cause:    The capability in question is not supported when the materialized
//            view uses the MIN, MAX, or ANY_VALUE function.
// *Action:   Re-phrase the query to avoid the use of the MIN, MAX or
//            ANY_VALUE functions.

    Insert is okay - if I insert a new row the new value is either larger than the current max (in which case it becomes the max) or not larger than the current max, in which case the max doesn't change.

    update, delete, etc: would require a check on the existing table to determine the state. e.g. if I delete a row which holds the current max what's the new max? [If] It might not change, but it might need to change.

    Regards

    Jonathan Lewis


    Edit: corrected if/it typo.

    CORRECTION: my comments were a long way from complete. In the case of SINGLE table it is possible to define the materialized view to handle aggregation during a fast refresh provided you also include count()'s as well. [See also the comments by @mathguy ]

    There may be cases where MVs that include aggregates AND joins can work - but they may have to follow very stringent rules to make it possible for Oracle to work out the effect of (say) one deleted row in one table affecting a min/max() from another table. It's also possible that in some patterns a fast refresh that is theoretically possible has not been implemented because the workload of the refresh has been considered to be too large.

    JackK
    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,200 Silver Crown

    If might not change, but it might need to change.

    I haven't tried, but can you have 2 MVs and a VIEW on top?

    • MV 1 is the given MV without MIN/MAX columns
    • MV 2 holds the Key/Value information needed to properly calculate MIN/MAX (may not be needed)
    • VIEW joins the two MVs and calculates MIN/MAX

    (I was actually thinking of the VIEW being and MV. But, that doesn't make sense; too many cogs)

    · Share on TwitterShare on Facebook
  • JackK
    JackK Member Posts: 703 Bronze Badge
    edited Feb 7, 2022 7:50PM

    "The report tells you the answer: you can get fast commit on insert, but not general DML."

    Does one have any idea how to overcome this limitation?

    According to:

    // *Action:   Re-phrase the query to avoid the use of the MIN, MAX or
//            ANY_VALUE functions.

    there should be a solution.

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,200 Silver Crown

    Do your Benchmarks support the need for an MV? or would a normal VIEW be "fast enough"??

    · Share on TwitterShare on Facebook
  • JackK
    JackK Member Posts: 703 Bronze Badge

    I need MV as normal view is too slow, unfortunately.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    Answer ✓

    As others already mentioned you need to split materialized view into 2:

    CREATE MATERIALIZED VIEW OS_OPV_AGGR_MVW1
 BUILD IMMEDIATE
 REFRESH FAST
 ON COMMIT
 WITH ROWID
 AS
  SELECT OPA_ID,
      OPV_ID,
      OUV_START,
      OUV_END,
      OS_OUTAGE_PACKAGES.ROWID RID1,
      OS_OUTAGE_PACKAGE_VERSIONS.ROWID RID2,
      OS_OUTS_IN_PACKAGE.ROWID RID3,
      OS_OUTAGES.ROWID RID4,
      OS_OUTAGE_VERSIONS.ROWID RID5
   FROM OS_OUTAGE_PACKAGES,
      OS_OUTAGE_PACKAGE_VERSIONS,
      OS_OUTS_IN_PACKAGE,
      OS_OUTAGES,
      OS_OUTAGE_VERSIONS
   WHERE OPV_ID = OPA_OPV_ID
    AND OIP_OPV_ID = OPA_OPV_ID
    AND OUT_OUV_ID = OIP_OUV_ID
    AND OUV_ID = OUT_OUV_ID
/
CREATE MATERIALIZED VIEW LOG ON OS_OPV_AGGR_MVW1
 WITH ROWID,
   SEQUENCE(
        OPA_ID,
        OPV_ID,
        OUV_START,
        OUV_END,
        RID1,
        RID2,
        RID3,
        RID4,
        RID5
       )
 INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW OS_OPV_AGGR_MVW2
 BUILD IMMEDIATE
 REFRESH FAST ON COMMIT
 AS
  SELECT OPA_ID,
      OPV_ID,
      MIN(OUV_START) MIN_START,
      MAX(OUV_END) MAX_END,
      COUNT(*) CNT
   FROM OS_OPV_AGGR_MVW1
   GROUP BY OPA_ID,
        OPV_ID
/

    Now:

    SQL> INSERT
  2    INTO OS_OUTAGES(out_id,out_number,out_status,out_usr_id,out_oki_code,out_bup_code,out_gel_mrid,out_oty_code,out_ouv_id)
  3    VALUES(1,'1','C',1,'C','1','C','X',1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTAGE_PACKAGES(opa_usr_id,opa_status,opa_bup_code,opa_opv_id,opa_id)
  3    VALUES(1,'S',1,1,1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTAGE_VERSIONS(ouv_start,ouv_end,ouv_cycle,ouv_out_id,ouv_usr_id,ouv_status,ouv_version,ouv_id)
  3    VALUES(SYSTIMESTAMP,SYSTIMESTAMP + 1,'C',1,1,'S','1',1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTAGE_PACKAGE_VERSIONS(opv_opa_id,opv_usr_id,opv_version,opv_id)
  3    VALUES(1,1,1,1)
  4  /

1 row created.

SQL> INSERT
  2    INTO OS_OUTS_IN_PACKAGE(oip_opv_id,oip_ouv_id)
  3    VALUES(1,1)
  4  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  OS_OPV_AGGR_MVW2
  3  /

    OPA_ID     OPV_ID MIN_START                   MAX_END                            CNT
---------- ---------- ---------------------------- ---------------------------- ----------
         1          1 07-FEB-22 05.20.34 PM -05:00 08-FEB-22 05.20.33 PM -05:00          1

SQL> UPDATE OS_OUTAGE_VERSIONS
  2     SET ouv_end = SYSTIMESTAMP + INTERVAL '10' DAY
  3  /

1 row updated.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  OS_OPV_AGGR_MVW2
  3  /

    OPA_ID     OPV_ID MIN_START                   MAX_END                            CNT
---------- ---------- ---------------------------- ---------------------------- ----------
         1          1 07-FEB-22 05.20.34 PM -05:00 17-FEB-22 05.20.34 PM -05:00          1

SQL> DELETE OS_OUTAGES
  2  /

1 row deleted.

SQL> DELETE OS_OUTAGE_VERSIONS;

0 rows deleted.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  OS_OPV_AGGR_MVW2
  3  /

no rows selected

SQL>

    SY.

    JackK
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,692 Blue Diamond

    If the documentation is to be trusted, there are - I believe - three problems with your materialized view. Two of them can be fixed; the third can't, and the documentation itself suggests the use of nested materialized views, as Solomon has illustrated already.

    First, the SELECT list of the MV should include count(expr) for every expression over which there's an aggregate function in the SELECT list. You have min(ouv_start) and max(ouv_end) in your list, but not count over the same columns.

    Second, MV's with max or min in the SELECT list can be fast refreshable after update/delete if the MV does not have a WHERE clause. Yours does, but that's just for the joins. Now, as I recall (perhaps wrongly, or from a wrong source), there was or still is an issue with ANSI join syntax and fast refreshable MV's. In any case, if you can change the joins to ANSI syntax your MV will no longer have a WHERE clause, and then it should be fast refreshable.

    However, here's the key point (I think). Here's the quote from the documentation:

    The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

    https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-932B8CD4-BF60-419A-9202-8A9FD5D24024

    The problem I have with this fragment in the documentation is the syntagm "SELECT column". There is no such thing; there is a SELECT list of columns. I assume they meant to say "every column in the SELECT list ..." But there is also another problem - they don't quite define with 100% clarity what expressions are "complex". In your case, the aggregates are over single columns, but I believe they are still "complex" - because the grouping is over columns from two other tables. [This should be clarified one way or another: does this, indeed, make those expressions "complex"?]

    And as you can see, they mention the possible workaround - nested materialized view.

    JackK
    · Share on TwitterShare on Facebook