Home » Server Options » Replication » MV Start With Parameter (Oracle, 12.2.0.1.0, Linux)
MV Start With Parameter [message #686635] Mon, 07 November 2022 09:42 Go to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
I am trying to understand how the "Start With" parameter works with a MV.

I've set the "Start With" with TO_DATE('07-11-2022 09:23:00','dd-mm-yyyy hh24:mi:ss') but I've seen the time be later on different refreshes. I'm expecting the TIME to be one hour later at 23 minutes past the hour. That doesn't seem to hold true. Sometimes it's one hour and 30 minutes.

Does the time reset based on when it completes so if the refresh takes 45 minutes then for the next hour it would be 10:45 and so on. As I have mentioned, I'm expecting the refresh to happen 23 minutes past the hour for every hour (9:23, 10:23, 11:23 and so on).

I would like to set the date/time (07-11-2022 8:05) and the refresh would then be 9:05, 10:05, 11:05 and so on without the time moving around (10:15, 11:23...etc).



CREATE MATERIALIZED VIEW DATE_MV 
    (DATA_DATE)

NOCACHE
NOLOGGING
NOCOMPRESS
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('07-11-2022 09:23:48','dd-mm-yyyy hh24:mi:ss')
NEXT sysdate + 1/24              
WITH PRIMARY KEY
AS 
(select syddate data_date
   from dual);


Re: MV Start With Parameter [message #686636 is a reply to message #686635] Mon, 07 November 2022 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the refresh lasts less than 37 minutes:

NEXT TRUNC(SYSDATE,'HH24') + 1/24 + 23/(24*60)

[Updated on: Mon, 07 November 2022 09:54]

Report message to a moderator

Re: MV Start With Parameter [message #686637 is a reply to message #686636] Mon, 07 November 2022 11:22 Go to previous message
Duane
Messages: 508
Registered: December 2002
Senior Member
Ah, so I was doing something wrong. I see what you are doing. Nice. Thanks.
Previous Topic: GG where clause confusion
Goto Forum:
  


Current Time: Sat Jan 28 10:27:52 CST 2023