This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL VIEW.." Watch "SQL VIEW.." New topic


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

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:
subject: SQL VIEW..
Similar Threads
A few Good questions..
Entity Bean - View mapping
Oracle dependencies query
Choice of Implementation
Passed 1z1-043