My developers came in today and asked me to create tons of views for them so they can avoid write too much code in their selects. These views are based on joins of 5 or 6 tables, my question is excessive views is a good idea? I dont think so really... any opinions?
As far as I know, as long as it isn't a view, based on a view, based on a view ......... which can cause problems when trying to find the correct indexes, there is no problem with views, they're generally a good thing.
well, views are quite a topic in both theory and practice.
In your case it looks like developers want to use views in order to keep piece of sql code in one centralized place. This is very good argument, preventing spreading of identical code across many modules.
However if views are created as security layer to mask db structures from application in order to do changes of tables in the future then this is wrong idea. rename command allows to do it later.
As for oracle:
1. views are not cached in library or dictionary cache (hi hi hi) and this causes certain overhead. But let oracle deal with this. There are more serious problems than that.
2. optimization of some queries involving views can be tricky or imposible at all(e.g union all views joined to other tables/views), but this is developers issue.
If your developers will be using these queries over and over, views are a great idea. It will allow you to be able to tune the views and ensure adequate performance. However, be carefull not to have multiple views that do the same thing as it will eventually get to be too much to keep track of. Views should go through a similar needs analysis process as tables.
LND what do you mean views are not cached? The SQL stmt that builds view?
My developers pretends a view per SQL stmt
If views are not cacheable by Oracle what is the other way to speed up my queries?
Is it stored procedures?
"LND what do you mean views are not cached? The SQL stmt that builds view?" - from my knowledge, yes.
The SQL stm is cached in block buffer only.
"My developers pretends a view per SQL stmt" - really?!
If views are used for security(like ALL_, DBA_ and USER_ views of oracle data dictionary) then it is OK.
Else views are used to join tables into something that is used frequently by application (i.e. a lot of separate application parts use this view, and this is about reuse what is good)
Else views hide internal structures (like oracle's data dictionary views), but is this the case for end-user application? Continuing analogy with oracle, does it use those extremly slow views in its kernel?
hmm what do you mean SQL stmt is cached in block buffer only? which buffer?!
The developers want to use view for frequently used queries yes, but seems that there are quite a few
The statements that comprise the views are cached. The data is cached in the buffer cache just as if it were executed from the command line. If your view is hit enough, you db blocks will already be in cache.
hey...have to work today..:-)
I'll tell you that i don't see it negative (in form9 but you have to think about this things: the views are written in data dictionary and data dictionary resides in system tablespace and much of the data dictionary information is cached in the SGA (information is stored in memory using the LRU (least recently used) ...
Click Here to Expand Forum to Full Width