-1

I have several Oracle Database Views that have some fairly complex queries that are used often in my web-application. The query takes a good 10-25 seconds to compute and then there is some backend Java processing on the data which makes it even slower.

I was playing around with the performance by switching the Views to Materialized Views and noticed a HUGE speed increase, but of course the views no long stay up-to-date except on refresh. From what I understand, there is a way to do a "Fast Refresh" but from what I've read that's not possible with a complex query.

Does anyone have any ideas for my performance issue? I'm open to any suggestion whether it be utilizing Materialized Views or some other solution. Thanks!

Brandon Wagner
  • 893
  • 9
  • 27
  • 2
    Without more detail, it is hard to say. Did you look at the explain plan of your original query? – OldProgrammer Mar 07 '14 at 22:07
  • 1
    Depends what you mean by 'complex'. You can [analyse your materialised view](http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1008271) to see if it might be fast-refreshable. – Alex Poole Mar 07 '14 at 22:46

1 Answers1

0

The views were not able to be fast refreshed materialized views because of the joins used in them. I ended up rewriting the views into smaller sub-views. The original views' lines of code reached over 150 lines per view. This was obviously making the view slower and wasn't capitalizing on caching. Rewriting the views into smaller parts made a huge speed performance and allowed me to convert some parts of the original views into materialized views.

Brandon Wagner
  • 893
  • 9
  • 27