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:
- 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.
- 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.
SELECT last_name, first_name, sum(tool_cost) employee_cost, Department_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:
- The in-line view is enclosed by parentheses.
- 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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have an Oracle or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle or SQL questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our Oracle and SQL gurus are waiting to answer your toughest questions.