Views
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Views

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Hi

    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?

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    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.

  3. #3
    Join Date
    Nov 2000
    Posts
    212
    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.


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    LND what do you mean views are not cached? The SQL stmt that builds view?
    My developers pretends a view per SQL stmt

  6. #6
    Join Date
    Apr 2001
    Posts
    1

    Cool ....

    If views are not cacheable by Oracle what is the other way to speed up my queries?
    Is it stored procedures?

  7. #7
    Join Date
    Nov 2000
    Posts
    212
    "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?




  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    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) ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width