Editorials

Too Many Materialized Views

I recently read a really cool blog on how to handle views with multiple layers of dependency. The problem was that the developer needed to drop a number of views for a particular product. The problem they were facing, and solved very nicely, was how do drop the views in the correct order of dependency so that you wouldn’t get a constraint violation error when trying to delete the view. The editorial was nicely written, straight forward, and the code worked as desired.

This reminds me of the difficulty you experience when views get out of control. In this case, the order of dependency was very important. I’m curios how any database with 300+ schema binding views can be maintained? How much redundancy is experienced by having views based on views, based on other views?

The fact that these are schema binding views tells me that there were performance problems with the views in the first place. So, in order to get the queries to run, they had to materialize the data contained in the views. Now we’re wasting disk space, and slowing down performance when data in the underlying tables changes. With nested materialized views, a single table update might update multiple other virtual tables (based on the view definitions). Arghhh.

This developer now has demonstrated only one of the problems that come from abusing a good thing. The solution for the problem is creative, and should be praised. My concern is an understanding of database principles and patterns that scale and perform.

Because of the number of views that exist in the database, I would guess that there are some views, and queries using the views, addressing the same table multiple times. Multiple joins to a table is not necessarily a bad thing. It is best when you intend to join a table multiple times, rather than when it is happening because you are using views without any understanding of the underlying structure.

That’s enough rant for today. The key thing you need to ask yourself when find you need to create materialized views is, “what is wrong with my database design requiring this action for performance?” The materialized view may be a great solution. But when they accumulate to over 300 materialized views, there may be something wrong with the entire database architecture.

Cheers,

Ben