Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Why my mview is not refreshing after commit?

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
Best Answers
-
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.
-
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.
Answers
-
Mview's properties:
-
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
-
Sorry for the Polish output.
-
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.
-
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)
-
"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.
-
Do your Benchmarks support the need for an MV? or would a normal
VIEW
be "fast enough"?? -
I need MV as normal view is too slow, unfortunately.
-
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.
-
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 theSELECT
list. You havemin(ouv_start)
andmax(ouv_end)
in your list, but notcount
over the same columns.Second, MV's with
max
ormin
in theSELECT
list can be fast refreshable after update/delete if the MV does not have aWHERE
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 aWHERE
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.The problem I have with this fragment in the documentation is the syntagm "
SELECT
column". There is no such thing; there is aSELECT
list of columns. I assume they meant to say "every column in theSELECT
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.