0

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.
Shawn
  • 1
  • 3
  • 1
    Materialized views only use your SQL (with hints) at the time they are created. On refresh, Oracle generates its own SQL automatically, and it may not match your original in every respect (predicates may change order, hints go away, etc.), as Oracle tries to optimize itself. It is an internal operation and it can't be controlled; Oracle will _always_ generate its own SQL for the refresh. All you can do is optimize performance indirectly with things like current table statistics, appropriate indexes, and memory configuration. – pmdba Sep 26 '21 at 02:35
  • 1
    You could also try creating a _view_ with the hints you want, and base the materialized view on the intermediate view. – pmdba Sep 26 '21 at 02:38
  • Thank you @pmdba! Did not expect get reply so soon. Thanks for the clarification. Your suggestion is a good work around, let's see will there be any other suggestions. Need some time and proof to persuade team to stop here... – Shawn Sep 27 '21 at 02:12

1 Answers1

0

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;
Shawn
  • 1
  • 3