Question: I see
bad performance and slow query execution time when using a view
because of unnecessary table joins from the view. How can I
tune a query with views?
Answer:
An Oracle view is the encapsulation of a complex query
into a single pseudotable that behaves like a single
table. View add nothing to SQL performance and a view is
usually only used in ad-hoc query systems wher a beginner is writing
SQL and you want to hide the complexity of the SQL. Views
suffer from the issue of adding unnecessary complexity into a query.
There are teo solutions for slow performance in views:
- Re-write the SQL without the view: This will remove
the overhead.
- Get to release 11g and use the View join elimination
feature.
view join elimination. Starting
in 11g, the optimizer is intelligent and it will detect
situations where view data is not needed in the result set, and
btpass processing of that data, thereby greatly improving
performance of queries with views.
For example, here we create a view:
create or
replace view
cust_view
as
select
customer_name,
order_nbr,
item_desc
from
customer c,
order o,
item i,
where
c.cust_nbr = o.cust_nbr
and
o_item_nbr = i.item_nbr;
The pseudotable in the following query hides the
complexity of the underlying query and has no effect on
the performance of the underlying SQL:
select * from cust_view where
cust_nbr = 123;
In this example, every time the cust_view is queried,
Oracle will join the three tables at runtime.
Since views don't improve performance, why use them?
Most Oracle shops that employ views do so for end-user
queries or for queries where they want to hide
complexity and ensure uniform join methods.
Materialized
views
Oracle's solution to improving performance of standard
views is the materialized view. When you create a
material view, it prejoins all of the tables in a
complex query. Since all of the query joins have been
done, running SQL against the materialized view will be
far faster than with a standard view. However,
materialized views have some shortcomings:
- More storage is required—Since the
materialized view actually performs the query, extra
disk space is required to store the result table.
- Materialized views become stale—From the
moment the materialized view is created, the view may
become out-of-date. To periodically refresh a
materialized view, you can use a mechanism that's
similar to an Oracle snapshot.
The danger of using views
Some shops create complex views to represent large
subsets of their schema and allow developers and end
users to access these views. This approach often leads
to poor performance. Here are some situations to avoid
if you work with complex views:
- Querying subsets—Developers will often
query a subset of the complex view, not realizing that
all tables in the view will be joined.
- Adding complex WHERE clauses—Queries
against views with complex WHERE clauses will often
override any tuning hints that are placed within the
view, causing suboptimal execution plans.
- Hinting the view—A view can't be treated as
a finite table, and adding SQL hints to view queries
will often result in suboptimal execution plans.
Remember, any time the optimizer gets "confused," it
will perform an unnecessary full-table scan. While
hints can be used for specific SQL optimization, the
use of views is strongly discouraged with hints
because they can be invoked in many contexts.
To summarize, Oracle views are an encapsulation of a
complex query and must be used with care. Here are the
key facts to remember:
- Views are not intended to improve SQL performance.
When you need to encapsulate SQL, you should place it
inside a stored procedure rather than use a view.
- Views hide the complexity of the underlying query,
making it easier for inexperienced programmers and end
users to formulate queries.
- Views can be used to tune queries with hints,
provided that the view is always used in the proper
context.
Combining hints
and views
Although you must be careful when using hints against a
view, here are two ways you can use them without
creating performance problems:
- You can embed hints inside the view definition.
This is useful for cases where a view will be called
without a WHERE clause, but it can be quite damaging
to performance when the view result set is altered by
calling the view with a complex WHERE clause.
- You can add hints in the calling query. The
danger with using hints in views is that the context
of the query may change. When this happens, any
existing hints within the view definition may be
ignored, which can confuse the SQL optimizer and
result in an unnecessary full-table scan.
When views are invoked with certain WHERE clauses, the
context of the view may change, as will the
functionality of any SQL hints that may be embedded
inside the view.
This simple example shows how such a context change can
occur:
select
cust_name,
cust_address
from
cust_view
where
cust_nbr = 123;
We've invoked a view that performs a three-way table
join on execution, but the WHERE clause in the SQL
indicates that the user is interested only in data
within a single table. Any SQL hints that might be
embedded inside the view may be ignored.
Conclusion
By themselves, Oracle views, materialized views, and SQL
query hints are useful tools for Oracle tuning. However,
special care must be taken when implementing views to
ensure that developers and end users don't misuse them
and create performance problems
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
