Database Views: Love 'em or Leave 'em? A view in Oracle is essentially a query you can store in the database.

I use them quite heavily in my own systems. There are a few reasons that I like them and use them and in no particular order, here they are:

Complex Joins

Say you want to flatten out a customer's data. Typically a customer is comprised of 1 or more phone numbers, 1 or more addresses, 1 or more email addresses and their demographic information (Gender, DOB, etc). If you use something like the Party Model, that will require at least 5 tables:

PERSON
ENTITY
ADDRESS
PHONE
EMAIL

Which in turn requires 4 joins. I typically make a view on those tables and then reference the view in all my code (naturally the exception is where there are INSERTs/UPDATEs/DELETEs).

I don't necessarily consider this a complex join, but it's a lot of typing going on and with that the potential for errors. My personal preference is to create the view and then grant access to the view, not the underlying tables (security).

Maintenance and Support

These 5 tables would probably be at the core of just about any system that requires user interaction. Let's say you had 50 packages referencing these tables and you need to make a change...either add a column, alter a column or just drop a column. You potentially have 50 packages that are now invalid because of this because they all reference one or more of these tables. You would then have to go through each package to determine whether or not a change was actually required, or you could just recompile the schema (DBMS_UTILITY.COMPILE_SCHEMA). After recompile though you still had 42 packages in an invalid state.

If you used the view, after it's update and recompile, you may (hopefully) have only 1 or 2 packages that require updating. Far and away a better prospect than 42!

Logic

What if you web application needed the address in a specific format? In the view you can create that one time

SELECT 
  name,
  address1,
  address2,
  city,
  state,
  zip,
  city || ', ' || state || '  ' || zip AS web_city_state_zip
FROM vw_person;

Of course that can be done on the table itself now with Virtual Columns. Let everyone know about it and they don't have to rewrite it (support and maintenance). Done!

That's my haphazard list for using database views.

What about you? Do you use them or prefer not to? If not, why not?

Labels: database, views

  ¶ 1/16/2009 11:20:00 PM