wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL VIEW.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL VIEW.." Watch "SQL VIEW.." New topic
Author

SQL VIEW..

Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
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

Joined: Jun 23, 2004
Posts: 163
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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Jun 23, 2004
Posts: 163
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..
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL VIEW..