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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rbeadieConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mike1956Author Commented:
What is your recommendation to remove the DISTINCT?  Thanks
0
 
mike1956Author Commented:
getting this error when running the query.

ORA-01427: single-row subquery returns more than one row
0
 
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
All Courses

From novice to tech pro — start learning today.