• Can you (or should you) create an index on a view?
• Can you (or should you) create a trigger on a view?
• How are views stored?
• Is there any way to see the mapping between a view's column(s) and its underlying base table's column(s)?

View Fundamentals

Two of the best sources on view information and details are the Concepts and Applications Developer's Guide – Fundamentals guides. The "schema object" definition of a view is:

Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.

Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.

Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

Which data dictionary tables or views can you use to determine how much space a view uses? Before you start executing -

select table_name from dictionary where table_name like '%VIEW%';

-          don't, because the answer is a view requires no space (or nothing significantly measurable). The reason no space is required is because a view is nothing more than a stored query.

Suppose you have a view based on a large table. As a representative example, suppose the view is base on most of the base table's columns.

Using the HR schema, the STAFF view can be created from the EMPLOYEES table via:

SQL> create or replace view staff as
  2  select employee_id,
  3  last_name,
  4  job_id,
  5  manager_id,
  6  department_id
  7  from employees;

View created.

What happens when you try to create an index on employee_id in the STAFF view?

SQL> create index emp_id_idx on staff(employee_id);
create index emp_id_idx on staff(employee_id)
                           *
ERROR at line 1:
ORA-01702: a view is not appropriate here

There should be no question that you can create an index on the EMPLOYEES table because after all, EMPLOYEES is simply and nothing more than a table, and that is where indexes are associated.

What about triggers on a view? Just like indexes, the answer is no.

SQL> CREATE OR REPLACE TRIGGER Staff_trigger
  2    BEFORE DELETE OR INSERT OR UPDATE ON Staff
  3    FOR EACH ROW
  4  WHEN (new.employee_id > 0)
  5  DECLARE
  6      manager_id number;
  7  BEGIN
  8      manager_id := 122;
  9  END;
 10  /
CREATE OR REPLACE TRIGGER Staff_trigger
                          *
ERROR at line 1:
ORA-25001: cannot create this trigger type on views

Is the "no" answer an absolute? In other words, is there a workaround for this error? The answer is in this case is yes (with some limitations), and the solution involves using an INSTEAD OF trigger. Further investigation of the ORA-25001 error yields:

Simple Versus Complex Views

The STAFF view is a simple view. The counterpart to simple views is complex views. What defines or constitutes a complex view? There are two definitions of what constitutes a complex view. In the first case, any view containing more than one table or containing functions (or grouped data) is a complex view. Another reference to a complex view is "modifiable join view," which Oracle describes as:

Why are some views updateable and some aren't? The answer is related to the difference between simple and complex views. A general rule of thumb is that if a base table is key-preserved, DML can be performed against the view. Using the DISTINCT operator as an example, duplicate data is suppressed, so when an update is being attempted, which record should Oracle update? Oracle has no way of knowing, so that is why DML is not allowed. A book frequently used for database concepts, architecture or introduction is Database Systems: Design, Implementation, and Management (Thompson Course Technology). The text states the following: "If the primary key columns of the base table you want to update still have unique values in the view, [then] the base table is updatable." (page 358, 6^th ed.)

The general rule is this: In a complex view, some columns may be updateable, and some may not be – it depends on which table the column came from and if the primary key or the key-preserved value can be determined.

Mapping a View's Columns to the Originating Base Table(s)

Once a view is created, where can you view the view's source? The TEXT column of the USER_VIEWS table contains the SQL used to create the view.

SQL> select text
  2  from user_views
  3  where view_name='STAFF';

TEXT
------------------------------
select employee_id,
last_name,
job_id,
manager_id,
department_id
from employees

What if the STAFF view had been created with different column names? For example:

SQL> create or replace view staff2 
  2  (emp_id,
  3   emp_lastname,
  4   emp_job_id,
  5   emp_manager_id,
  6   emp_dept_id)
  7  as
  8  select 
  9    employee_id,
 10    last_name,
 11    job_id,
 12    manager_id,
 13    department_id
 14  from 
 15    employees;

View created.

Does USER_VIEWS show anything different for the SQL query used to create STAFF2?

SQL> select text from user_views
  2  where view_name = 'STAFF2';

TEXT
--------------------------------
select
  employee_id,
  last_name,
  job_id,
  manager_id,
  department_id
from
  employees

This example is simple in that only simple views were created. There is only one base table, so matching the view's column names to the base table's column names is trivial. What about the case where five tables are involved and not all of the columns are qualified with a table alias?

SQL> create or replace view which_table as
  2  select
  3    employee_id,
  4    start_date,
  5    hire_date,
  6    job_id
  7  from job_history natural join employees;

View created.

Using the simple example above, which table contains START_DATE? Without describing the tables or querying USER_TAB_COLUMNS, and without prior knowledge of the schema, there is no way to determine the origin of START_DATE. Unfortunately, there is no publicly available dictionary view that stores the mapping we are looking for. Again, going back to the description of "How Views are Stored" in the Concepts guide, this should not be surprising.

You can store the information yourself, or examine the contents of the USER_VIEWS.TEXT column. If you noticed in the example I used, a natural join was used to join the two tables. With respect to identifying columns, what key limitation exists when using natural joins? From error message ORA-25155, "column used in NATURAL join cannot have qualifier" is the show stopper here. An alternative approach is to use an equivalent JOIN…ON construct as that allows column qualifiers (table aliases) to be used.

In Closing

After reading this refresher on views, you should be able to conclude that simple views can be made complex, and complex views can be made simple, so to speak. A judicious choice of "key" columns can make most if not all DML operations available against a view. Although views have some limitations, most of them can be circumvented to some degree by going back to the base table(s) or using INSTEAD OF triggers.

Another type of view is referred to as an inline view, which exists only for the statement it appears in. The inline view appears in the FROM clause, and a commonly seen example of this occurs with a "top-N query." Other options for views include the FORCE keyword, and the WITH CHECK OPTION and WITH READ ONLY options. If you are not familiar with these options, you may be limiting yourself on what you are currently doing when it comes to using views.

Back to DBAsupport.com