Donald K. Burleson Oracle Tips

New Oracle9i view constraints

Prior to Oracle9i, Oracle views were always dynamically generated from their base tables, and the view could not contain constraints.

As a quick refresher, Oracle views are used to simplify a complex query by hiding all of the internal table joins operations. 

For example, here is a view of display order information for the Widget products:

create or replace view

   widget_orders

as

select

   cust_name,

   order_date,

   product_name,

   sum(qty*price) total cost

from

   customer

natural join

   orders

natural join

   order_item

natural join

   product

where

   product_type = ‘widget’;

Once this view is defined, we can issue complex queries against the view:

select * from widget_orders where order_date > sysdate-5;

The problem with traditional views is that referential integrity constraints cannot be defined against our view.  Starting in Oracle9i, Oracle supports the following constraints on views:

• NOT NULL – This is always inherited directed from the base tables that make-up the view.

• Unique constraints – Oracle9i allows for unique constraints to be defined upon any column of the view.

• Primary key – Today we can get primary key constraints defined directly upon the view.

• Foreign key – Foreign key referential integrity is now directly available whenever a view has foreign key dependencies against other base tables.

As you may know, managing referential integrity within views can have a severe impact on query performance.  Here is a great article on this subject called Guard against performance issues when using Oracle Views.

With Oracle9i, we can bypass the traditional problems associated with non-constrained views.  Here is an example of a primary key constraint created on a view:

alter view

   widget_orders

add constraint

   widget_orders_pk

primary key

   (cust_name, order_date, product_name)

disable novalidate;
 

If you like Oracle tuning, check out my latest book "Oracle Tuning: The Definitive Reference".  It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts.