Solved

Oracle -  CREATE OR REPLACE MATERIALIZED VIEW

Posted on 2007-03-25
5
40,064 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
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 shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

635 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