how to perform a fast refresh on a complex materialized view.

Can you provide more information on your complex view?

Sort of what it is doing that keeps the MV from being fast refreshable?

While we wait, have you checked out the restrictions?

General Restrictions on Fast Refresh
http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007007

ok...I have a four table inner join, with a where clause on one of the tables.
I have a DECODE and a CAST statement; with the whole thing set to DISTINCT.

So in short I have

Select Distinct
     DECODE(table1.column1, 'xxx', 'y', 'z'),
     table1.column2,
     table2.column1,
     cast(table2.column2 as varchar2(3)),
     table2.column3,
     table2.column4,
     table2.column5
from table2, table1, table3, table4
where   table2.column3=table1.column4,
              table3.column1=table4.column1,
              table2.column3=table3.column4
              table4.column3 in ('val1','val2');

Select allOpen in new window

I'm sorry I can't put in the actual request, but this is proper in it's format and column order.

Thanks

>>I'm sorry I can't put in the actual request

No problem.  What you provided should give me what we need.

Let me see what I can come up with.

The last time I did this it was totally by trial and error.  I had to do it a few times before I got all the columns that I needed.

First, here is the doc link:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#i2064649

What you need to be looking at in the materialized view log is the WITH clause.  You need to add the columns that you are filtering on.  For instance, the materialized view log on table2 needs to have column3 in the WITH list.  You will need to do that for every table.  I believe that if you do that,  you should be able to do a fast refresh on the view.

>> with the whole thing set to DISTINCT.
I would look for ways to remove the need for distinct
e.g.
perhaps by replacing joins to table3 and table4 with an EXISTS() construct

SELECT
       DECODE(table1.column1, 'xxx', 'y', 'z')
     , table1.column2
     , table2.column1
     , cast(table2.column2 AS varchar2(3))
     , table2.column3
     , table2.column4
     , table2.column5
FROM table2
INNER JOIN table1 ON table2.column3 = table1.column4
WHERE EXISTS (
               SELECT 1
               FROM table3
               INNER JOIN table4 ON table3.column1 = table4.column1
               WHERE table4.column3 IN ('val1', 'val2')
               AND table3.column4 = table2.column3 --<< table2 used here
             )
;

Select allOpen in new window

+edit & postscript
from "General Restrictions on Fast Refresh" (first url above)
"It cannot contain nested queries that have ANY, ALL, or NOT EXISTS."

I am assuming EXISTS() is therefore OK

Thanks for the idea Paul, but aren't subselects verboten in fast refreshes?

In my reading of the documentation an EXISTS() subquery is not excluded - you would need to satisfy yourself of course.

doco: as provided earlier:
http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007007

Others may have more direct experience, I have not personally used this combination.

I can say that avoiding the conditions that cause a need for distinct will only have positive effects on performance of the query itself.

What absolutely is verboten is
"It cannot contain a SELECT list subquery."

SELECT
       DECODE(table1.column1, 'xxx', 'y', 'z')
     , table1.column2
, (select max(something) from somewhere where somewhere.id = table1.is)
     ^^^^ this is a "SELECT list subquery"

I'm going to take this and grant the points.
it will take a week to get approvals to do the repairs...
so I'll give you the points now.
if I have other questions, I'll put it up as different ones, so you can get more points. :)

Thanks so much.
Evan

Glad to help.  Hope it works for you.

If PortletPaul or another Expert can get his example working, he can post it here and I'll unaccept this question and you can take a look at what they post.

>>I wasn't able to get PortletPaul's code to create the MV.  
If slightwv can't do it very little chance I can & I'll take that as indicating it doesn't work - pity

I'd stick with what is proven. Good one slightwv.

Cheers, Paul.

>>If slightwv can't do it very little chance I can

Thanks for the vote of confidence.  However, I'm far from an Expert on Materialized Views.  I don't know them near well enough.

nor am I experienced with MVs, the apps I deal with don't use them due to cross platform support objectives - however some clients will use them occasionally (usually BI related).

& I do have every confidence in your Oracle expertise, there is overwhelming evidence of it :)