• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL VIEW..

 
Brian Percival
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • 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
  • 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..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic