Hi There,
Good day!
Recently I am creating several materialized views on Oracle 19c which all base tables come from one single remote database through dblink, and all of the MVs schedule refresh everyday.
For most of the MVs I add some hints like use_hash to reduce the refresh time, but the hint does not work on one of them, the strange thing is the mv only take no more than 10 seconds for creation, but it takes hours even days to do refresh, there are only no more than 1 thousand records. Someone saying use hint optimizer_features_enable('9.0.0') will solve the problem, I copied the insert into ... select ... statement from the MV refresh execution plan and do an experimental on a table, seems it does work, but when I am trying to add the hint on the MV, it does not work at all, I also tried to add clause alter session set optimizer_features_enable='9.0.0' in the refresh job, it does not work too.
Anyone have idea to solve this question? There are some work around list below, but still would like to know whether can make the MV refresh fast directly.
Instead do fresh, re-create the MV everyday.
Create a staging table, then make MV refresh data from it.
Do fast refresh, actually I have not make a try, as we are not going to create any MV log table on remote database.