Motivation: MySQL 5.x supports views - what database developers gain
Features: MySQL users can already create new tables readily - so what's in a view?

MySQL version 5.0 adds views, triggers and stored procedures to the MySQL DML-Data Manipulation Language features and functions. Given that users can already :

LOAD DATA ... INTO TABLE - import data into tables from external files
INSERT INTO tablename (*) SELECT * FROM ...; - insert data into a newly created table
CREATE TABLE tabname SELECT ...; - also insert data into new table.

So what does a VIEW get users over and above the Tables created dynamically using any of the above commands with a powerful SELECT with same possibilities of Joins and Subqueries as used in a CRETE VIEW statement ? Do VIEWS bring anything to the table ?

The Tradeoffs

CREATE VIEW creates a new view from the underlying tables each time a query of the View is made -
- so this means the update to the underlying emp table is reflected in the final data displayed. So when the View shows the fact that the emp table was changed and employee named Saito was change to ename SaitioMan_I_Changed_This. So the latest data in the underlying tables is used when users use a View in a query.

In contrast when we do a query on the Snapshot table, test1. The old value Saito remains in the

table. This is because the snapshot Table query is done once only. And that is useful as well. Because it acts as a snapshot of what was in the table at the moment in time when the snapshot was taken.

There is another difference - see what approach the MySQL developers have taken:
In designing an implementation of views, our ambitious goal, as much as
is possible within the confines of SQL, has been full compliance with
"Codd's Rule #6" for relational database systems: "All views that are
theoretically updatable, should in practice also be updatable."
So this means that users should be able to update the underlying tables through the View. In actual fact the current 5.0.3 version of MySQL only allows a simple update through Views to be done. It will be interesting

Summary

Think of Views as dynamic queries into the underlying table(s) that make up the View. In contrast all the other snapshot views are static - that was a onetime query that was done when the table was created. Each is valuable for developers depending on the circumstances. In addition, MySQL is committed to making the underlying tables updatable directly through the View. One can imagine the complexities involved - MySQL has to figure what table(s) to lock to maintain data integrity through the update operation using a view.

Finally there is another trade-off involved in using Views - space saved versus the extra time required to do the underlying query that creates the View. Now if a View is queried several times in a row, MySQL is smart enough to cache the underlying View data so that only has to be done once. So there are advantages to doing all queries on a View together to reduce the query time. The space saved can be quite substantial if many views are employed in a system.

(C)JBSurveyer  Home  SQL Overview