Infrastructure Software

Hello,

I have seen the same problem when using fast refresh materialized view that uses mview log also.

Do an explicit  select privilege for the mlog:

grant select on mlog$_<master_table> to <mview_schema>

https://deeparaja.wordpress.com/2012/07/17/materialized-view-fast-refresh-fails-with-ora-12018-ora-00942/

thank you for the answer and the link: but in my case the owner of table and mview is the same and the ora-942 is not related to the accessed table, but to an internal object and furthermore a transient object, that cannot be found in the dictionary at all (so the ora-942 makes sense - but not the idea to gather statistics for this object at all).

Hi Martin,

Have you tried the OPT_PARAM('optimizer_dynamic_sampling', '0') hint?

Regards,

Bob

Would be interesting to know what fix is responsible

https://blog.dbi-services.com/ofe-optimizer-features-enable/

https://blog.tanelpoder.com/posts/scripts-for-drilling-down-into-unknown-optimizer-changes/

trying on a 12.2 with SYS user (disabling query rewrite) it seems to work correctly without modifying OFE

SQL> select user from dual;

USER

------

SYS

SQL> create materialized view t_mv

  2  disable query rewrite

  3  as

  4  select t.col1, t.col2, r.val

  5    from t,

  6    LATERAL (SELECT MIN(val) AS val

  7              FROM JSON_TABLE ( t.col2, '$[*].target'

  8                                COLUMNS (val NUMBER PATH '$')

  9                                )

10            ) r;

Creata vista materializzata.

SQL> exec dbms_mview.refresh('T_MV');

Procedura PL/SQL completata correttamente.

Hi Martin,

I have the same result but if you change the query as a subquery it works. of course mv does not support scalar subqueries so I created a view to achive that:

create or replace view v_x as 
select t.col1, t.col2, (SELECT min(val) AS val
                         FROM JSON_TABLE ( t.col2, '$[*].target'
                                           COLUMNS (val NUMBER PATH '$')
                                          )
                       ) as val
from t;
drop materialized view t_mv;
create materialized view t_mv
as
select * from v_x;
exec dbms_mview.refresh('t_mv');

it works and when I checked the execution plan of the view I can see that VW_LAT*** view. I hope that gives some idea.

Hi Andrew,

thank you for the hint: I didn't consider this option since the lateral join was a 12c addition (if I recall  this right). This should work in the given context.

Regards

Martin

Hi Donatello,

SYS is special: I would not be surprised to see different strategies, when SYS is concerned.

Regards

Martin

Hi Mustafa,

thank you for adding this solution, which would also be a valid option.

Regards

Martin

in fact, I did not indicate it as a solution but it seemed "strange" to me that without changing the behavior of the optimizer (OFE) it would still work under SYS user.

Regards

strange indeed, but SYS tends to include strange effects.

Dom Brooks wrote:
Would be interesting to know what fix is responsible
https://blog.dbi-services.com/ofe-optimizer-features-enable/
https://blog.tanelpoder.com/posts/scripts-for-drilling-down-into-unknown-optimizer-changes/

I tried the obvious parameter (with lateral in the name) but it wasn't that. I started writing some scripts a while ago to brute force these things with explain plan, but only managed to get as far as it cycling through fix controls (and can confirm there's no fix control in it). Mauro Pagano's Pathfinder (which my scripts mimic) does include hidden parameters but it has to execute the SQL each time and needs to be run as SYS (which doesn't seem to have the problem) https://mauro-pagano.com/category/pathfinder/ . Might be worth kicking off overnight and checking the results in the morning.