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
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 timeSELECTOf 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!
name,
address1,
address2,
city,
state,
zip,
city || ', ' || state || ' ' || zip AS web_city_state_zip
FROM vw_person;
That's my haphazard list for using database views.
What about you? Do you use them or prefer not to? If not, why not?
2 comments:
Be careful if you use analytics in your view, as we have found that it will calculate the analytics on the whole view first, and then apply any where clause, instead of the other way round.
Annoying and performed badly when all you were after was the set of information for just one of the primary keys! We found it much quicker and easier to go straight to the tables in that instance, although it's less pretty in terms of code neatness!
Yep, by all means views are a valuable option.
"Option" being the operative term here.
Certainly when a certain analytic (general use of the term, not the Oracle SQL-specific context) is well established and understood, a view would be an appropriate way to codify that understanding.
In the absence of a high level of confidence in the data, and your understanding of it, though, creating a view to acquire data through can be a risky proposition. By this I mean that it can provide you with a false sense of confidence in the results it provides.
In this sense, I view a view (view a view...hmmmm) as an end-process tool.
If I want to have the absolute best chance of uncovering a subtle error in my SQL logic (or business logic) I go straight to the table so I'll know I'm not inadvertently including/excluding instances of data due to poorly-placed or outdated WHERE conditions. Boneist's observation is another example of how things can go wrong with SQL logic in a subtle way.
That's my two-cents worth!! That and two bucks will get you....
Post a Comment