Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle -  CREATE OR REPLACE MATERIALIZED VIEW

Posted on 2007-03-25
5
Medium Priority
?
41,639 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 450 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 300 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

670 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