Home » SQL & PL/SQL » SQL & PL/SQL » Materalized view (oracle9i,Linux)
Materalized view [message #298851] Fri, 08 February 2008 00:39 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,
I got error while creating a materalized view based on the grants.

scripts:

CREATE MATERIALIZED VIEW EMPLOYEES
TABLESPACE TSTSMLTBS
NOCACHE
LOGGING
NOPARALLEL
USING INDEX
TABLESPACE TSTSMLTBS
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT *
FROM MASTER.EMPLOYEES;
ERROR :ORA-00942: table or view does not exist

i sure that employees table is present in master schema.

but i comment the ENABLE QUERY REWRITE option, the materalized view created.

what is the reason ?
what is the meant by ENABLE QUERY REWRITE?
Re: Materalized view [message #298852 is a reply to message #298851] Fri, 08 February 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
CREATE MATERIALIZED VIEW page
Prerequisites section.

Regards
Michel

Re: Materalized view [message #298868 is a reply to message #298851] Fri, 08 February 2008 01:27 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,
Now, i created a materalized view based on the DB link with
enable query rewrite option.

scripts:
CREATE MATERIALIZED VIEW EMPLOYEES
TABLESPACE TSTSMLTBS
NOCACHE
LOGGING
NOPARALLEL
USING INDEX
TABLESPACE TSTSMLTBS
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT *
FROM EMPLOYEES@masdb;

It's working fine.

what is the reason?

Re: Materalized view [message #298870 is a reply to message #298868] Fri, 08 February 2008 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have the correct privileges in this case and not inthe previous one.

Regards
Michel
Re: Materalized view [message #298894 is a reply to message #298851] Fri, 08 February 2008 03:16 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,
which one is better performance whether db link or grant?
Re: Materalized view [message #298903 is a reply to message #298894] Fri, 08 February 2008 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer seems obvious, doesn't it?

Regards
Michel
Re: Materalized view [message #299202 is a reply to message #298851] Sun, 10 February 2008 11:03 Go to previous message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
hi,
i have compared materalized view based on the db link and grants..Materalized view based on the db link is good performance compare than grants.
Previous Topic: Update values
Next Topic: convert data
Goto Forum:
  


Current Time: Fri Dec 09 19:19:56 CST 2016

Total time taken to generate the page: 0.07446 seconds