Materialized view creation is fast but refresh takes hours on Oracle 19c

Question

Recently I need to create several materialized views on Oracle 19c, all of the base tables are locate on a remote oracle database, the query uses dblink to connect the tables, and fast refresh is not allowed. Most of them can complete refreshment in seconds after add some hints on them, like use_hash etc. But when create the one with union on the query, the hints do not work at all, luckily there is only one union, so I split the query to two parts, but another issue emerges, one of the materialized view only takes no more than 10 seconds for creation, but it takes hours even days cannot complete refreshment. I searched on web and got below answers:

1. use dbms_mview.refresh(mv_name, 'C', atomic_refresh=>false). This solution does not work.
2. fast refresh. The solution does not allowed.
3. Instead to do refresh, re-create the materialized view every time, it is a workaround, but not a solution.
4. Use hint optimizer_features_enable(9.0.0), I simulated the issue on a table (as I cannot insert into...select... on materialized view), seems the hint does work, but when I tried to apply the hint on the materialized view, from the execution plan I can see that the hint has been ignored. I also tried to add alter session set optimizer_features_enable='9.0.0' on scheduler job before the dbms_refresh.refresh(mv_name), but it does not work. Would like to know are there anyone have any idea on this problem? Thank you.

Answer 1

Jonathan from oracle community just gave me a solution for my specific query. As all the fields of my query come from remote database except the systimestamp function, so I can separate the function to the outer select statement and make all the remote fields as sub-select statement then add no_merge hint to it, this will make remote database optimizer come to play.

SELECT systimestamp, v.*
  FROM (
    my original query with /*+ no_merge */
) v;