Oracle materialized views: 25/24 in REFRESH / NEXT parameter-CodePudding

I'm trying to figure out the meaning of 25/24 in this REFRESH parameter of an Oracle materialized view found on a repository:

REFRESH FORCE ON DEMAND START WITH sysdate 0 NEXT trunc(SYSDATE) 25/24

I'm trying to find the correct refresh scheduling of this Oracle materialized view: 1/24 means "every one hour' but I'm not sure about the meaning of 25/24 and I couldn't find the correct answer so far...

Can anyone give an help? Thanks in advance.

Marco

CodePudding user response：

That's tomorrow, 1 hour after midnight:

SQL> select sysdate right_now,
  2         trunc(SYSDATE)   25/24 what_is_that
  3  from dual;

RIGHT_NOW           WHAT_IS_THAT
------------------- -------------------
06.12.2022 12:20:53 07.12.2022 01:00:00

SQL>

trunc "removes" time component from sysdate (sets it to midnight) and then adds 25 hours to it.

CodePudding user response：

From the CREATE MATERIALIZED VIEW documentation

NEXT Clause

Specify a datetime expression for calculating the interval between automatic refreshes.

Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then the database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then the database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the create_mv_refresh entirely, then the database does not automatically refresh the materialized view.

Your statement:

REFRESH FORCE ON DEMAND START WITH sysdate 0 NEXT trunc(SYSDATE)   25/24

is effectively saying refresh every X hours where X is the difference between the time now (SYSDATE 0) and the tomorrow at 01:00 (TRUNC(SYSDATE) gives midnight today and then 25/24 adds 25 hours). Thus if you execute the CREATE MATERIALIZED VIEW statement at exactly midnight then it will refresh every 25 hours, if you execute it at 9am then it would refresh ever 16 hours, etc.

If you wanted to run it every 25 hours then you can remove the TRUNC:

REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE   25/24

or, equivalently, using an INTERVAL literal rather than arithmetic:

REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE   INTERVAL '25' HOUR

NEXT Clause

`NEXT` Clause