Oracle training, tutorials and consulting


For Better, Faster, Smarter, Oracle Training and Consultancy


The Role Of Views In High Performance Oracle Databases

Uses of Views
Optimising Views:
      Integrating Views With The Rest Of The Query
      Keeping Views Separate From The Rest Of The Query
Further Reading

Uses of Views

Views are useful for providing a horizontal or vertical subset of data from a table (possibly for security reasons) ; for hiding the complexity of a query; for ensuring that exactly the same SQL is used throughout your application; and in n-tier applications to retrieve supplementary information about an item from a related table.

Vertical/Horizontal Subsets

Now let's look at these uses in a little more detail :-

You might use these for security reasons - if the view is created using the "with check option" then any updates made using the view will only succeed if the row(s) being updated could be selected by the view. Let's use the following data to provide a concrete example:


Id Name Salary Dept Id
1 Jack Jones       20000 3
2 Mike Lewis 30100 2
3 Susan Charles 22060 3
4 Anne Malloy 21230 4
5 Bharat Patel 34750 3

Assuming we wish to create a view on emp for each department to prevent any changes being made to records of employees not in that department, we can do that as follows:-

SELECT name, salary FROM emp WHERE dept_id = 3

(This is both a horizontal subset and a vertical subset of emp) . This means only the record of employees in department #3 can be updated using this view.

How does that help performance? The answer is only indirectly - it does save you from writing extra code (which has to be tested and tuned) but the real advantage is that this checking is performed by the database as part of the kernel and so will be much faster than any code you produced to do the checking. Depending on the size of your Oracle database, your transaction throughput etc., this slight increase in performance may be negligible or it may be significant, but it's always better to use core features provided by Oracle rather than create your own - why reinvent the wheel?

Hiding the Complexity Of Queries

Can this improve performance? By itself, no. In fact it may actually degrade performance. Let me explain. Suppose you create a view to hide the details of one rather complicated query, involving the join of several tables and then create another view for a different query which again involves joining several tables.

You then decide that you need a query which is the intersection (join) of these two views, so you create a query that does just that - join the two views together. Any harm in that? Unfortunately, there may be, the Oracle optimizer is not always very good at resolving the underlying views into their constituents. Consequently, queries involving joins of views may not perform very well.

So what's the answer, don't use views of views ? No, just be aware that if the underlying views are complex you may have performance problems. The best thing is to use "explain plan" to determine the execution path and see if it looks sensible, if it does you may be OK, if not try changing the order of the views or using a few hints and re-running "explain plan" and the query to see what difference it makes.

Ensuring the same SQL is used throughout your application

A view, by definition is merely a pre-written SQL query, therefore by using the view you are using the pre-defined query, more importantly though, you are using exactly the same query. This means that the query has to be parsed and the execution path determined only once - the first time it is used.

Subsequent uses of the query will avoid the overhead of parsing the query and determining the execution plan. Generally this is a good thing. There is a caveat however - if the data is highly skewed and the cost-based optimiser is being used, this may not be desirable as the initially chosen execution plan may not be right for all elements of the data (assuming that the query uses a bind variable and not a constant, of course).

For example, suppose we had a database of the names and addresses of all UK citizens. There would be many more people with the surnames Smith,Jones,Brown or Williams then there would be people with surnames starting with Z. If an index were to be created on surname and the query were to be tuned to use the index, the stored execution plan would be the good for retrieving the Aardvarks and the Zebberdees but not so good for retrieving the Smiths and the Browns. In those situations, you would need two or more views, if you knew how the data was distributed and when the common and uncommon names were being requested.

If we ignore the problem of skewed data, then there is another advantage of using views. Once the underlying query has been tuned it can be used as is by developers, who would not need to individually tune their queries, obviously reducing the development time.

Click here to go to part 3 of this article and learn how to optimise the use of views in Oracle


Looking for more Oracle tips and tricks ?

If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.

Smartsoft Computing Ltd
Bristol, England

Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses to gather statistical information about our visitors. View woopra privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.

© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.

Search for: