Thursday, September 3, 2009

The Case For Views

I recently had to "defend" my use of views.

To me, they seem natural. Using them is almost always a good thing to do. I've met those that don't really care for them...I just never understood why. Then again, those same people are still not convinced of PL/SQL APIs. Maybe there is something to that mindset...

Being forced to articulate one's views is a good thing, it's part of why I blog. I won't lie though, it gets frustrating to have do this, seemingly, all the time.

I'm going to do it here, again.

Complex Joins
No, I'm not afraid of joins. I am afraid of others who are afraid of joins though. More specifically, I'm afraid of those who aren't proficient at writing SQL. Let me do it, once, and let everyone else have access to the view. Besides, I'm the subject matter expert (SME) on the given set of tables, so it follows that I should create the interface to those tables.

Yes, I said interface. It's exactly what a view is and interface to the underlying data.

Write it once and let it propogate everywhere.

When I had to "defend" my use of views, I mistakenly used the example of adding columns. Oops. That would (possibly) require changes throughout the system. I meant to say remove columns, in which case you could keep the placeholder in the view using NULL without having to change all of the code. This does not mean that proper analysis does not need to be performed, it does, but you could possibly get away with not having to change everything that references the view.

My second example was a derived value. This makes more sense to some people thankfully. I've seen the same calculation done on a specific field done 10s, even 100s of times throughout the code. Why not do it one time? Perfect use for views.

Following the least privileges necessary to perform a given action, views allow you to give access to the data without direct access to the tables. Views can also be used to hide or mask data that certain individuals should not have access to. In conjunction with VPD or Application Contexts, it's a powerful way to prevent unauthorized access.

Maintenance has been alluded to above, but not explicitly stated.

For derived values: If you have a derived or calculated value and that calculation is performed all over the place, what happens when it changes? You have to update it everywhere. If you had used a view, change it once and it propogates everywhere. What was once a project is now a "simple" code change. This affects IT in how they choose and assign resources as well as the Business.

For complex joins: What if one table is no longer used or needed? What if that table is littered throughout the code base? You have a project on your hands.

If that table were part of a view, you could "simply" remove it, keep the columns in the view and you're done. There might be places where code needs to be adjusted, but overall, you have a much smaller impact. That's a good thing.

I tried putting the following statement in a category up above, but couldn't make it fit.

Records in a table typically constitute data. Tables, joined together, in a view, tend to turn that data into information.


Tim... said...


Views are good. Developers using views can be very bad. They have a tendency to start joining them together and before you know it you've got a 20 table join because they wanted to add 2 extra columns an existing view. :)

I hate the misuse of views, not views themselves. :)



oraclenerd said...


I, of course, have that tendency as well. I am conscious of it though and "knowing" is half the battle right?

I try not to nest them too much. But I do find it useful at times to do so (a certain calculation should carry through). Fortunately, these aren't views on top of huge tables (nor will they ever be). The are mostly rules consisting of tables less than 1000 rows (I'm saying that in regards to the nesting aspect).

For the larger tables, maybe one on the base table and then use that in combination with another.

Anonymous said...

Turn data into information? That doesn't make a whole lot of sense to me-- All data is information. Can you clarify that statement a little?

For that same reason, I don't think data encapsulation exists in a database. The database is for storing data, not hiding it. Encapsulation is a feature of objects that hide the details of the data while //doing// something.