OracleBlog

Very often the solution to a problem involves using a view. Use Views!

What is a View?

Most people think of a view either as a stored, named query, or as a "virtual table."

Here are two definitions from Oracle, along with references to the key documents on Views to review.

"A logical representation of a table or combination of tables."
- Oracle Application Developer's Guide, Section 2.11 (Views)

"A view takes the output of a query and presents it as a table."
- Oracle Concepts Guide, Section 10.16 (Views)

How do you create a View?

Nothing could be easier. Here:

CREATE OR REPLACE VIEW MyView AS
(Insert Query Here!)

For more, you can always reference Dan Morgan's library:
http://www.psoug.org/reference/views.html

How do I use Views?

Treat it just like a table. You can query them, as well as insert, update and delete*. Bear in mind that these actions will update the base table(s). Likewise, any changes to the base table(s) will automatically update the View*.

*This is true in general. Some Views can't be updated, and you can make a View READONLY.

Here is how to tell what Views exist:

SELECT view_name FROM user_views;

Here is how to tell what the View is:

SELECT text FROM user_views WHERE view_name = 'MyView';

Why are they useful?

I use them for many reasons, here are the most common situations where I will advocate their use:

1. Denormalizing the database

Sometimes its nice to pull related data from several tables into a single table. Using views allows you to satisfy both the Fabian Pascalesque relational database purist, and the pragmatic user.

2. Making things look better

For example, you can use a view to substitute a generic ID number with the name of the individual. You can leave out columns that are rarely interesting. You can use a view to add information that is derived from other data, for example figure out everyone's salary in Canadian dollars in an international organisation.

3. Complex queries/query simplification

You might have several queries that have to perform similar logic. You can just create a view for that logic, and make the queries far simpler. Also, when I can't figure out how to write a really complex query, sometimes I just create views as intermediate steps.

For example, say you have a table of expenses, with "department", "type" and "total", a revenue table with the same columns, and a department table. Now you have to put together a query that shows each department, and their total expenses and revenues, regardless of type.

Rather than write a really complex query, just write a view (or two) that contains the sum of the expenses and revenues, by department. Then you can create a query to put them all into a single row by (outer) joining on those views. Simple!

4. Restrict access to a column or row

Say you want a user to have access to only part of a table (either by row or column). Here's what you do: restrict his access to the table completely, then create a view that contains the information the user is allowed to access, and then grant that user access to the view.

5. Rename a column

Create a view which is exactly like the table, but with the column renamed. That is really easy, and it will save you from having to possibly update lots of other tables or applications because you left the base table alone. No, you can't write indexes on views, but queries get optimized as normal, and it will use the base table's indexes at that time.

6. Change schema, but still support an old version

Just like renaming a column. You can completely re-design your database schema, but create views to support legacy applications who still rely on the structure and naming of the original. That will save a lot of hassle.

I will close with a really interesting aspect of Views.

SQL> CREATE TABLE BaseTable (MyNumber NUMBER(1));

Table created.

SQL> INSERT INTO BaseTable (MyNumber) VALUES (1);

1 row created.

SQL> CREATE VIEW MyView AS SELECT * FROM BaseTable;

View created.

SQL> ALTER TABLE BaseTable ADD (MyString VARCHAR2(32));

Table altered.

SQL> INSERT INTO BaseTable (MyNumber, MyString) VALUES (2, 'Hello');

1 row created.

SQL> SELECT * FROM MyView;

MYNUMBER
----------
1
2

SQL> SELECT * FROM BaseTable;

MYNUMBER MYSTRING
---------- --------------------------------
1
2 Hello

The View is NOT updated when the BaseTable is, even when you SELECT *. When you write SELECT * the view chooses its columns then and there.