Solved

Oracle -  CREATE OR REPLACE MATERIALIZED VIEW

Posted on 2007-03-25
5
34,733 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

705 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

20 Experts available now in Live!

Get 1:1 Help Now