Home » SQL & PL/SQL » SQL & PL/SQL » Ora10g MVs & QR
Ora10g MVs & QR [message #186610] Tue, 08 August 2006 14:18 Go to next message
adminme
Messages: 31
Registered: May 2006
Member
I have reviewed:
ora docs
www
searched orafaq, and
tested code

Is it possible to create a MV based on more than 2 tables?

Does the SET operator union all impact this? I know this is a "complex query": it is not a fast refresh.

[Updated on: Tue, 08 August 2006 14:41]

Report message to a moderator

Re: Ora10g MVs & QR [message #186629 is a reply to message #186610] Tue, 08 August 2006 15:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do I not understand what you are talking about? Or does this example (Oracle 10g) answers your doubts?
SQL> CREATE MATERIALIZED VIEW mv_emp_Dept AS
  2  SELECT d.dname, e.ename, e2.job
  3  FROM EMP e, DEPT d, EMP e2
  4  WHERE e.deptno = d.deptno
  5    AND e.empno = e2.empno
  6    AND d.deptno = 10
  7    UNION ALL
  8  SELECT d.dname, e.ename, e2.job
  9  FROM EMP e, DEPT d, EMP e2
 10  WHERE e.deptno = d.deptno
 11    AND e.empno = e2.empno
 12    AND d.deptno = 20;

Materialized view created.

SQL>
Re: Ora10g MVs & QR [message #186656 is a reply to message #186629] Tue, 08 August 2006 22:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As long as its not FAST refresh, I think you can do pretty much anything.

But you asked about Query Rewrite in the title of your post - there you will find restrictions.

Ross Leishman

[Updated on: Tue, 08 August 2006 22:25]

Report message to a moderator

Re: Ora10g MVs & QR [message #186789 is a reply to message #186610] Wed, 09 August 2006 09:54 Go to previous message
adminme
Messages: 31
Registered: May 2006
Member
Thank you for replying. My MV(materialized view) worked. The dev tool I'm using periodically gets quirky so I went to SQLPlus.

I am concerned with another issue on this MV & QR (query rewrite)in using Sequence numbers.

Should I create a new post?

thanks

Previous Topic: Error during fetching data in cursor
Next Topic: Varrays
Goto Forum:
  


Current Time: Sun Dec 11 08:29:26 CST 2016

Total time taken to generate the page: 0.07279 seconds