I like views. I don't mean panoramic landscapes, though I do like them, too. But here, I'm talking about database views.
In its simplest form, a view simply provides a different way of accessing data within a table or two within your database. But done they are capable of so very much more; here are some uses I've found for them.
- Hiding calculations – fairly obvious, you can create a view–or series of views–that include complex calculations that are used multiple other places. My NaNoWriMo tracker is a great example of this–I have two views which together comprise at least 80% of the calculations required by the tool. Some of the calculations are analytic functions, so it's really nice to be able to write that once, tuck the logic away, and just trust it to work whenever I select current_target from the view.
- Hiding data – Got sensitive data that only certain users should be allowed to see? If Oracle's Virtual Private Database (VPD) isn't an option, this can be a great poor-man's substitute. I'd generally go with a VPD, because it's a more robust solution, but it also takes more work to set up. And, of course, if you look at a view's code, you can see whether something is being masked; VPD's don't give you that visibility. (Yes, there's positives to both sides of that last point!)
Creating data – Um, yeah. You can create data on the fly with views. I mentioned doing this in passing in my post on cartesian joins, but the technique is fairly simple. If you need a sequence of numbers, for instance, just do something like
select level var from dual connect by level < 10;
- Outer join to multiple tables - I love this one. Normally, if you try to outer join one table to two others, Oracle throws an error. But if you encapsulate those two tables in a view, you can outer join to that view, and Oracle proceeds happily. One place I tend to run into this is the E-Business Suite's PO tables--PO_HEADERS, PO_LINES, PO_RELEASES, PO_LINE_LOCATIONS--and being able to coax Oracle into doing what I want is wonderful.
- Enabling table restructures - I have to give credit for this one to a great friend and DBA that I worked with six years ago. We needed to revamp our entire table structure, but couldn't afford to take the business offline for the time it would take to do so (who could?). The solution he came up with was to create views based on the current table structure, and have all of the developers modify our code to point to the views instead of the tables. Once that was done, he was able to build the new tables, populate them with data, and update the view code to point to the new structure at his leisure. It was still a bit of work, but the risk of unwanted downtime was reduced to a minimum.
Of course, Uncle Ben's maxim that "with great power comes great responsibility" holds true for views. Because they are capable of hiding so much behind the scenes, it's very possible to write a simple-looking query with abysmal performance; it's the developer's responsibility to constantly review their query performances, and check to see if a view is truly necessary.