How to create materialized view to refresh every X hours?
Hi, at the time of a materialized view creation, I want to specify that it should be refreshed every 6 hours starting the next day at 12am, 6am, 12pm, 6pm and so on. I'm a little confused on how to do this using the NEXT clause. The documentation says for the NEXT clause to "Specify a datetime expression for calculating the interval between automatic refreshes." I'm not sure if the refresh interval is calculated by taking the difference between the NEXT and START WITH times? Would the following syntax be correct for what I want to do?
CREATE MATERIALIZED VIEW test_mv REFRESH COMPLETE START WITH TRUNC(SYSDATE+1) NEXT TRUNC(SYSDATE+1)+6/24 as ....
I am not sure if this will only refresh once a day at 6am. I guess I can wait to see for sure, but hopefully, I can get an answer here faster. If the above is not correct, can someone offer a suggestion? Thanks.