?
Solved

how do I create an oracle materialized view?

Posted on 2006-03-24
15
Medium Priority
?
2,632 Views
Last Modified: 2013-12-11
First of all, I can't seem to create a materialized view containing self-joins AND union all. Is it possible?

I checked Oracle 9i (my version: PL/SQL Release 9.2.0.4.0 - Production) documentation and I get different answers (or so it seems to me).

First I saw this: "The COMPATIBILITY parameter must be set to 9.0 if the materialized aggregate view has inline views, outer joins, self joins or grouping sets and FAST REFRESH is specified during creation..."

Did you see the part about 'self joins' in there? I did and I was pumped because that seems to say that you CAN have 'self joins' (and my compatibility is 9.2...)

BUT

In the very same document I also found "Oracle does not allow self-joins in materialized join views." (rage)

You can see the document I am speaking of here: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#574889

Whenever I try to create the mview I get the following error: ORA-01446 cannot select ROWID from view with DISTINCT, GROUP BY, etc.

That's because the oracle documentation says I need to use rowid in my mview if I am using 'union all'
0
Comment
Question by:tomandlis
  • 9
  • 6
15 Comments
 
LVL 16

Expert Comment

by:RCorfman
ID: 16282558
I would build this up in pieces. If you are doing a union all as a subquery, then trying to self-join to it, this clearly isn't allowed becuase of the rules on the union all fast refresh materialized views.  You don't have to specifically use rowid with union all, they just have to be able to uniqueily identify each row at the top level of the union all.

you can do a self join in a materialized view, but not in a fast refresh materialized view...
  1  create materialized view udtmv
  2  refresh fast on demand as
  3* select t1.id,t2.id2 from udttemp t1, udttemp t2 where t1.id=t2.id
SQL> /
select t1.id,t2.id2 from udttemp t1, udttemp t2 where t1.id=t2.id
                         *
ERROR at line 3:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL> create materialized view udtmv as
  2  select t1.id,t2.id2 from udttemp t1, udttemp t2 where t1.id=t2.id;

Materialized view created.

SQL>

0
 

Author Comment

