Alex Rivera | Logout

Is it OK to nest database views?

Asked 2009-09-29T20:27:53.050
10

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)?

sql oracle-database plsql view
Edit
Report

2 Answers

5

I'll just answer from a best practices perspective:

There are only a few times I would pause about using Views on Views.

  1. Nesting seems to be getting out of hand ... like over 3 levels deep. The reason I am nesting is to make the code easier to maintain. As soon as I start getting to this point it starts feeling a little too complicated to understand.

  2. Nesting a view which uses analytical functions. I've personally, for one reason or another, not had very good experience with nesting views with analytical function.

  3. Nesting views that do full scans by nature. While I think the the query optimizer is probably smart enough to handle this it just looks wrong to me when I'm reviewing the logic of the view.

  4. Performance is a great concern. This is not to say the optimizer might get it wrong but this is to say before I release it, I'm going to test it to see if I can't figure a faster way to do it.

Other than that I've used views on views quite successfully.

answered 2009-09-30T15:04:07.607
2

There's always a tradeoff between coding time, ease or quality of code, and performance.

Nesting views is really easy to code and, given the right circumstances, makes it easy to read. It can also reduce time. It's arguably reducing the quality and often reduces performance... but by how much?

It's all subjective. If it makes sense, roll with it. Don't prematurely optimize your code.

answered 2009-09-29T21:04:08.317

Your Answer