Problem solve Get help with specific problems with your technologies, process and projects.

Using in-line views

This tip discusses views, problems that occur and an Oracle device that solves those problems.

A view is a SELECT statement that is stored within the database. Views are used to create a virtual record at run time. The view is placed in the FROM clause of a SELECT statement. When the SELECT statement is executed, the SELECT statement contained within the view is executed and returns the virtual record to the calling SELECT statement.

Views are especially useful to mask SQL complexity or to compute records for use in a select statement. However there are two problems that can occur with Views. These are:

  1. Since views are so extremely useful for creating special records for a SELECT statement, the database can a large number of views. This can confuse the DA/business analysts. Which views are really business objects and which are only used to create records? Reducing the number of objects in a schema will increase business analyst efficiency.
  2. The locations and names of database schemas often change. When this happens, the views must be recreated or transferred. This causes increased work for the DBA. It also increases the chances for reports and queries to quick working when the view is not re-created in the move or is deleted through error.
Oracle has a device that that eliminates this problem. They are called in-line views. An in-line view is actually the placement of the view as a subquery within the FROM clause of the calling SELECT statement. The following is an example of an in-line view that calculates the average cost of a department's tools and joins this record to an employee record.

SELECT last_name, first_name, sum(tool_cost) employee_cost,
FROM Employee,
   (select fk_department, sum(tool_cost) department_cost
    from employee, emp_tools
    group by fk_department) d
WHERE employee.fk_department = d.fk_department
GROUP by last_name, first_name, department_cost;

Notice the following:

  1. The in-line view is enclosed by parentheses.
  2. A table alias must be created for the in-line view.

Using the above technique will allow you reduce the number of views that reside in your database schema. This will yield the results described above.

About the Author

John Palinski has been an Oracle developer and project manager for the last fourteen years. He developed one of the first relational database work management systems in the country. Mr. Palinski also teaches his own Oracle courses at Iowa Western Community College and the University of Nebraska at Omaha. Mr. Palinski is the author of the Oracle Database Construction Kit published by QUE and provides custom Oracle training worldwide through his consulting business, Realistic Software Training.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.