LeanMoreTryMore
asked on
Oracle - CREATE OR REPLACE MATERIALIZED VIEW
I want to create/replace the materialized view but i got the following error
ORA-00922 - Missing or invalid option
CREATE OR REPLACE MATERIALIZED VIEW payroll_mvw
REFRESH ON DEMAND
AS SELECT payroll_no
,employee_no
,employee_name
,department
FROM payroll
WHERE payroll_date >= to_date('01-JUL,2000','DD- MON-YYYY')
ORDER BY employee_no
========================== ========== ========== ========== ========== ========
I refer to the following link=
http://www.oracle.com/technology/oramag/oracle/05-mar/o25data.html
I run ok if i remove the REPLACE, like below
CREATE MATERIALIZED VIEW payroll_mvw
REFRESH ON DEMAND
AS SELECT payroll_no
,employee_no
,employee_name
,department
FROM payroll
WHERE payroll_date >= to_date('01-JUL,2000','DD- MON-YYYY')
ORDER BY employee_no
========================== ========== ========== ========== ========== ========== ======
My question is why i can't use 'CREATE OR REPLACE', but the oracle's example indicates we can use
ORA-00922 - Missing or invalid option
CREATE OR REPLACE MATERIALIZED VIEW payroll_mvw
REFRESH ON DEMAND
AS SELECT payroll_no
,employee_no
,employee_name
,department
FROM payroll
WHERE payroll_date >= to_date('01-JUL,2000','DD-
ORDER BY employee_no
==========================
I refer to the following link=
http://www.oracle.com/technology/oramag/oracle/05-mar/o25data.html
I run ok if i remove the REPLACE, like below
CREATE MATERIALIZED VIEW payroll_mvw
REFRESH ON DEMAND
AS SELECT payroll_no
,employee_no
,employee_name
,department
FROM payroll
WHERE payroll_date >= to_date('01-JUL,2000','DD-
ORDER BY employee_no
==========================
My question is why i can't use 'CREATE OR REPLACE', but the oracle's example indicates we can use
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
What is the difference between ON DEMAND and REFRESH every hours
AND
which one is better
ON DEMAND
or refresh every 1 hour
REFRESH START WITH SYSDATE
NEXT SYSDATE + 1/48
What is the difference between ON DEMAND and REFRESH every hours
AND
which one is better
ON DEMAND
or refresh every 1 hour
REFRESH START WITH SYSDATE
NEXT SYSDATE + 1/48
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok. i got your idea. thanks
ASKER
ON DEMAND
or refresh every 1 hour
REFRESH START WITH SYSDATE
NEXT SYSDATE + 1/48