Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why MV refresh fast on commit is not possible?

JackKDec 8 2021

Hi All!
I am testing possibility of creating MV refreshed fast on commit:

begin
dbms_mview.explain_mview(
'SELECT U.timeDiff2seconds(rev_start,rev_end)  -- deterministic function returning a number
       ,req_jot_code
   FROM OS_REQS_IN_OUTAGE  -- normal table
   JOIN OS_REQUIREMENT_VERSIONS ON rev_id = rio_rev_id  -- normal table
   JOIN OS_REQUIREMENTS ON rev_id = req_rev_id  -- normal table
  ORDER BY 1 DESC, rev_start, rev_cycle DESC
       ,rev_readiness__sort  -- virtual column in OS_REQUIREMENT_VERSIONS table
       ,rev_id DESC');
end;
/

In mv_capabilities_table table is an information that REFRESH_FAST is not possible:
capability_name possible msgno
REFRESH_FAST_AFTER_INSERT N 2153
REFRESH_FAST_AFTER_INSERT N 2042
I have polish msgtxt, so I will write my own translation here:
2153 --> "inline" view or subquery in FROM list is not supported for this type of mview
2042 --> view or subquery in FROM list
I don't understand this, because none of this messages are true in my mview's SELECT statement.
Please help!

This post has been answered by Jonathan Lewis on Dec 8 2021
Jump to Answer

Comments

Post Details

Added on Dec 8 2021
7 comments
473 views