by:tomandlis
ID: 16283490
Here is essentially what I want to do.  BTW, I tried encapsulating the self-joining-union all-mview in a subquery (per Oracle recommendation) and it still bombed.  I even included the 'umarker' and aliased 'rowid' fields.  (I'll post that try next):

See if you can use the sample table below.  The 'insert' stmt is essentially what I want the mview to look like.

DROP TABLE CAF.peck CASCADE CONSTRAINTS;

CREATE TABLE CAF.peck
(
      peckid         integer       not null      primary key
      ,x1  integer                  not null
      ,x2    integer        not null
);

insert into peck(peckid, x1, x2)
values (3, 1, 20);

insert into peck(peckid, x1, x2)
values (4, 1, 0);

insert into peck(peckid, x1, x2)
values (8, 2, 21);

insert into peck(peckid, x1, x2)
values (9, 2, 0);


--roll-up the hierachy into a self-referencing sql table
DROP TABLE CAF.peckMview CASCADE CONSTRAINTS;

--**********************************************************
--this table is what I would like my fast updating materialized view to look like
--**********************************************************  
CREATE TABLE CAF.peckMview
(
      peckid_parent         integer      not null
      , peckid_child         integer      not null
      , constraint peck_pk_p_c primary key (peckid_parent, peckid_child)
) organization index;


--roll-up the hierachy into a self-referencing sql table

--x1 join to itself
insert into caf.peckMview (peckid_parent, peckid_child)
select peckid, peckid
from peck
where x2 = 0  --if x2 is zero then this is a top node to
union all
--x1 x x2
select p1.peckid, p2.peckid
from peck p1
join peck p2
on p1.x1 = p2.x1
where p1.x2 = 0
and p1.peckid <> p2.peckid



0
 

Author Comment

by:tomandlis
ID: 16283658
Here is my create stmt for the above example.  Oracle won't let me make an 'on commit' mview containing self-joins and or union all

--I think the p1 rowid is what oracle wants
CREATE MATERIALIZED VIEW mview_peck
refresh fast on commit
AS
(select rowid crid, peckid, peckid, 1 umarker
from peck
where x2 = 0  --if x2 is zero then this is a top node to
union all
--x1 x x2
select p1.rowid crid, p1.peckid, p2.peckid, 2 umarker
from peck p1
join peck p2
on p1.x1 = p2.x1
where p1.x2 = 0
and p1.peckid <> p2.peckid
)

--cannot set the ON COMMIT refresh attribute for the materialized view
--ora-12054
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tomandlis
ID: 16283776
If you take a look at the "Restrictions on Fast Refresh on Materialized Views With the UNION ALL Operator" section of oracle 9i documentation (chapter 8).  You will see the criteria for doing a 'fast refresh' union all materialized view.

Here is a link: http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96520/mv.htm

I believe after reading those requirements you will find that the view defined above meets the requirements, yet it still doesn't work.

"The following error has occurred:
ORA-12015: cannot create a fast refresh materialized view from a complex query"
0
 
LVL 16

Accepted Solution

by:
RCorfman earned 1500 total points
ID: 16284297
It has nothing to do with the union all, it is the self-join that is causing the problem....
  1  CREATE MATERIALIZED VIEW mview_peck
  2  refresh fast
  3  AS
  4  select p1.rowid crid, p1.peckid, p2.peckid peckid2, 2 umarker
  5  from peck p1
  6  join peck p2
  7  on p1.x1 = p2.x1
  8  where p1.x2 = 0
  9* and p1.peckid <> p2.peckid
SQL> /
from peck p1
     *
ERROR at line 5:
ORA-12015: cannot create a fast refresh materialized view from a complex query
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16284327
I overrode the name on the second peckid to peckid2 as this would cause a problem just creating a normal view, I was trying to get it down to the smallest problem possible. Looks like you can't create a fast refresh materialized view with a self-join.

I agree there is a problem with the documentation in the first statement that 'inferred' you could do this. but the other statement explicitely says you can't... looks that that one wins.  Unfortunately , you can't do it....
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16284379
I thought I'd try to trick it and do a fast refresh of a normal join, where one-side was a materialized view... doesn't seem to allow that either...  :(

SQL> create table udttemp1 ( id number primary key, val number);

Table created.

SQL> create materialized view log on udttemp1;

Materialized view log created.

SQL> create materialized view udttemp2
  2  refresh fast on commit as
  3  select * from udttemp1;

Materialized view created.

SQL> create materialized view log on udttemp2;

Materialized view log created.

SQL> create materialized view udttemp3
  2  refresh fast on commit as
  3  select t1.id, t2.id id2, t2.val
  4  from udttemp1 t1, udttemp2 t2
  5  where t1.val=t2.id;
from udttemp1 t1, udttemp2 t2
     *
ERROR at line 4:
ORA-12052: cannot fast refresh materialized view AUBURN.UDTTEMP3


SQL>
0
 

Author Comment

by:tomandlis
ID: 16304840
If it can't be done then can someone suggest a workaround?  I'm just trying to improve performance of a query that uses a snapshot like the mview above.  If the mview can't be up to the minute then I'll just have to deal with it. :-(

Here is the query.  Using the example above (with the renamed columns peckid1, peckid2 - nice catch ;-)) the query I am trying to optimize looks like this:

select *
from peck
where peckid in (select peckid2 from mview_peck where peckid1 = :mypeckid)

where 'mypeckid' is the parameter
0
 

Author Comment

by:tomandlis
ID: 16305061
I tried doing the following index, but it didn't get used in my explain plan for the above query.  

CREATE INDEX mv_ix_peck
ON mview_peck('peckid1', 'peckid2');

0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16307540
It should have if the table is of any substantial size. If the table is small, Oracle will often determine that it is cheaper to do a serial read than use an index. If you believe the index should be used, be sure to run dbms_stats on the table with cascade to assure Oracle understands the makeup of the table so it can make good decisions on the query plans...
0
 

Author Comment

by:tomandlis
ID: 16312605
when you say 'table' above I assume you are referring to the mview table.  

To ensure the index would be used I inserted 3000 rows of sample data (that I made in excel) and then recreated the materialized view then I ran:

CREATE INDEX mv_ix_peck ON mview_peck('peckid1', 'peckid2')

where 'mview_peck1' is the materialized view.  Oracle informs me that it created the index and then I ran the sql above, but the explain plan didn't use the index.  

all my other indexes work w/o having to do dbms_stats, but if you think I should use it, what is the syntax for executing this on a mview? (I don't want to do it on the whole db)


0
 

Author Comment

by:tomandlis
ID: 16312640
hmm, do I need to remove the quotes around peckid1 & peckid2
0
 

Author Comment

by:tomandlis
ID: 16312716
ah, yes, that did it.   OK, well, I keep answering my own questions, but I'll give the points to you (rcorfman) since you are the only one helping if you can confirm that the following is correct mview is correct (per our example) and will refresh itself automatically every day.  (please also let me know the syntax for the dbms_stat on a mview)

CREATE MATERIALIZED VIEW mview_peck1
refresh complete
start with sysdate
next sysdate+1
enable query rewrite
AS
(select peckid peckid1, peckid peckid2
from peck
where x2 = 0  --if x2 is zero then this is a top node to
union all
--x1 x x2
select p1.peckid peckid1, p2.peckid peckid2
from peck p1
join peck p2
on p1.x1 = p2.x1
where p1.x2 = 0
and p1.peckid <> p2.peckid
)


CREATE UNIQUE INDEX mv_ix_peck1 ON mview_peck1(peckid1, peckid2)

0
 

Author Comment

by:tomandlis
ID: 16312750
btw, that was pretty wierd that oracle created an index when I had quotes around 'peckid1' and 'peckid2'.  YOu think it would bomb, but it just said 'yep, I made it' and then didn't bother to use it.  I can see that it wouldn't use it, because those columns don't exist (with quotes around them), but it should have bombed in the first place.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16312906
I agree, I didn't even notice that with the single quotes. Sorry, I was in a meeting. I do have a 'real job' too ;) dbms_stats still holds, but wasn't relevant.

I'm going to play with the index creation with the quotes... I agree, should have choked on it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

830 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