Views

For example, the EMP table has several columns and numerous rows of information. If you only want users to see five of these columns, or only specific rows, you can create a view of that table for other users to access. Figure 5 - 5 shows an example of a view called STAFF derived from the base table EMP. Notice that the view shows only five of the columns in the base table.

Figure 5 - 5. An Example of a View

Since views are derived from tables, many similarities exist between the two. For example, you can define views with up to 254 columns, just like a table. You can query views, and with some restrictions you can update, insert into, and delete from views. All operations performed on a view actually affect data in some base table of the view and are subject to the integrity constraints and triggers of the base tables.

For More Information

Note: You cannot explicitly define integrity constraints and triggers on views, but you can define them for the underlying base tables referenced by the view.

Storage for Views

How Views Are Used

• to provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table

• to hide data complexity

• to simplify commands for the user

• to present the data in a different perspective from that of the base table

• to isolate applications from changes in definitions of base tables

• to express a query that cannot be expressed without using a view

• to save complex queries

• to achieve improvements in availability and performance

The Mechanics of Views

NLS Parameters

Using Indexes

CREATE VIEW emp_view AS 

	SELECT empno, ename, sal, loc 

	FROM emp, dept 

	WHERE emp.deptno = dept.deptno AND dept.deptno = 10; 

Now consider the following user-issued query:

SELECT ename 

	FROM emp_view 

	WHERE empno = 9876; 

The final query constructed by Oracle is

SELECT ename 

	FROM emp, dept 

	WHERE emp.deptno = dept.deptno AND 

	      dept.deptno = 10 AND 

	      emp.empno = 9876; 

In all possible cases, Oracle merges a query against a view with the view's defining query (and those of the underlying views). Oracle optimizes the merged query as if you issued the query without referencing the views. Therefore, Oracle can use indexes on any referenced base table columns, whether the columns are referenced in the view definition or the user query against the view.

In some cases, Oracle cannot merge the view definition with the user-issued query. In such cases, Oracle may not use all indexes on referenced columns.

Dependencies and Views

Updatable Join Views

An updatable join view is a join view, which involves two or more base tables or views, where UPDATE, INSERT, and DELETE operations are permitted. The data dictionary views, ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS, contain information that indicates which of the view columns are updatable.

Table 5 - 1 lists rules for updatable join views.

Rule	Description
General Rule	Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.
UPDATE Rule	All updatable columns of a join view must map to columns of a key preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.
DELETE Rule	Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
INSERT Rule	An INSERT statement must not, explicitly or implicitly, refer to the columns of a non-key preserved table. If the join view is defined with the WITH CHECK OPTION clause, then INSERT statements are not permitted.

Table 5 - 1. Rules for INSERT, UPDATE, and DELETE on Join Views

Partition Views

For example, sales data for a calendar year may be broken up into four separate tables, one per quarter: Q1_SALES, Q2_SALES, Q3_SALES and Q4_SALES.

Partition Views Using Check Constraints

ALTER TABLE Q1_SALES ADD CONSTRAINT C0 check (sale_date between
  'jan-1-1995' and 'mar-31-1995');
ALTER TABLE Q2_SALES ADD CONSTRAINT C1 check (sale_date between
  'apr-1-1995' and 'jun-30-1995');
ALTER TABLE Q3_SALES ADD CONSTRAINT C2 check (sale_date between
  'jul-1-1995' and 'sep-30-1995');
ALTER TABLE Q4_SALES ADD CONSTRAINT C3 check (sale_date between
  'oct-1-1995' and 'dec-31-1995');
CREATE VIEW sales AS
  SELECT * FROM Q1_SALES UNION ALL 
  SELECT * FROM Q2_SALES UNION ALL 
  SELECT * FROM Q3_SALES UNION ALL
  SELECT * FROM Q4_SALES; 

This method has several advantages. The check constraint predicates are not evaluated per row for queries. The predicates guard against inserting rows in the wrong partitions. It is easier to query the dictionary and find the partitioning criteria.

Partition Views Using WHERE Clauses

CREATE VIEW sales AS 
  SELECT * FROM Q1_SALES WHERE sale_date between
  'jan-1-1995' and 'mar-31-1995' UNION ALL 
  SELECT * FROM Q2_SALES WHERE sale_date between
  'apr-1-1995' and 'jun-30-1995' UNION ALL 
  SELECT * FROM Q3_SALES WHERE sale_date between
  'jul-1-1995' and 'sep-30-1995' UNION ALL 
  SELECT * FROM Q4_SALES WHERE sale_date between
  'oct-1-1995' and 'dec-31-1995';  

This method has several drawbacks. First, the partitioning predicate is applied at runtime for all rows in all partitions that are not skipped. Second, if the user mistakenly inserts a row with sale_date = 'apr-4-1995' in Q1_SALES, the row will "disappear" from the partition view. Finally, the partitioning criteria are difficult to retrieve from the data dictionary because they are all embedded in one long view definition.

However, using WHERE clauses to define partition views has one advantage over using check constraints: the partition can be on a remote database with WHERE clauses. For example, you can use a WHERE clause to define a partition on a remote database as in this example:

SELECT * FROM eastern_sales@east.acme.com WHERE LOC = 'EAST'
  UNION ALL
  SELECT * FROM western_sales@west.acme.com WHERE LOC = 'WEST';

Because queries against eastern sales data do not need to fetch any western data, users will get increased performance. This cannot be done with constraints because the distributed query facility does not retrieve check constrains from remote databases.

Benefits of Partition Views

• partition elimination

• partition-level query optimization

    SELECT orderno, value, custno FROM orders  
      WHERE order_date BETWEEN '30-JAN-95' AND '25-FEB-95'; 

This query involves just a few days of data for ORDERS_JAN and most of the data for ORDERS_FEB. Given this, the optimizer may come up with a plan that uses indexed access of ORDERS_JAN and a full scan of the table ORDERS_FEB. Examination of the remaining 10 partitions will be eliminated since the query does not involve them.

Partition views are especially useful in data warehouse environments where there is a common need to store and analyze large amounts of historical data.

For More Information