Re: Sanity check re. layering of views

On Wed, 16 Mar 2005 10:09:07 -0400, Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca> wrote:
> My instinct it would be ideal to have at most three layers of views. That
> perhaps it's more an issue of the design of the queries used to retrieve the
> data.
>
>

I suspect that whatever number you put on it there will be someone who can come up with a reason why you could need more. I'd go with 'as few as possible' so Oracle wouldn't have a performance hit due to having to keep visiting the dictionary to get the view definitions, although after the first parse to build the execution plan would it need to?

Where I can recall seeing views stacked on top of views it's usually been a case that the system has been retrofitted with views for specific purposes (e.g. a new sort of report or an information feed to a new system) by developers who don't really undertand the database or Oracle. They might only want to expose certain columns or maybe want to do a join so the create a view which is based on existing views or a mixture of views and tables. Then later a new requirement comes along and a different developer creates another view based on that view (maybe also other views and/or tables) and so on.

I guess when you find that situation you have to ask yourself:

1. is it causing as problem?
2. is the cost of fixing that problem (rewriting application/inferface code to use the different data structures) less than the cost caused by the problem?
3. do you have time (and can get the necessary downtime) to fix it?
4. can you be bothered/motivated to fix it?

If the answer to any of those questions is 'No' then just make a note of it and get on to something more productive.

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.
--
http://www.freelists.org/webpage/oracle-l