Is it OK to nest database views?

Question

In the world of oracle, I have the impression that views based on other views are considered to be bad practice. I myself have complained about this when the trying to solve performance issues and the nesting seemed excessive and hid away unneeded complexity in the underlying views. Now I find myself in the situation of thinking that it may not be so clear-cut:

I have users who very specifically need the accounting numbers from one view to match those of another that does further processing on them. If they ever change anything in one, they want the other to reflect that immediately, without anyone having to think of this requirement in a few years time and reports showing non-matching numbers while they figure things out.

Is it ok to nest views in this case?

Does it change things if the inner view contains a further, important view that contains relevant prices (i.e. you're "always" supposed to use this view when determining prices)?

Answer 1

The main problem with nesting views is that the query optimiser is more likely to get confused and produce a sub-optimal plan. Apart from this, there is no specific overhead to using views on views unless they do something that the optimiser can't push predicates down into.

This means that the best option is to try the nested views. See if you get sensible query plans out of the reports. If it does cause problems then you may have to re-think your strategy.

Answer 2

I think you are on the slippery slope here where code reuse and performance are going to clash. You can try it and see how badly it wil affect performance. We have a couple of databases here where they have stacked views on top of views and frankly the performance is miserable and now everyone involved wished thhat they had not designed that way.