Materialized View performance problem

The attached code needs to be built in to a materialized view for a discoverer report to access.  I did not write the code but responsible now for engineering a solution.  Can this code be broken in to multiple mv's and then combined?  I have never done that.  Or is there a better way to engineer this process.

Currently they create this view which is attached and then try to create a mv on top of it and since the view is executed and is poor in performance it takes four days to complete.  I have added some indexing but still poor performance.  I think the enitre process needs re engineered.  I am open to any ideas.  Thanks experts
mv4expertechange.txt
mike1956Asked:
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.

rbeadieCommented:
Though I don't know for sure without seeing the explain plan output, I'd guess that the subqueries may contribute to your performance problems.  Replace them with outer joins directly on the tables and see if that helps.  See the attached file.

Also, you might try to modify the query so that the DISTINCT clause is not necessary (usually the result of a suspect join).
mv4expertechange.txt
0
mike1956Author Commented:
I will try this when I get to the office on Monday and also post the explain plan from the original.  Thank you.  was thinking about trying to break it in to smaller mv's and create a group.   Will post on Monday.
0
mike1956Author Commented:
What is your recommendation to remove the DISTINCT?  Thanks
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mike1956Author Commented:
getting this error when running the query.

ORA-01427: single-row subquery returns more than one row
0
rbeadieCommented:
Mike1956, sorry that I've been slow to respond -- I was sick.  As for the DISTINCT, knowing whether or not it can be removed requires some knowledge of the data, so I can't give you a definitive answer.  What I'd suggest is running the statement both with and without it, then looking at the rows that are duplicated.  This can help to give you an idea about which join might be causing the multiples.

As for the subquery error, it is coming from this line in the SELECT clause:

(select relationship_type from hz_relationship_types where forward_rel_code = ptrel.relationship_code)

You can change it to the following and see if that fixes the problem:

(select relationship_type from hz_relationship_types where forward_rel_code = ptrel.relationship_code group by relationship_type)
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
mike1956Author Commented:
That worked.  I am still having very slow performance.  Better but still too slow.  There is a function call where it seems to drag down now.  I need to find a way to reduce the data set.  
0
rbeadieCommented:
It may be the size of the dataset, or it could be the number of joins.  The explain plan output may offer some insight as to which operation is consuming the most resources.  You might also look at your report design and see if you can simplify the MV in some way so that you don't need so many joins in the MV.  

Another option is to replace your MV with a table -- then you can update the data with a stored procedure.  Since the stored procedure can run steps individually (rather than trying to do everything in a single query), you should be able to get better performance.  This is the approach that I've used when I had MVs that I could not optimize satisfactorily.
0
mike1956Author Commented:
thanks
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.