Solved

Oracle -  CREATE OR REPLACE MATERIALIZED VIEW

Posted on 2007-03-25
5
37,713 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 143

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

713 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