XML materialized view

[Roshan]

Oracle DB 12.1.0.2
Solaris 11.4

Hello Erman,

I would like to know if Oracle can capture DML on XML in redo logs. I am unable to perform fast refresh on below SQL

select WRKFLW_INSTANCE_ID_N,activityName,activityStatus,batchOnline,maxHoldTime,parentCount,parentSequence,sequenceNumber,timer,timerFlag,urlKeyCode,workAgent,lvl.id,lvl.resolutionTime,lvn.*
from WF_WORKFLOW,
xmltable(
  '/WorkFlow/Activity[node()]'
  passing WRKFLW_DATA_X
  columns
    activityName varchar2(100)  path '@activityName ',
    activityStatus varchar2(100) path '@activityStatus',
    batchOnline char(1) path '@batchOnline',
    maxHoldTime number(10) path '@maxHoldTime' ,
    parentCount number(2) path '@parentCount',
    parentSequence varchar2(15) path '@parentSequence',
    sequenceNumber varchar2(15) path '@sequenceNumber',
    timer number(2) path '@timer',
    timerFlag char(1) path '@timerFlag',
    urlKeyCode varchar2(2) path '@urlKeyCode',
    workAgent number(5) path '@workAgent',
    lvl xmltype path 'Owners/Level'
     
) wfl,
xmltable(
    'Level' passing lvl
    columns
        id number(10) path '@id ',
        resolutionTime number(10) path '@resolutionTime',
        lvn xmltype path 'Owner'
) lvl,
xmltable(
    'Owner' passing lvn
    columns
        defaultUser varchar(5),
        userGroup char(1) path '@userGroup',
        userGroupId number(5) path '@userGroupId'
        ) lvn

Grateful if you can advise.

Thanks,

Roshan

[ErmanArslansOracleBlog]

DBMS_MVIEW.EXPLAIN_MVIEW can be used to highlight what is supported for the mview in question.
Ref: How to Build a Materialized View Containing an XMLTYPE Column (Doc ID 1489788.1)

However; you have that xmltable function in your MV..
Materialized views are not eligible for fast refresh if their query contains an analytic function or --> the XMLTable function.
So this is a known restriction.. I think it answers your question.

See -> Restrictions on FAST Refresh -- in docs.oracle