I am far from a DX, Just a database person.

View is a good concept. But it is very easy to be abused by people who don't know database much, and thus resulting in performance issue.

A simple example. It often happens that developers query only a subset of a view. But in the view itself, all the tables must be joined. It is a waste of resources. It sounds very stupid and you may assume that developers wont' do it. But it does happen often.

Also, never believe in optimizer. This is only my opinion. People may disagree. Although optimizers have been very smart, still they can get confused in many cases and generated a sub-optimal plan. Oracle has the best optimizer till now, but you know what, many database people make a joke to call it "randomizer". :)

As for views, optimizers can get confused much more often on hints in a view. Then it will generate sub-optimal plans. This is especially true for views of complex queries.

On the other hand, if the query is simple, why bother to create a view for it.

Of course, views can be beneficial in security. It is a trade-off.

As for materialized views, I think in databases that require high performance and high concurrency, the issues it causes outweigh the benefit. First, it consumes resources to materialize it. Second, materialized views may become stale and require regular update, which requires more resources as well.

所有跟帖： 

• 俺顶你，有事没事就弄“景”（VIEW）的人，我总想再背后跩他一脚！ -以后再说- ♂ (0 bytes) () 10/11/2008 postreply 14:14:23

• who are you? you are new here? :) -戏雨飞鹰- ♀ (0 bytes) () 10/11/2008 postreply 14:30:48

• 我是who?， 以后再说！ -以后再说- ♂ (58 bytes) () 10/11/2008 postreply 15:24:31

• :) no need 以后再说. -戏雨飞鹰- ♀ (0 bytes) () 10/11/2008 postreply 15:27:23

• Many thanks for the cogent answer :). I like it. -戏雨飞鹰- ♀ (265 bytes) () 10/11/2008 postreply 14:29:18