Real-time materialized view not working as expected

Hello,

I have a problem with Real-time Materialized View ‘ON QUERY COMPUTATION’ functionality.

My Real-time MV is enabled for both QUERY REWRITE & ON QUERY COMPUTATION.

As I understand, when the MV is fresh, we get a MAT_VIEW REWRITE ACCESS FULL run which is not because ON QUERY COMPUTATION but QUERY REWRITE option which was already there prior to Oracle 12c as well. This situation work fine for me.

But when the MV is not FRESH, it does a full table scan instead of reading MV + MV Log (& table). Due to this, performance becomes bad at this situation.

When the trace file is analyzed, an error is visible at the beginning of the trace with text “Error encountered: ORA-10980”. This error is not thrown to client & query operation succeeds any how. However, this error is present only when I set ON QUERY COMPUTATION option for the MV, i.e. when the MV becomes a Real-time MV.

Also apart from the main select statement, there is another query is visible for this run as below which is related to the corresponding MV Log.

select dmltype$$, max(snaptime$$) from “IFSAPP”.”MLOG$_CUSTOMER_ORDER_LINE_TAB” where snaptime$$ <= :1 group by dmltype$$

My Parameter Settings:

———————-

query_rewrite_integrity = ENFORCED

query_rewrite_enabled = TRUE

Materialized View Log Definition:

———————————

CREATE MATERIALIZED VIEW LOG ON customer_order_line_tab WITH ROWID (order_no,qty_invoiced,part_price) INCLUDING NEW VALUES;

Materialized View Definition:

————————–

CREATE MATERIALIZED VIEW customer_order_line_mv

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

ENABLE ON QUERY COMPUTATION

AS

SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr

FROM customer_order_line_tab t

GROUP BY t.order_no;

Query executed:

————–

SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr

FROM customer_order_line_tab t

GROUP BY t.order_no;

Please find the attached Source trace file (LiveSQL) when ON QUERY COMPUTATION is set)

Please advice us on the error I’m having in trace file & why ON QUERY COMPUTATION is not working for my scenario?

Many Thanks,

Navinth

Related:

Leave a Reply