Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Create Oracle Views tips

Oracle Tips by Burleson Consulting

Oracle Views

As a DBA one of the types of objects you will manage will be views. In this section we will introduce you to Oracle view constructs. Following your introduction to views, we will discuss the benefits and downsides of views in Oracle.

Inside Oracle Views

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For example, if we frequently issue the following query

SELECT empid FROM emp;

I might well want to make this a view (the reality is that we would probably never create a view for a statement this simple but we wanted to use an easy example).

To create a view use the create view command as seen in this example

CREATE VIEW
   view_emp
AS
   SELECT empid FROM emp;

This command creates a new view called VIEW_EMP. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this:

SELECT * FROM view_emp WHERE empid BETWEEN 500 AND 1000;

And Oracle will transform the query into this:

SELECT * FROM (select empid from emp) WHERE empid BETWEEN 500 AND 1000;

Benefits of Oracle Views

Oracle views offer some compelling benefits. These include:

* Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it?s less likely to require parsing. This is because the basic underlying SQL that is called is always the same. However, since you can add additional where clauses when calling a view, you still need to use bind variables. Additional where clauses without a bind variable can still cause a hard parse!

* Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.

* Predicate pushing. Oracle supports pushing of predicates into a given view. Assume we had a set of layered views, like this:

-- View One
CREATE VIEW vw_layer_one
AS SELECT * FROM emp;
-- view two
CREATE VIEW vw_layer_two_dept_100
AS SELECT * FROM vw_layer_one
WHERE deptno=100;

Then assume we issued this query:

SELECT * FROM vw_layer_two_dept_100
WHERE empid=100;

The predicate in this statement is the where empid=100 statement. You may have one of tens or even hundreds of predicates (if you have hundreds, we don?t want to be supporting your SQL code!). Oracle will, in many cases, push those predicates down into the views being called. Thus, Oracle will transform the VW_LAYER_ONE view into a SQL statement that looks like this:

CREATE VIEW vw_layer_one
AS SELECT * FROM emp
WHERE deptno=100
AND empid=100;

Note that both the predicate from view two (where deptno=100) and the predicate from the SQL statement being executed (where empid=100) are pushed down into the final view that is executed. This can have significant performance benefits because now the bottom view can possibly use an index if one exists on deptno and/or empid.

Predicate pushing has a number of restrictions that are beyond the scope of this book, but you can find them in the Oracle documentation. Also, any predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:

SELECT * FROM vw_layer_two_dept_100
WHERE empid=:b100;

The downside to using Views

Views are very handy but they get badly abused, which is a shame. I?ve seen views that return 50 columns, and have 40 predicates used to return just two or three columns that could easily have been retrieved from a simple SQL query. This is clearly a case of view abuse, and can lead to badly performing views.  Also see: Guard against performance issues when using Oracle hints and views.

Stacked views can also mask performance problems. Again, they can result in innumerable columns being returned when all you really need are a few of those columns. Also, predicate pushing tends to break down as you stack more views on top of more views. If you are going to start stacking views, carefully review the rules for predicate pushing in the Oracle documentation. They are rather long and involved!

Oracle views notes:

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.