Oracle OpenWorld 2009, the last three days

Tuesday was a very rainy day. I decided to take the shuttle bus to the Moscone and was therefore a little late for the first session. The sessions I visited this day were:

Dan Morgan - What's New in Eleven ... Dot Two (that Oracle won't be talking about)
In every release there are like two types of features. On the one side there are features that are picked up by marketing and which are given catchy names. On the other side are the features that are not picked up by marketing. Dan talked about those. Some I recognized because they are mentioned in the New Features Guide, but he also mentioned a few others. Dan is involved in the beta program, so he has already worked with 11g Release 2 for a year now.

Richard Foote - Oracle Indexes: Q & A Discussion
Richard had only two slides which he showed in the first minutes, and then it was time to throw questions at him. About indexes of course, and David Bowie. Jonathan Lewis was also in the room, so they kind of answered the questions together. One of the things that was nice to hear, is that neither Richard nor Jonathan had seen a useful use for the skip scan. Which was something I already suspected.

Steven Feuerstein - Coding Therapy for Software Developers
Another great presentation by Steven. His presentation skills are simply awesome. The subject of the presentation helps of course. However, there were a few statements here that I strongly disagree with. Statements like "SQL statements with 3 table joins, or 7; that's crazy!" or "It doesn't make sense for developers to have to learn the ins and outs of tuning SQL!". Then who is going to write that transactional API or that PL/SQL interface or batch program? I suspect that Steven had some other context in mind when making those statements.

Raj Mattamal - Giving Your Oracle Application Express a Web 2.0 Face-Lift
Raj talked about how to make your APEX application cooler for the end user. With so much jquery code out there, it's easy to do more than just the standard APEX stuff. It makes a big difference in how you interact with the application. Raj did a live demonstration, which I always like. And, man, does Raj talk fast!

Andrew Holdsworth - Current Trends in Real-World Database Performance
This seems to be an annual session, but it was the first one I attended. It's nice to hear from the people who do performance stuff only, what they encounter most. It seems bad SQL is still number one.

Blogger Meetup
Alex Gorbachev organized the annual bloggers meeting this year. And he made it a special one with t-shirts saying "I met more bloggers than you", where everybody was writing their names on the t-shirts. The wearer of the shirt with the most names won a HP Netbook. This is a great event for meeting the people behind the blogs you read (or don't read yet).


Wednesday I really tried to get some more sleep. Again I was awake at 4 AM, about the same time as every morning here in San Francisco. But unfortunately just staying in bed until late in the morning did not help. The sessions I followed were:

Arup Nanda - Upgrade Case Study: Database Replay, Snapshot Standby, and Plan Baselines
This session mainly outlined the use of new technologies you can use for upgrading your database to 11g. Unfortunately for me, the technologies themselves were not explained here; it was about when to apply which technology.

Rory McLean - Using Row-Level Security with Oracle Application Express
Last year I swore not to enter sessions anymore when I don't know the speaker, but this session was only 30 minutes and there wasn't anything more interesting to choose from at that time, so I decided to give this one a shot. And I wasn't disappointed. Rory McLean gave a nice and well-prepared presentation, including a demonstration of VPD within APEX. I didn't learn much new though, but that's because I'm already familiar with the subject.

S. Gopalakrishnan - Keynote: Seven Game Changing Trends: How Prepared Are You?
I realized too late that, to have a good press/analysts/blogger seat for Larry's keynnote, you'd have to be in the room before this one started. When Andrew Clarke and I arrived at Moscone North's hall D, the room was already full, except for some seats at the side. So we ended up behind a giant pillar just watching the big screens. This first keynote was aimed at managers to give some high-level overview about the strategic challenges they are awaiting. Nothing much of interest to me.

Larry Ellison and others - Keynote
Larry's keynote did not have a big new introduction, as expected. He first mentioned the success of Oracle Enterprise Linux. Then he went on to talk about Exadata V2 for OLTP databases. Some impressive numbers were there. I think my current client should definitely check this one out. After they have migrated to 11g Release 2 of course ... And he repeated the $10M challenge and the attack on IBM, which he also did at the Sunday keynote. The third part was about introducing pro-active Oracle support. If you give Oracle all the data about your configuration, then Oracle will inform you about which patches you should run to avoid running into the same problems as other customers did. The fourth part was about the new Fusion Applications. This software is still not ready to ship, but it looked they are well on their way. Fusion Applications will be the successor of E-Business Suite, Siebel, Peoplesoft, JD Edwards and the like. Oracle will support those applications for a long long time, but they just offer an alternative based on modern technologies like SOA.

The highlight of the keynote was of course the appearance of the governor of California, Arnold Schwarzenegger. He did a very funny talk about the importance of technology. How it had helped him in his career and how important it is to reduce global warming.


The keynote was a long show that should have ended at 16:30, but ended at 17:20. I therefore missed "SQL gone wild: Taming Bad SQL the Easy Way (or the Hard Way)" by Benoit Dageville about tuning SQL with some automated tools.

Appreciation Event
Andrew invited me to join him and Marco Gralike, Jacco Landlust, Anjo Kolk, Tim Hall and Doug Burns to the Appreciation Event on Treasure Island. An evening with music performances by Aerosmith, Roger Daltrey and the Wailers and two other bands I did not see. Normally I don't fancy Aerosmith's music that much (with the exception of "Dream On"), but I thought they gave a great show. Roger Daltrey played a couple of songs I did not recognize, but when he played some of The Who classics, the enthusiasm of the crowd -and mine- was greatly increased. After losing everyone of the original group and having a peek at the Wailers, I called it a day.


I was back at the hotel at 1AM. And again awake at 4AM. My own Unconference session started at 10AM (more about that in a separate post). After my session I returned my laptop to my hotel and went back to see "Real-World Database Performance Roundtable" by five people, among whom Greg Rahn, Graham Wood and Andrew Holdsworth. It was a really nice Q&A session with some interesting questions from the audience. But I was so tired that when I closed my eyes for a moment, I actually started sleeping. Some internal mechanism woke me up instantly and I decided I had to leave before I started disrupting the session with some loud snoring ... I grabbed a lunch, had a quick talk with H.Tonguç Yılmaz and Husnu Sensoy (DBA of the year!), and took some photographs to include in a presentation about Oracle OpenWorld for my department. I went back to my hotel and slept from 2PM to 7PM. Finally ...

Oracle OpenWorld 2009, the first two days

This year I'm visiting Oracle OpenWorld for the second time. Everything is now familiar, except for two differences. First one is the location of Oracle Develop, which has moved back to the Hilton Hotel, instead of the nearby Marriot Hotel. It is a nice walk, but sometimes you cannot chat after a session if you have a next session at the Moscone Center. Second difference is that it seems to me less people are attending the conference than previous year. But it's still a lot of people. One thing hasn't changed: my excitement about the conference.

The first two days were very busy. I'll quickly describe the sessions I went to. Sunday started early at 8:30 AM.

Alex Gorbachev - Demystifying Oracle Real Application Clusters Workload
A topic that with which I am not too familiar: my shop doesn't use RAC and I'm not a DBA. So I thought to use this one to get to know a little bit more. I had some trouble getting the context right in the beginning of the session, but when Alex started demonstrating the stuff on his laptop, everything fell into place. The demos were simply great. The sometimes complex load balancing algorithms were made perfectly clear by showing exactly the right things. A good start of the conference.

Steven Feuerstein - Why you should care about PL/SQL in Oracle Database 11g Now!
This was the first time I attended a session by Steven Feuerstein. Although I heard nothing new about PL/SQL, it was great to see how he presented. Very interactive and with a lot of humour.

Dimitri Gielis - Mastering an Oracle Application Express Page
In this session we learnt about what happens when APEX shows or processes a page. The session really helped to get some of my thoughts straight. One of the new things I heard was a simple button on the page design screen called "View" where you can see the order of the events that will happen. I saw that page lots of times now, but never bothered to use that.

Andrew Clarke - Designing PL/SQL with Intent
This session was totally different than I expected. It was a rather philosophical one comparing design patterns in PL/SQL with design patterns in town planning. At the end a few PL/SQL examples were shown that I liked a lot, since I preach them myself often as well, although not as well as Andrew did.

Michael Hichwa & David Peake - Oracle Application Express 4.0
A lot of nice new features were presented from the APEX development team themselves. The ones I liked most were Dynamic Actions, with which you can create client side validations declaratively, and the APEX listener. I'm not entirely sure, but it looks like the APEX listener opens up easier and supported ways to have proxy database authentication. Other new features include integrated Team Development for feedback and bug reports and the like, improved charts, websheets applications and improved tabular form handling.

Bryn Llewellyn - Online Application Upgrade
This session was renamed to "Edition Based Redefinition". Bryn very concisely talked about the 11g Release 2's killer feature: editions. Without using a demo he managed to make it perfectly clear what the feature is about and what it is not about. A few random things I wrote down: from now on an object is not defined by owner and name but by owner, name and edition. Only synonyms, views and PL/SQL of all kinds are editionable. You need to rename the table, create an editioning view with the same name as the table's former name. Then move all triggers, all privileges and all VPD policies to the editioning view. And last but not least: in 11gR2 it's now possible to add an extra column while the table is in use, whereas previously you would get an ORA-04021 lock timeout message.

Scott McNealy - Extreme innovation
This keynote was mainly aimed at assuring all Sun customers that Oracle is not trying to kill Sun's products but instead Oracle is increasing the investments in Sun's products. Larry Ellison did a good job at depicting IBM's range of hardware as inferior, slow, and not very green. He even offers $10M if your Oracle application doesn't run at least twice as fast. Now that's confidence!

ACE dinner
An evening at the Sens Restaurant for all Oracle ACEs and ACE Directors, where I've met a lot of old and new friends. Thanks to Lillian and all others at OTN for the perfect organization and a nice evening!


On Monday I attended these sessions:

Jonathan Lewis - How to become an expert
A very entertaining story about what you should do to become an expert. My rather free interpretation of the answer to this question is, to learn and really understand the basics first. If you understand the architecture of version 6, when you could still carry the documentation with you, then everything afterwards is really just a variation on the same theme. Understand exactly what Oracle will have to do when you send it a SQL statement and you'll be in a good position to know where the root cause of your problem is.

Tom Kyte - All About Metadata: Why Telling the Database About Your Schema Matters
A good story about why should have constraints, even in data warehouses. They may slow down DML a little, but it's extra information for the cost based optimizer to make smarter decisions. Extra access paths will become available. A new thing for me was to see the effect of creating a dimension object in combination with materialized view query rewrites. I have to see that one for myself one time.

Greg Rahn - Chalk & Talk: The Core Performance Fundamentals Of Oracle Data Warehousing
A story about Exadata and data warehouses and benchmarks. A different world than my usual one, which was nice. For example, this exadata machine is so fast, Greg recommends to not even use indexes anymore in most data warehouses. Of course, it all depends. I must say I was impressed by all numbers he mentioned. Those are not numbers I usually see in my clients OLTP database.

Jonathan Lewis - How to Hint
Again a very good presentation in which I heard quite a number of new things. To name a few: use_hash is only half a hint; it's better to combine this hint with the swap_join_inputs hint to explicitly state which query block should act as the in-memory table and which one as the probe table. Jonathan also had a great example of the no_merge hint, which finally became clear to me. And the parallel hint was not what I thought it would be. It's not instructing to always go parallel, but rather: if you are full scanning the table AND it's cost is lower than the serial plan, only then go parallel. Which means there might be a day your query will run serial even if it ran in parallel before, or vice versa. In this session I also learnt how an alien looks like ...

Chen Shapira - Visualization Session - Make your charts more effective for troubleshooting and presenting
This one definitely categorizes as "something completely different". Although the running example was about duration of exports, the session was about how to use graphs effectively and how they can effectively support your message, or not. Luckily I knew just enough about statistics to understand all graphs. I especially liked the Q-Q one.

Lucas Jellema - Continuous Application Application Evolution with Oracle Database 11g Release 2
Exactly the same topic as Bryn Llewellyn's session yesterday. Lucas also had a clear story and even included a few live demos that really helped. A very well explained introduction. The only unfortunate thing about both sessions is that no downsides were mentioned. And there must be several. Like test efforts being multiplied or worse performance because crossedition triggers are row level triggers. And we know what that means ... But still I liked the session for being very clear and the nice metaphores.

OTN Night
Here I just grabbed some food, but I could not find any familiar faces so I decided to leave early and catch some sleep. Which doesn't work by the way: I'm still awake each morning well before 4AM. Which is not uncommon for Europeans, it seems ...

More to come later.

Check constraints and AND

Remember the opening statement of the MV error series? It was "Never put more than one error condition into one error message". Two months later I realized I didn't practice what I preach myself.

I'm currently busy developing a Tour de France application at home. The application has to store the standings. After each stage, a daily and overall standing becomes available for each of the following standing types: Individual, point, mountain and team. I'm storing standing lines and I chose a supertype implementation: all in one table. Which means the table looks something like this:

rwijk@ORA11GR1> create table standing_lines
  2  ( stage_id           number(6)   not null
  3  , standing_type      varchar2(2) not null
  4  , overall_or_daily   varchar2(1) not null
  5  , position           number(3)   not null
  6  , rider_id           number(6)
  7  , team_id            number(6)
  8  , duration           interval day(2) to second(0)
  9  , number_of_points   number(4)
 10  , constraint pk primary key (stage_id,standing_type,overall_or_daily,position)
 11  , constraint ck1 check (standing_type in ('IN','PO','MO','TE'))
 12  , constraint ck2 check (overall_or_daily in ('D','O'))
 13  )
 14  /

Table created.

Note that I deliberately left out some unique, foreign key and other check constraints. For each standing type, different columns should be mandatory and other columns should stay null. For example: an individual standing line should contain a rider_id and a duration, and no team_id and no number_of_points. So it seems logical to code some additional check constraints like this:

rwijk@ORA11GR1> alter table standing_lines add constraint ck3 check
  2    (  standing_type != 'IN'
  3    or (   rider_id is not null
  4       and team_id is null
  5       and duration is not null
  6       and number_of_points is null
  7       )
  8    )
  9  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck4 check
  2    (  standing_type != 'PO'
  3    or (   rider_id is not null
  4       and team_id is null
  5       and duration is null
  6       and number_of_points is not null
  7       )
  8    )
  9  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck5 check
  2    (  standing_type != 'MO'
  3    or (   rider_id is not null
  4       and team_id is null
  5       and duration is null
  6       and number_of_points is not null
  7       )
  8    )
  9  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck6 check
  2    (  standing_type != 'TE'
  3    or (   rider_id is null
  4       and team_id is not null
  5       and duration is not null
  6       and number_of_points is null
  7       )
  8    )
  9  /

Table altered.

But when I started coding routines to read some clob content pasted from the official Tour de France site, parsing all standing lines, and inserting the result into the standing_lines table, the inevitable occurred: it didn't work the first time. I received an error message like this:

ORA-02290: check constraint (RWIJK.CK6) violated

So I looked up the definition of this check constraint and all I could say was that I was trying to insert a team standing line and I gave rider_id a value, or I left team_id null, or I left duration null, or I gave number_of_points a value. That's four options. And it's certainly not one error condition per error message. To resolve this error, I first redefined my check constraints and split them into four each.

The first rewrite is by applying the distributivity rule: A or (B and C) == (A or B) and (A or C). In Oracle check constraint logic, it looks like this:

rwijk@ORA11GR1> alter table standing_lines drop constraint ck3
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck4
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck5
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck6
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck3 check
  2    (   ( standing_type != 'IN' or rider_id is not null )
  3    and ( standing_type != 'IN' or team_id is null )
  4    and ( standing_type != 'IN' or duration is not null )
  5    and ( standing_type != 'IN' or number_of_points is null )
  6    )
  7  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck4 check
  2    (   ( standing_type != 'PO' or rider_id is not null )
  3    and ( standing_type != 'PO' or team_id is null )
  4    and ( standing_type != 'PO' or duration is null )
  5    and ( standing_type != 'PO' or number_of_points is not null )
  6    )
  7  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck5 check
  2    (   ( standing_type != 'MO' or rider_id is not null )
  3    and ( standing_type != 'MO' or team_id is null )
  4    and ( standing_type != 'MO' or duration is null )
  5    and ( standing_type != 'MO' or number_of_points is not null )
  6    )
  7  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck6 check
  2    (   ( standing_type != 'TE' or rider_id is null )
  3    and ( standing_type != 'TE' or team_id is not null )
  4    and ( standing_type != 'TE' or duration is not null )
  5    and ( standing_type != 'TE' or number_of_points is null )
  6    )
  7  /

Table altered.

And now it becomes clear we can split them into four parts like this:

rwijk@ORA11GR1> alter table standing_lines drop constraint ck3
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck4
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck5
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines drop constraint ck6
  2  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck3 check
  2    ( standing_type != 'IN' or rider_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck4 check
  2    ( standing_type != 'IN' or team_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck5 check
  2    ( standing_type != 'IN' or duration is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck6 check
  2    ( standing_type != 'IN' or number_of_points is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck7 check
  2    ( standing_type != 'PO' or rider_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck8 check
  2    ( standing_type != 'PO' or team_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck9 check
  2    ( standing_type != 'PO' or duration is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck10 check
  2    ( standing_type != 'PO' or number_of_points is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck11 check
  2    ( standing_type != 'MO' or rider_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck12 check
  2    ( standing_type != 'MO' or team_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck13 check
  2    ( standing_type != 'MO' or duration is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck14 check
  2    ( standing_type != 'MO' or number_of_points is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck15 check
  2    ( standing_type != 'TE' or rider_id is null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck16 check
  2    ( standing_type != 'TE' or team_id is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck17 check
  2    ( standing_type != 'TE' or duration is not null )
  3  /

Table altered.

rwijk@ORA11GR1> alter table standing_lines add constraint ck18 check
  2    ( standing_type != 'TE' or number_of_points is null )
  3  /

Table altered.

I reran my code and the error message became:

ORA-02290: check constraint (RWIJK.CK17) violated

Ah! I tried to insert a team standing line, but failed to give duration a value. Now that's more like it.

This is a very specific example, but you can say in general:

Whenever you have a check constraint and a top-level AND operator, you should split the constraint.

To know whether your expressions contains a top-level AND operator, it's essential you can calculate with boolean expressions. I mentioned the distributivity rule already above, and in that link you find some more useful rules. And I find I apply De Morgan's laws very often as well. These allow you to rewrite your expressions containing less mandatory brackets, so the AND and OR operators will go up in the hierarchy.

Whether you want to apply this rule for a BETWEEN operator (which also contains an AND :-) ) is subjective.

All About Grouping at OOW and UKOUG

My plan for 2009 was to try to do a presentation about grouping in SQL at both Oracle OpenWorld and at UKOUG. I submitted abstracts for both conferences. Unfortunately, the session was not selected for Oracle OpenWorld. Not very surprising really, since it is about functionality that was introduced in versions 8 and 9, so I can imagine other subjects are more important for Oracle. A bit later I read this post by Jonathan Lewis about the UKOUG conference, where Jonathan writes about more than 200 abstracts for 55 slots and a very high standard this year. So I started assuming that I would not be giving my presentation this year at all.

I did receive some good news though: CIBER management selected two people to visit Oracle OpenWorld, and I was one of them. Thanks Erik and Roel! A week later, I was totally surprised but delighted to receive an email telling that my session was selected for UKOUG. The agenda has been put online a couple of days ago, so you can see the proof here. And I was able to reserve a spot for Oracle OpenWorld's Unconference. So now I will be giving the presentation two times.

Details:
- Thursday 15th October 10 AM, at Oracle OpenWorld, Unconference, Moscone West Overlook I
- Wednesday 2nd December 13:50 - 14:50, at UKOUG Conference Series Technology & E-Business Suite, Track 3, Server Technology stream

You can read the UKOUG abstract here. If you are visiting one of those conferences, I hope to see you there.

Fast refreshable materialized view errors, part seven: a summary

Previous posts:

Fast refreshable materialized view errors, part one (basis MV's)
Fast refreshable materialized view errors, part two: join MV's
Fast refreshable materialized view errors, part three: aggregate MV's
Fast refreshable materialized view errors, part four: union all MV's
Fast refreshable materialized view errors, part five: nested MV's
Fast refreshable materialized view errors, part six: MV_CAPABILITIES_TABLE


This post summarizes the previous posts by reversing the angle. You are developing fast refreshable materialized views and you experience a problem: either an error message appears when creating the MV or the MV gets created but just doesn't fast refresh. What should you do? Normally, the error message should tell you all about it, but as indicated in part one, this isn't always the case with materialized view errors. The list below will hopefully help in resolving your error.

This list may appear pretty complete after you've read all previous entries, but it is certainly not, due to several reasons:
- I could reproduce most error conditions, but not all.
- After having them reproduced, it's not certain the entire restriction was covered.
- There probably are a few undocumented restrictions. I've already encountered a few of them.
- The documentation has shown itself to be outdated and not entirely complete, so you cannot rely on that.
However, I hope to have covered at least the majority of error conditions, so there is a good chance your situation is covered.


ORA-12032: cannot use rowid column from materialized view log on "RWIJK"."MYEMP"

A rather cryptic way to say the rowid column is missing from the materialized view log. I only saw this restriction mentioned for aggregate MV's (restriction 5: All tables in the materialized view must have materialized view logs specified with ROWID), but it should be true as well for union all MV's and join MV's. The error message is quite clear, but if you'd do an explain_mview you'd see the even clear message "mv log must have ROWID" for the
REFRESH_FAST_AFTER_INSERT capability.


ORA-12033: cannot use filter columns from materialized view log on "RWIJK"."MYEMP"

You violated restriction 3 for aggregate MV fast refresh: the materialized view logs must contain all columns from the table referenced in the materialized view. Again I think the error message is worded a little poorly. The REFRESH_FAST_AFTER_INSERT capability says it better: mv log does not have all necessary columns.


ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV

You want to know why, so this error message is not helpful. You probably violated one of the three restrictions below:

1) Restriction 4 for basic MV fast refresh: It cannot contain an analytic function. The REFRESH_FAST_AFTER_INSERT capability says: window function in mv.

2) Restriction 5 for basic MV fast refresh: It cannot contain a MODEL clause. The REFRESH_FAST_AFTER_INSERT capability says: a SPREADSHEET clause is present.

3) Restriction 2 for join MV fast refresh: Rowids of all the tables in the FROM list must appear in the SELECT list of the query. The REFRESH_FAST_AFTER_INSERT capability says: the SELECT list does not have the rowids of all the detail tables.

So when you encounter an ORA-12052, do an explain_mview to really see why your MV cannot be fast refreshed.


ORA-12053: this is not a valid nested materialized view

And why is this not a valid nested materialized view? You probably violated restriction 1 for nested MV fast refresh:All parent and base materialized views must contain joins or aggregates. An explain_mview is not helpful here, as the REFRESH_FAST_AFTER_INSERT capability says: requirements not satisfied for fast refresh of nested mv. As could be seen in part five, this message means one of your parent MV's is a basic MV.


ORA-22818: subquery expressions not allowed here

You probably violated restriction 3 for basis MV fast refresh: It cannot contain a SELECT list subquery. A clear error message. An explain_mview cannot be done; it gives error message ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement.


ORA-23413: table "RWIJK"."MYDEPT" does not have a materialized view log

This error message cannot be more clear: you need materialized view logs on the base tables/MV's and they are missing. This error message corresponds with:
- Restriction 4 for join MV fast refresh: Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
- Restriction 2 for aggregate MV fast refresh: All tables in the materialized view must have materialized view logs
- Restriction 2 for nested MV fast refresh: All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.


ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV

You violated restriction 3 for nested MV fast refresh:You cannot create both a materialized view and a prebuilt materialized view on the same table. A clear error message, so it doesn't matter that this one cannot be further explained by explain_mview.


ORA-32401: materialized view log on "RWIJK"."MYEMP" does not have new values

You violated restriction 6 for aggregate MV fast refresh: All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES. If you do an explain_mview, the REFRESH_FAST_AFTER_INSERT capability says: mv log must have new values.


ORA-32412: encrypted column "DEPTNO" not allowed in the materialized view log

You violated restriction 4 for aggregate MV fast refresh: None of the columns in the base table, referred to in the materialized view log, can be encrypted.


The materialized view is created, but just doesn't fast refresh

You probably violated one of the four restrictions below:

1) Restriction 15 for aggregate MV fast refresh: If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads. In this case the REFRESH_FAST_AFTER_ONETAB_DML capability says: SUM(expr) without COUNT(expr).

2) Restriction 16 for aggregate MV fast refresh: If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads. In this case, the REFRESH_FAST_AFTER_ONETAB_DML capability says: COUNT(*) is not present in the select list.

3) Restriction 17 for aggregate MV fast refresh: A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause. In this case the REFRESH_FAST_AFTER_ONETAB_DML capability says: mv uses the MIN or MAX aggregate functions.

4) Restriction 22 for aggregate MV fast refresh: Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. In this case the MV_CAPABILITIES_TABLE says everything is ok.

So by using the MV_CAPABILITIES_TABLE, there is a good chance you may find out more about this situation.


ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

You probably violated one of the restrictions below:

• Restriction 1 for basic MV fast refresh: The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
• Restriction 7 for basic MV fast refresh: It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
• Restriction 8 for basic MV fast refresh: It cannot contain a [START WITH ...] CONNECT BY clause.
• Restriction 10 for basis MV fast refresh: ON COMMIT materialized views cannot have remote detail tables.
• Restriction 5 for join MV fast refresh: You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.
• Restriction 6 for join MV fast refresh: ANSI joins are not possible
• Restriction 8 for aggregate MV fast refresh: Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
• Restriction 10 for aggregate MV fast refresh: Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
• Restriction 12 for aggregate MV fast refresh: If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.
• Restriction 13 for aggregate MV fast refresh: The SELECT list must contain all GROUP BY columns.
• Restriction 18 for aggregate MV fast refresh: For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
• Restriction 19 for aggregate MV fast refresh: For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".
• Restriction 21 for aggregate MV fast refresh: The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.
• Restriction 23 for aggregate MV fast refresh: Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.
• Restriction 24 for aggregate MV fast refresh: If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
• Restriction 25 for aggregate MV fast refresh: Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.
• Restriction 26 for aggregate MV fast refresh: It cannot contain a HAVING clause with a subquery.
• Restriction 1 for union all MV fast refresh:The defining query must have the UNION ALL operator at the top level.
• Restriction 3 for union all MV fast refresh:The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block.
• Restriction 5 for union all MV fast refresh:Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL.
• Restriction 6 for union all MV fast refresh:Remote tables are not supported for materialized views with UNION ALL.

Good luck in determining which one applies to your situation :-)

This is horrible of course. The ORA-12054 basically means: sorry, you are out of luck. And don't bother trying to explain the materialized view, because that just leads to an "ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement" message. Of course it would be nice if Oracle provided one error message per violated restriction. We can but hope ...

Fast refreshable materialized view errors, part six: MV_CAPABILITIES_TABLE

After you've been confronted with an error message during the creation of your fast refreshable materialized view, you either know exactly what to do next to resolve the error, or not. If not, the MV_CAPABILITIES_TABLE might help. You can read about it in the documentation here. I remember trying to use it in my project half a year ago and I didn't find it useful. After reading this post, you'll probably know what error message I was trying to resolve back then...

The MV_CAPABILITIES_TABLE is much like the PLAN_TABLE. The latter stores the execution plan of a SQL query and its related statistics after issuing an explain plan statement. The MV_CAPABILITIES_TABLE stores the capabilities, among which the capability to fast refresh, after issuing an dbms_mview.explain_mview statement. You can install the MV_CAPABILITIES_TABLE by running the utlxmv.sql script.

Here is how it works when the fast refreshable materialized view doesn't experience any problems. It is the same setup as in part one:

rwijk@ORA11GR1> create table myemp as select * from emp
  2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with primary key, rowid
  3  /

Materialized view log created.

rwijk@ORA11GR1> truncate table mv_capabilities_table
  2  /

Table truncated.

Now wrap the entire "create materialized view" statement as an argument to the explain_mview procedure:

rwijk@ORA11GR1> begin
  2    dbms_mview.explain_mview
  3    ( 'create materialized view emp_mv
  4    refresh fast on commit
  5  as
  6  select empno
  7       , ename
  8       , job
  9       , sal
 10       , deptno
 11    from myemp
 12   where deptno = 10'
 13    );
 14  end;
 15  /

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> select *
  2    from mv_capabilities_table
  3   order by seq
  4  /

STATEMENT_ID                   MVOWNER
------------------------------ ------------------------------
MVNAME                         CAPABILITY_NAME                P
------------------------------ ------------------------------ -
RELATED_TEXT
--------------------------------------------------------------------------------
RELATED_NUM      MSGNO
----------- ----------
MSGTXT
--------------------------------------------------------------------------------
       SEQ
----------
                               RWIJK
EMP_MV                         PCT                            N



         1

                               RWIJK
EMP_MV                         REFRESH_COMPLETE               Y



      1002

                               RWIJK
EMP_MV                         REFRESH_FAST                   Y



      2003

                               RWIJK
EMP_MV                         REWRITE                        N



      3004

                               RWIJK
EMP_MV                         PCT_TABLE                      N
MYEMP
        155       2068
relation is not a partitioned table
      4005

                               RWIJK
EMP_MV                         REFRESH_FAST_AFTER_INSERT      Y



      5006

                               RWIJK
EMP_MV                         REFRESH_FAST_AFTER_ONETAB_DML  Y



      6007

                               RWIJK
EMP_MV                         REFRESH_FAST_AFTER_ANY_DML     Y



      7008

                               RWIJK
EMP_MV                         REFRESH_FAST_PCT               N

                  2157
PCT is not possible on any of the detail tables in the materialized view
      8009

                               RWIJK
EMP_MV                         REWRITE_FULL_TEXT_MATCH        N

                  2159
query rewrite is disabled on the materialized view
      9010

                               RWIJK
EMP_MV                         REWRITE_PARTIAL_TEXT_MATCH     N

                  2159
query rewrite is disabled on the materialized view
     10011

                               RWIJK
EMP_MV                         REWRITE_GENERAL                N

                  2159
query rewrite is disabled on the materialized view
     11012

                               RWIJK
EMP_MV                         REWRITE_PCT                    N

                  2158
general rewrite is not possible or PCT is not possible on any of the detail tabl
es
     12013

                               RWIJK
EMP_MV                         PCT_TABLE_REWRITE              N
MYEMP
        155       2068
relation is not a partitioned table
     13014


14 rows selected.

This materialized view is fast refreshable. You can tell because the column "Possible" contains a 'Y' value for the REFRESH_FAST% capabilities.

The MV capabilities are roughly about three features: fast refresh, query rewrite and partition change tracking (wonderful feature, by the way!). This series is about fast refresh, so from now on I'll use the predicate "capability_name like 'REFRESH_FAST%'".

I will now repeat all situations of the previous five posts, only skipping the ones that didn't result in an error. You can imagine it can become extremely lengthy, so I'll only write the restriction, the error message and the result of doing this sequence:

truncate table mv_capabilities_table
/
begin
  dbms_mview.explain_mview
  ( 'create materialized view ...'
  );
end;
/
select capability_name
     , possible
     , msgtxt
  from mv_capabilities_table
 where capability_name like 'REFRESH_FAST%'
 order by seq
/

At the end of this post, you'll find a link to the script used to receive this output, so you can replay it on your own database, and see for yourself.


Restriction 1 for basic MV fast refresh: The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: the dbms_mview.explain_mview errors out with an ORA-32342:

rwijk@ORA11GR1> begin
  2    dbms_mview.explain_mview
  3    ( 'create materialized view emp_mv
  4    refresh fast on commit
  5  as
  6  select empno
  7       , ename
  8       , job
  9       , sal
 10       , deptno
 11       , sysdate now
 12    from myemp
 13   where deptno = 10'
 14    );
 15  end;
 16  /
begin
*
ERROR at line 1:
ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view
statement
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
ORA-06512: at "SYS.DBMS_XRWMV", line 22
ORA-06512: at "SYS.DBMS_XRWMV", line 42
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3299
ORA-06512: at line 2

And the MV_CAPABILITIES TABLE remains empty:

rwijk@ORA11GR1> select capability_name
  2       , possible
  3       , msgtxt
  4    from mv_capabilities_table
  5   where capability_name like 'REFRESH_FAST%'
  6   order by seq
  7  /

no rows selected

This situation will be encountered much more often, so this will be shortened to "ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement" from now on.



Restriction 3 for basis MV fast refresh: It cannot contain a SELECT list subquery.

Error message: ORA-22818: subquery expressions not allowed here

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 4 for basic MV fast refresh: It cannot contain an analytic function.

Error message: ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
window function in mv

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

The "ORA-12052: cannot fast refresh materialized view X" does not tell you why it cannot refresh, but the MV capabilities table tells you "window function in MV" as the reason. So it's worth using in this case.



Restriction 5 for basic MV fast refresh: It cannot contain a MODEL clause.

Error message: ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
a SPREADSHEET clause is present

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

"a SPREADSHEET clause is present". Nice name for the model clause, but again you are getting more information from the MV_CAPABILITIES_TABLE.



Restriction 7 for basic MV fast refresh: It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement.



Restriction 8 for basic MV fast refresh: It cannot contain a [START WITH à] CONNECT BY clause.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view.



Restriction 10 for basis MV fast refresh: ON COMMIT materialized views cannot have remote detail tables.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 2 for join MV fast refresh: Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

Error message: ORA-12052: cannot fast refresh materialized view RWIJK.EMPDEPT_MV

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
the SELECT list does not have the rowids of all the detail tables

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Again, the ORA-12052 error message leads to a much clearer message in the capabilities table.



Restriction 4 for join MV fast refresh:
Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

Error message: ORA-23413: table "RWIJK"."MYDEPT" does not have a materialized view log

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
the detail table does not have a materialized view log

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

The error message was already very clear, and the contents of the capabilities table is so as well.



Restriction 5 for join MV fast refresh: You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 6 for join MV fast refresh: ANSI joins are not possible

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 2 for aggregate MV fast refresh:All tables in the materialized view must have materialized view logs

Error message: ORA-23413: table "RWIJK"."MYEMP" does not have a materialized view log

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
the detail table does not have a materialized view log

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 3 for aggregate MV fast refresh:The materialized view logs must contain all columns from the table referenced in the materialized view

Error message: ORA-12033: cannot use filter columns from materialized view log on
"RWIJK"."MYEMP"

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
mv log does not have all necessary columns

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 4 for aggregate MV fast refresh:None of the columns in the base table, referred to in the materialized view log, can be encrypted.

Error message: ORA-32412: encrypted column "DEPTNO" not allowed in the materialized view log

MV capabilities: because the MV log could not even be created, it's not possible to collect a message from the MV_CAPABILITIES_TABLE.



Restriction 5 for aggregate MV fast refresh:All tables in the materialized view must have materialized view logs specified with ROWID

Error message: ORA-12032: cannot use rowid column from materialized view log on
"RWIJK"."MYEMP"

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
mv log must have ROWID

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 6 for aggregate MV fast refresh:All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.

Error message: ORA-32401: materialized view log on "RWIJK"."MYEMP" does not have new values

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
mv log must have new values

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 8 for aggregate MV fast refresh:Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 10 for aggregate MV fast refresh:Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 12 for aggregate MV fast refresh:If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 13 for aggregate MV fast refresh:The SELECT list must contain all GROUP BY columns.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 15 for aggregate MV fast refresh:If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads.

Error message: none; MV gets created but just doesn't fast refresh

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   Y


REFRESH_FAST_AFTER_INSERT      Y


REFRESH_FAST_AFTER_ONETAB_DML  N
SUM(expr) without COUNT(expr)

REFRESH_FAST_AFTER_ANY_DML     N
mv log does not have sequence #

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

This one is interesting and explains why the MV gets created, but doesn't (always) fast refresh: REFRESH_FAST and REFRESH_FAST_AFTER_INSERT are possible, but REFRESH_FAST_AFTER_ONETAB_DML is not possible. According to the documentation, the latter means:

If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation, provided all update operations are performed on a single table. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations are performed on multiple tables.

If you look closely at my test of restriction 15 in the third post of this series, then you see I only perform one update on one table, so I am at a loss here as to why that single update doesn't cause the MV to get refreshed after commit. But at least the MV_CAPABILITIES_TABLE warns you by saying "SUM(expr) without COUNT(expr)".



Restriction 16 for aggregate MV fast refresh:If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads.

Error message: none; MV gets created but just doesn't fast refresh

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   Y


REFRESH_FAST_AFTER_INSERT      Y


REFRESH_FAST_AFTER_ONETAB_DML  N
COUNT(*) is not present in the select list

REFRESH_FAST_AFTER_ANY_DML     N
mv log does not have sequence #

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 17 for aggregate MV fast refresh:A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

Error message: none; MV gets created but just doesn't fast refresh

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   Y


REFRESH_FAST_AFTER_INSERT      Y


REFRESH_FAST_AFTER_ONETAB_DML  N
mv uses the MIN or MAX aggregate functions

REFRESH_FAST_AFTER_ANY_DML     N
mv log does not have sequence #

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 18 for aggregate MV fast refresh:For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 19 for aggregate MV fast refresh:For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 21 for aggregate MV fast refresh:The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 22 for aggregate MV fast refresh:Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified.

Error message: none; MV gets created, but just doesn't fast refresh after modification on the inner table

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   Y


REFRESH_FAST_AFTER_INSERT      Y


REFRESH_FAST_AFTER_ONETAB_DML  Y


REFRESH_FAST_AFTER_ANY_DML     N
mv log does not have sequence #

REFRESH_FAST_AFTER_ANY_DML     N
mv log does not have sequence #

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

And after adding "sequence" to the materialized view log, it says:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   Y


REFRESH_FAST_AFTER_INSERT      Y


REFRESH_FAST_AFTER_ONETAB_DML  Y


REFRESH_FAST_AFTER_ANY_DML     Y


REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

So no additional clue as to why the refresh doesn't happen: the MV_CAPABILITIES_TABLE indicates all is well.



Restriction 23 for aggregate MV fast refresh:Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement

Note that in part three, the MV was created first, then the primary key was dropped. This lead to an MV that didn't refresh. Here I changed the order by first dropping the primary key and then trying to create the MV. This time it led to the above mentioned ORA-12054.



Restriction 24 for aggregate MV fast refresh:If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 25 for aggregate MV fast refresh:Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 26 for aggregate MV fast refresh:It cannot contain a HAVING clause with a subquery.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 1 for union all MV fast refresh:The defining query must have the UNION ALL operator at the top level.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 3 for union all MV fast refresh:The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 5 for union all MV fast refresh:Insert-only aggregate materialized view queries are not supported for materialized views with UNION ALL.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 6 for union all MV fast refresh:Remote tables are not supported for materialized views with UNION ALL.

Error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



Restriction 1 for nested MV fast refresh:All parent and base materialized views must contain joins or aggregates.

Error message: ORA-12053: this is not a valid nested materialized view

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
requirements not satisfied for fast refresh of nested mv

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

"Requirements not satisfied for fast refresh of nested MV". Now that's not very helpful, is it? Exactly the same is said by the 'N' in the Possible column.



Restriction 2 for nested MV fast refresh: All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.

Error message: ORA-23413: table "RWIJK"."PARENT1_MV" does not have a materialized view log

MV capabilities:

CAPABILITY_NAME                P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST                   N


REFRESH_FAST_AFTER_INSERT      N
the detail table does not have a materialized view log

REFRESH_FAST_AFTER_ONETAB_DML  N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N
PCT is not possible on any of the detail tables in the materialized view

Restriction 3 for nested MV fast refresh:You cannot create both a materialized view and a prebuilt materialized view on the same table.

Error message: ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV

MV capabilities: ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement



The above shows that situations exist in which the MV_CAPABILITIES_TABLE gives more precise information about what restriction you are hitting, and it shows much more information as well.

The next part will summarize all findings by starting with the error message and collecting all encountered error conditions. Hopefully that will be a very useful post for people developing fast refreshable views.

The script

Fast refreshable materialized view errors, part five: nested MV's

Previous post about union all MV's

The restrictions regarding nested MV's are not in the fast-refreshable part of the documentation, but a little earlier: here.

I noticed three restrictions in there:

1) All parent and base materialized views must contain joins or aggregates.
2) All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.
3) You cannot create both a materialized view and a prebuilt materialized view on the same table.

Let's inspect the related error messages, but I'll start again with a situation where everything works. Two parent materialized views and on top of them one nested materialized view:

rwijk@ORA11GR1> create materialized view log on emp
  2    with primary key, rowid (deptno,sal) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view log on dept with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view parent1_mv
  2    refresh fast on commit
  3  as
  4  select emp.rowid emp_rowid
  5       , dept.rowid dept_rowid
  6       , emp.ename
  7       , emp.deptno
  8       , emp.sal
  9       , dept.dname
 10    from emp
 11       , dept
 12   where emp.deptno = dept.deptno
 13  /

Materialized view created.

rwijk@ORA11GR1> create materialized view parent2_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6       , count(sal) countsal
  7       , count(*) countall
  8    from emp
  9   group by deptno
 10  /

Materialized view created.

rwijk@ORA11GR1> create materialized view log on parent1_mv with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view log on parent2_mv with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view nested_mv
  2    refresh fast on commit
  3  as
  4  select p1.rowid p1_rowid
  5       , p2.rowid p2_rowid
  6       , p1.ename
  7       , p1.sal
  8       , p1.dname
  9       , p2.sumsal
 10    from parent1_mv p1
 11       , parent2_mv p2
 12   where p1.deptno = p2.deptno
 13  /

Materialized view created.

Please note that the materialized view log on emp is already fully equipped for future uses. Parent1_mv is a join MV and parent2_mv is an aggregate MV. And the nested MV joins both. This works as can be seen by this little test where KING's salary is doubled to 10,000:

rwijk@ORA11GR1> select * from nested_mv
  2  /

P1_ROWID           P2_ROWID           ENAME             SAL DNAME              SUMSAL
------------------ ------------------ ---------- ---------- -------------- ----------
AAAWnXAAEAAABRrAAA AAAWnZAAEAAABRzAAB SMITH             800 RESEARCH            10875
AAAWnXAAEAAABRrAAB AAAWnZAAEAAABRzAAA ALLEN            1600 SALES                9400
AAAWnXAAEAAABRrAAC AAAWnZAAEAAABRzAAA WARD             1250 SALES                9400
AAAWnXAAEAAABRrAAD AAAWnZAAEAAABRzAAB JONES            2975 RESEARCH            10875
AAAWnXAAEAAABRrAAE AAAWnZAAEAAABRzAAA MARTIN           1250 SALES                9400
AAAWnXAAEAAABRrAAF AAAWnZAAEAAABRzAAA BLAKE            2850 SALES                9400
AAAWnXAAEAAABRrAAG AAAWnZAAEAAABRzAAC CLARK            2450 ACCOUNTING           8750
AAAWnXAAEAAABRrAAH AAAWnZAAEAAABRzAAB SCOTT            3000 RESEARCH            10875
AAAWnXAAEAAABRrAAI AAAWnZAAEAAABRzAAC KING             5000 ACCOUNTING           8750
AAAWnXAAEAAABRrAAJ AAAWnZAAEAAABRzAAA TURNER           1500 SALES                9400
AAAWnXAAEAAABRrAAK AAAWnZAAEAAABRzAAB ADAMS            1100 RESEARCH            10875
AAAWnXAAEAAABRrAAL AAAWnZAAEAAABRzAAA JAMES             950 SALES                9400
AAAWnXAAEAAABRrAAM AAAWnZAAEAAABRzAAB FORD             3000 RESEARCH            10875
AAAWnXAAEAAABRrAAN AAAWnZAAEAAABRzAAC MILLER           1300 ACCOUNTING           8750

14 rows selected.

rwijk@ORA11GR1> update emp set sal = 10000 where ename = 'KING'
  2  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from nested_mv
  2  /

P1_ROWID           P2_ROWID           ENAME             SAL DNAME              SUMSAL
------------------ ------------------ ---------- ---------- -------------- ----------
AAAWnXAAEAAABRrAAA AAAWnZAAEAAABRzAAB SMITH             800 RESEARCH            10875
AAAWnXAAEAAABRrAAB AAAWnZAAEAAABRzAAA ALLEN            1600 SALES                9400
AAAWnXAAEAAABRrAAC AAAWnZAAEAAABRzAAA WARD             1250 SALES                9400
AAAWnXAAEAAABRrAAD AAAWnZAAEAAABRzAAB JONES            2975 RESEARCH            10875
AAAWnXAAEAAABRrAAE AAAWnZAAEAAABRzAAA MARTIN           1250 SALES                9400
AAAWnXAAEAAABRrAAF AAAWnZAAEAAABRzAAA BLAKE            2850 SALES                9400
AAAWnXAAEAAABRrAAH AAAWnZAAEAAABRzAAB SCOTT            3000 RESEARCH            10875
AAAWnXAAEAAABRrAAJ AAAWnZAAEAAABRzAAA TURNER           1500 SALES                9400
AAAWnXAAEAAABRrAAK AAAWnZAAEAAABRzAAB ADAMS            1100 RESEARCH            10875
AAAWnXAAEAAABRrAAL AAAWnZAAEAAABRzAAA JAMES             950 SALES                9400
AAAWnXAAEAAABRrAAM AAAWnZAAEAAABRzAAB FORD             3000 RESEARCH            10875
AAAWnXAAEAAABRrAAG AAAWnZAAEAAABRzAAC CLARK            2450 ACCOUNTING          13750
AAAWnXAAEAAABRrAAN AAAWnZAAEAAABRzAAC MILLER           1300 ACCOUNTING          13750
AAAWnXAAEAAABRtAAA AAAWnZAAEAAABRzAAC KING            10000 ACCOUNTING          13750

14 rows selected.

1) All parent and base materialized views must contain joins or aggregates.

Let's replace the join MV, parent1_mv, by a basic MV. Note that I'll have to recreate the other MV's as well to avoid the "ORA-12034: materialized view log younger than last refresh" message. In real life with large tables you don't want to recreate everything from scratch when doing a little maintenance. In that case, use prebuilt tables so you can split the MV in a data part (the table) and a definition part (the MV). You can then recover from an ORA-12034 by just replacing the definition part. This is a useful link regarding this subject. Although it looks like the scripts at the end have not been tested, the ideas are sound. But I digress.

rwijk@ORA11GR1> update emp set sal = 5000 where ename = 'KING'
  2  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view parent1_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> drop materialized view parent2_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> drop materialized view nested_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view parent1_mv
  2    refresh fast on commit
  3  as
  4  select empno
  5       , ename
  6       , deptno
  7       , sal
  8    from emp
  9  /

Materialized view created.

rwijk@ORA11GR1> create materialized view parent2_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6       , count(sal) countsal
  7       , count(*) countall
  8    from emp
  9   group by deptno
 10  /

Materialized view created.

rwijk@ORA11GR1> create materialized view log on parent1_mv with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view log on parent2_mv with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view nested_mv
  2    refresh fast on commit
  3  as
  4  select p1.rowid p1_rowid
  5       , p2.rowid p2_rowid
  6       , p1.ename
  7       , p1.sal
  8       , p2.sumsal
  9    from parent1_mv p1
 10       , parent2_mv p2
 11   where p1.deptno = p2.deptno
 12  /
  from parent1_mv p1
       *
ERROR at line 9:
ORA-12053: this is not a valid nested materialized view

I'm not exactly fond of a "is not valid" error message, as it can be paraphrased more meaningful, but we've seen worse in this series.

Let's try the other possibility: a union all MV.

rwijk@ORA11GR1> drop materialized view parent1_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> drop materialized view parent2_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view parent1_mv
  2    refresh fast on commit
  3  as
  4  select emp.rowid emp_rowid
  5       , empno
  6       , ename
  7       , deptno
  8       , sal
  9       , 'E1' umarker
 10    from emp
 11   where deptno = 10
 12   union all
 13  select emp.rowid emp_rowid
 14       , empno
 15       , ename
 16       , deptno
 17       , sal
 18       , 'E2' umarker
 19    from emp
 20   where deptno = 20
 21  /

Materialized view created.

rwijk@ORA11GR1> create materialized view parent2_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6       , count(sal) countsal
  7       , count(*) countall
  8    from emp
  9   group by deptno
 10  /

Materialized view created.

rwijk@ORA11GR1> create materialized view log on parent1_mv with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view log on parent2_mv with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view nested_mv
  2    refresh fast on commit
  3  as
  4  select p1.rowid p1_rowid
  5       , p2.rowid p2_rowid
  6       , p1.ename
  7       , p1.sal
  8       , p2.sumsal
  9    from parent1_mv p1
 10       , parent2_mv p2
 11   where p1.deptno = p2.deptno
 12  /

Materialized view created.

rwijk@ORA11GR1>
rwijk@ORA11GR1> select * from parent1_mv
  2  /

EMP_ROWID               EMPNO ENAME          DEPTNO        SAL UM
------------------ ---------- ---------- ---------- ---------- --
AAARhlAAEAAAAI3AAG       7782 CLARK              10       2450 E1
AAARhlAAEAAAAI3AAI       7839 KING               10       5000 E1
AAARhlAAEAAAAI3AAN       7934 MILLER             10       1300 E1
AAARhlAAEAAAAI3AAA       7369 SMITH              20        800 E2
AAARhlAAEAAAAI3AAD       7566 JONES              20       2975 E2
AAARhlAAEAAAAI3AAH       7788 SCOTT              20       3000 E2
AAARhlAAEAAAAI3AAK       7876 ADAMS              20       1100 E2
AAARhlAAEAAAAI3AAM       7902 FORD               20       3000 E2

8 rows selected.

rwijk@ORA11GR1> select * from nested_mv
  2  /

P1_ROWID           P2_ROWID           ENAME             SAL     SUMSAL
------------------ ------------------ ---------- ---------- ----------
AAAWnoAAEAAABRrAAA AAAWnqAAEAAABRzAAC CLARK            2450       8750
AAAWnoAAEAAABRrAAB AAAWnqAAEAAABRzAAC KING             5000       8750
AAAWnoAAEAAABRrAAC AAAWnqAAEAAABRzAAC MILLER           1300       8750
AAAWnoAAEAAABRrAAD AAAWnqAAEAAABRzAAB SMITH             800      10875
AAAWnoAAEAAABRrAAE AAAWnqAAEAAABRzAAB JONES            2975      10875
AAAWnoAAEAAABRrAAF AAAWnqAAEAAABRzAAB SCOTT            3000      10875
AAAWnoAAEAAABRrAAG AAAWnqAAEAAABRzAAB ADAMS            1100      10875
AAAWnoAAEAAABRrAAH AAAWnqAAEAAABRzAAB FORD             3000      10875

8 rows selected.

rwijk@ORA11GR1> update emp set sal = 10000 where ename = 'KING'
  2  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from parent1_mv
  2  /

EMP_ROWID               EMPNO ENAME          DEPTNO        SAL UM
------------------ ---------- ---------- ---------- ---------- --
AAARhlAAEAAAAI3AAG       7782 CLARK              10       2450 E1
AAARhlAAEAAAAI3AAN       7934 MILLER             10       1300 E1
AAARhlAAEAAAAI3AAA       7369 SMITH              20        800 E2
AAARhlAAEAAAAI3AAD       7566 JONES              20       2975 E2
AAARhlAAEAAAAI3AAH       7788 SCOTT              20       3000 E2
AAARhlAAEAAAAI3AAK       7876 ADAMS              20       1100 E2
AAARhlAAEAAAAI3AAM       7902 FORD               20       3000 E2
AAARhlAAEAAAAI3AAI       7839 KING               10      10000 E1

8 rows selected.

rwijk@ORA11GR1> select * from nested_mv
  2  /

P1_ROWID           P2_ROWID           ENAME             SAL     SUMSAL
------------------ ------------------ ---------- ---------- ----------
AAAWnoAAEAAABRrAAD AAAWnqAAEAAABRzAAB SMITH             800      10875
AAAWnoAAEAAABRrAAE AAAWnqAAEAAABRzAAB JONES            2975      10875
AAAWnoAAEAAABRrAAF AAAWnqAAEAAABRzAAB SCOTT            3000      10875
AAAWnoAAEAAABRrAAG AAAWnqAAEAAABRzAAB ADAMS            1100      10875
AAAWnoAAEAAABRrAAH AAAWnqAAEAAABRzAAB FORD             3000      10875
AAAWnoAAEAAABRrAAA AAAWnqAAEAAABRzAAC CLARK            2450      13750
AAAWnoAAEAAABRrAAC AAAWnqAAEAAABRzAAC MILLER           1300      13750
AAAWnoAAEAAABRtAAA AAAWnqAAEAAABRzAAC KING            10000      13750

8 rows selected.

This scenario works. And parent1_mv is clearly neither a join MV nor an aggregate MV. So it looks like the Oracle documentation needs an update here.

When an underlying MV is more complex (join, aggregate, union all), then everything works, but when the underlying MV is simple, it breaks. Makes me wonder why. Does anyone have a clue? Or is it just Oracle saying to the developer that he made his MV design too complex, and he can do with one MV less?


2) All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log.

A rather obvious one, but for the record:

rwijk@ORA11GR1> update emp set sal = 5000 where ename = 'KING'
  2  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view log on parent1_mv
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> update emp set sal = 10000 where ename = 'KING'
  2  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /
commit
*
ERROR at line 1:
ORA-23413: table "RWIJK"."PARENT1_MV" does not have a materialized view log

And yes I know, if dropping the materialized view log would have worked this way, it would lead to an ORA-12034. But that's not the point here.


3) You cannot create both a materialized view and a prebuilt materialized view on the same table.

The documentation adds an example of what is meant here:

For example, If you have a table costs with a materialized view cost_mv based on it, you cannot then create a prebuilt materialized view on table costs. The result would make cost_mv a nested materialized view and this method of conversion is not supported.

I simulated this situation by removing the previous setup and introducing an auxiliary table myemp, acting as the costs table above.

rwijk@ORA11GR1> drop materialized view nested_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> drop materialized view parent2_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> drop materialized view parent1_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create table myemp as select * from emp
  2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on myemp with rowid
  2  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view parent1_mv
  2    refresh fast on commit
  3  as
  4  select emp.rowid emp_rowid
  5       , dept.rowid dept_rowid
  6       , emp.ename
  7       , emp.deptno
  8       , emp.sal
  9       , dept.dname
 10    from myemp emp
 11       , dept
 12   where emp.deptno = dept.deptno
 13  /

Materialized view created.

rwijk@ORA11GR1> create materialized view myemp
  2    on prebuilt table
  3    refresh fast on commit
  4  as
  5  select empno
  6       , ename
  7       , deptno
  8       , sal
  9    from emp
 10  /
create materialized view myemp
        *
ERROR at line 1:
ORA-32334: cannot create prebuilt materialized view on a table already referenced
by a MV

A nice clear error message.

In the next part I'll go through all previous situations again, and use the explain_mview procedure and mv_capabilities_table to see if this gives us more information than the error message itself.