<div class="gmail_quote">On Fri, Jan 8, 2010 at 11:52 AM, Emerson, Tom (*IC) <span dir="ltr"><<a href="mailto:Tom.Emerson@wbconsultant.com">Tom.Emerson@wbconsultant.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I've come across something I think is a bit odd for a database - every table has a corresponding "view" that (I presume) is little more than "select * from table" - end user (reporting) apps only have access to the "views" and not the underlying tables. The reason I've been given for this is that it "avoids locking issues".<br>
<br>
>From this, I also presume that the "views" are "read only", which I would agree would avoid locking the underlying table, but doesn't a "view" imply extra processing and/or storage to support the view? [i.e., a "temporary" table built and populated with a copy of the data]<br>
<br>
This seems to be a false-economy to me - but then, I "grew up" with a different sort of database than the current crop of RDBMS's, one in which "locking" wasn't a big scary issue. (it is still a big issue, just not scary...) After all, "end user apps" can be written such that any "query" is a "read only" query, avoiding the need to generate a temporary table and populate it with a copy of the data [in particular, you would need/use "non-repeatable" reads]<br>
<br>
For those of you who administer modern databases, does this make sense? Is it true that a "view" that mirrors the structure of an existing table completely avoids "locking" issues with the tables, or are you robbing peter to pay paul? [using more disk or processor time to present the same data]<br>
</blockquote></div><br>Yes on one hand, maybe on the other hand. Guess which hand? (Joking)<br>
<br>
A view is a recordset of data that exists as a virtual table in the
database. They are primarily used for rapid querying, data protection, or obfuscation.
Views are also dummy objects, they only have the same security as the
underlying table they query. If your users don't have the permissions
to access certain columns and rows in the table, they won't have access
to the columns and rows through the view. So, yes, views can be used to
alleviate locks because they -generally- aren't used to edit data.<br>
<br>
As far as locks and cursors, you can use views to edit data but you
don't want to do that if you can avoid it. There are simply too many
things to go wrong. If you see a view that starts with the word
"UPDATE" in it's code, that's a view that can be modified. Caveats abound in using views this way.<br clear="all"><br>-- <br>Joel Witherspoon<br><br>LinkedIn: <a href="http://www.linkedin.com/in/jcwitherspoon">http://www.linkedin.com/in/jcwitherspoon</a><br>
<br>Facebook: <a href="http://www.facebook.com/joel.witherspoon">http://www.facebook.com/joel.witherspoon</a><br><br>Skype: joel.witherspoon<br>