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

Greetings.
I have several materialized views that contain complex select queries.
I'm getting an error that says no fast refresh allowed.
Is there some way to enable fast refreshes on complex views?  Thanks
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
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');

Open 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
0
slightwv (䄆 Netminder) Commented:
>>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.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

johnsoneSenior Oracle DBACommented:
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.
0
PortletPaulfreelancerCommented:
>> 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
             )
;

Open 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
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thanks for the idea Paul, but aren't subselects verboten in fast refreshes?
0
PortletPaulfreelancerCommented:
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"
0
slightwv (䄆 Netminder) Commented:
I wasn't able to get PortletPaul's code to create the MV.  It might work if we played with it a little more.

Maybe he can use my test case below as a starting point.

Here is what I came up with.  I had to select the rowid's in the MV itself then created a normal view on top of that to get the distinct.

There may be a better way but I would have to defer the to MV Experts.  I don't use them every day and don't know them inside and out.

--drop table table1 purge;
--drop table table2 purge;
--drop table table3 purge;
--drop table table4 purge;
--
--create table table1( column1 char(1) primary key, column2 char(1), column3 char(1), column4 char(1), column5 char(1));
--create table table2( column1 char(1) primary key, column2 char(1), column3 char(1), column4 char(1), column5 char(1));
--create table table3( column1 char(1) primary key, column2 char(1), column3 char(1), column4 char(1), column5 char(1));
--create table table4( column1 char(1) primary key, column2 char(1), column3 char(1), column4 char(1), column5 char(1));


drop MATERIALIZED VIEW LOG ON table1;
drop MATERIALIZED VIEW LOG ON table2;
drop MATERIALIZED VIEW LOG ON table3;
drop MATERIALIZED VIEW LOG ON table4;

CREATE MATERIALIZED VIEW LOG ON table1 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON table2 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON table3 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON table4 WITH ROWID;


drop MATERIALIZED VIEW my_mv;
--
CREATE MATERIALIZED VIEW my_mv REFRESH FAST AS
Select 
    DECODE(table1.column1, 'xxx', 'y', 'z') my_decode,
	table1.rowid t1_rowid,
	table2.rowid t2_rowid,
	table3.rowid t3_rowid,
	table4.rowid t4_rowid,
    table1.column2,
    table2.column1,
    cast(table2.column2 as varchar2(3)) my_cast,
    table2.column3,
    table2.column4,
    table2.column5
from table1, table2, table3, table4
where table2.column3=table1.column4
      and table3.column1=table4.column1
      and table2.column3=table3.column4
      and table4.column3 in ('val1','val2')
/

drop view my_view;
create view my_view as
select distinct my_decode, column2, column1, my_cast, column3, column4, column5 from my_mv;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
PortletPaulfreelancerCommented:
>>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.
0
slightwv (䄆 Netminder) Commented:
>>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.
0
PortletPaulfreelancerCommented:
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 :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.