To subscribe just go to www.asktheoracle.net/oracle-tips-signup.html
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
The first step to optmising views in Oracle is to tune the underlying query. (Refer to oracle-performance-tuning-part1.htm , oracle-performance-tuning-part2.htm and oracle-performance-tuning-part3.htm, for discussions on other tuning issues).
The next step is to tune the queries using the view. There are 2 ways that views can be processed by the Oracle optimizer. (1) the view is fully integrated into the query - the view is processed first and then the rest of the query conditions will be applied to produce the final result set. Depending on the sizes of the tables involved, this may cause performance problems. (2) the view is processed separately from the rest of the query - all the conditions of the query can be applied to the view producing a smaller result set.
You need to be aware that if view contains a set operation such as GROUP BY, SUM, COUNT, DISTINCT, MAX, MIN, etc. then it can't be integrated into the query.
This is likely to be desirable when the view produces a large result set or the result set needs to be filtered by additional conditions in the query. The Oracle optimiser will do this automatically if it can, but let's look at an example where this wouldn't happen.
Let's assume we run an international book store called Amazing Books and we've created a view on our database called CUR_mth_SALES to show the current month's sales per book with each book identified by its ISBN. The view is defined as follows:
CREATE VIEW cur_mth_sales AS
SELECT isbn, sum(sales_val) total_sales
FROM cur_mth_transactions
GROUP BY ISBN;
As this isn't very user friendly, because we have to look up the title of each book separately, after a few months we decide to join this view with the BOOKS table which translates the ISBN into a title, as follows:
SELECT name,
total_sales
FROM books JOIN cur_mth_sales USING (ISBN);
using the ANSI syntax, or
SELECT name,
total_sales
FROM books
, cur_mth_sales
WHERE books.isbn = cur_mth_sales.isbn;
using the Oracle syntax.
This gives us what we want - the book title and each book's sales for this month - the only problem is that it's very slow so we need to optimize it. Looking at the query and the view we can see that their both at the same level i.e. the ISBN level, so the obvious solution is either to redefine the view to return the book name or to dispense with it altogether. As we're discussing views, let's do the former, so our view would be defined as follows:
CREATE VIEW cur_mth_sales AS
SELECT name
, sum(sales_val) total_sales
FROM book
, cur_mth_transactions cmt
WHERE book.isbn = cmt.isbn
GROUP BY name;
and our query would now look like this:
SELECT name, total_sales FROM cur_mth_sales;
This has the added advantage that our final query is now much simpler.
Normally with Oracle, we want views to be integrated with the rest of the query to provide the best performance, however if the query contains a GROUP BY clause, the grouping won't occur until after the tables have been joined. This would have a detrimental effect on performance if the cur_mth_transactions table were much larger than the book table because a much larger result set would have to be sorted.
The way to overcome this is to force the view to be evaluated first, by moving the GROUP BY clause into the view (as in our first example).
CREATE VIEW cur_mth_sales AS
SELECT ISBN, sum(sales_val) total_sales
FROM cur_mth_transactions
GROUP BY ISBN;
The query would be as follows:
SELECT name, total_sales
FROM books , cur_mth_sales
WHERE books.isbn = cur_mth_sales.isbn;
With the view containing the GROUP BY clause, only the cur_mth_transactions table has to be sorted and so performance would be much better in this case.
Since Oracle 7.2 there has been a way to create implicit views by using a sub query in the FROM clause so the query could also be written as follows:
SELECT name, total_sales
FROM books ,
(SELECT ISBN,
SUM(sales_val)
total_sales
FROM cur_mth_transactions
GROUP BY ISBN)
WHERE books.isbn = cur_mth_transactions.isbn;
Views have a useful role to play in any application and can be used to help improve Oracle performance, but every query using views or not needs to be tested and examined to make sure that the performance is not suddenly degraded when the application goes live because the impact of the change in data volumes has not been considered.
Click here for Oracle performance tuning part 5
---------------------------------------
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 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 .
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.