• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL VIEW..

 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
purely sql question, I know a view is a virtual table created based on the select statement, and if a view is updated,the underlying table values are updated too.

My question is, is the the view always 'fresh'? Meaning, if the underlying tables are modified, is the view data automatically modified?

I really appreciate your time on this. This is the only forum I know I will get answers fastest, so posting here.
 
Brian Percival
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
to answer my own q, I would assume a view is virtual, so each time it is accessed, a query is 'run', so it is guaranteed to be fresh based on the select criterion?

Please correct me if wrong.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Brian,

Yes and no... most views are as you describe... a "virtual" table created view a select statement that organizes the data located in other views and/or tables. If view is created off of other tables and "virtual" views... yes... it is up-to-date with in the sense that there is no latency between your result set and the underlying data.

But here's is the one caveat... the "no" part. if there is a materialized view being accessed... which is a view in the form of database cache (which is really a fancy way of saying that somewhere a physical table has been created to house the data described in a view to improve performance) there can be latency between the data in the view and the underlying table data. Is that an issue? Most times it will not be... usually this type of view will be created in a OLAP environment and it will be refreshed when new data is loaded to the data store... but it doesn't have to be.

if you are unsure of what type of database object you are accessing, you can access the database's information schema to find out what type it is (i.e., the oracle system view all_objects, mysql's information_schema tables, etc.)

why all the extra information to tell you that you will almost always be right with that assumption? in IT, if you fall into the habit of making assumptions... at some point one of your assumptions will come back and bite you.
 
Brian Percival
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you very much Paul.. That was very helpful. In my case latency is not an issue, so I guess I am ok for now..
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic