The Role Of Views In High Performance Oracle Databases

Introduction
Uses of Views
Optimising Views:
Integrating Views With The Rest Of The Query
Keeping Views Separate From The Rest Of The Query
Summary
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 :-

A horizontal subset of a table is simply a subset of the rows in the table - i.e. a restriction is added to the where clause to limit the rows returned by the query.
A vertical subset is just a subset of the columns in the table.

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:

Emp

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:-

CREATE OR REPLACE VIEW dept_3 AS
SELECT name, salary FROM emp WHERE dept_id = 3
WITH CHECK OPTION;

(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, t_{his 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 woopra.com to gather statistical information about our visitors. View woopra privacy policy .