Solved

Oracle -  CREATE OR REPLACE MATERIALIZED VIEW

Posted on 2007-03-25
5
35,556 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:LeanMoreTryMore
  • 3
5 Comments
 

Author Comment

by:LeanMoreTryMore
ID: 18790695
Which one is better
ON DEMAND

or refresh every 1 hour

REFRESH START WITH SYSDATE
NEXT SYSDATE + 1/48
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 18790699
I refer to the following source, which does NOT indicate that OR REPLACE is possible, hence I assume that indeed it is not possible:
http://www.psoug.org/reference/materialized_views.html

that is possibly a bug in the documentation or in the implementation...
0
 

Author Comment

by:LeanMoreTryMore
ID: 18790705
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

0
 
LVL 15

Assisted Solution

by:ishando
ishando earned 100 total points
ID: 18790807
REFRESH ON DEMAND will only refresh the data in the MV when a user issues a command to do a refresh.

REFRESH START WITH SYSDATE NEXT SYSDATE + 1/48 will automatically refresh the data in the MV every half hour (1/24 for every 1 hour !!)

As to which is better, that all depends on your application, how you want to use the MV, and how complex and time consuming the refresh is.
0
 

Author Comment

by:LeanMoreTryMore
ID: 18790808
ok. i got your idea. thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now