oracle materialized view refresh time

Question

hey all,

anyone able to tell me how often a materialized view is set to refresh with the following setting plz?

REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT (round(sysdate) + 1/24) + 1

i think i read it as every hour but i'm not sure

Answer 1

SQL> alter session set nls_date_format = 'yyyy-mm-dd :hh24:mi:ss';

Session changed.

SQL> select sysdate from dual;

SYSDATE
--------------------
2008-12-19 :12:18:28

SQL> select (round(sysdate) + 1/24) + 1  from dual;

(ROUND(SYSDATE)+1/24
--------------------
2008-12-21 :01:00:00

Answer 2

I'm not 100% sure that it's legal in a materialized view scheduling statement, but you might like to try the (arguably) more intuitive INTERVAL specification:

round(sysdate) + interval '1 1' day to hour

Other examples here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#SQLRF00221

Answer 3

To answer your first question (will this run once an hour?):

Nope, this will run once when you create it because of this clause:

START WITH sysdate+0

Personally, I think the "+0" is extraneous, as now is now.

Then it will run tomorrow at 1 a.m., because of the following clause:

NEXT (round(sysdate) + 1/24) + 1

The "1/24" part calculates when 1 a.m. is, since Oracle dates are actually stored as numbers, with the decimal part indicating hours, minutes, etc. The syntax is just fine.