Database Reference
In-Depth Information
Chapter 15
Beyond Data Access and Join
Optimization
The optimization of data accesses and joins must be performed before considering the advanced optimization
techniques presented in this chapter. In fact, the optimization techniques described here are intended only to further
improve performance when it isn't possible to achieve it otherwise. In other words, you should fix the basics first, and
then, if the performance is still not acceptable, you can consider special means.
This chapter describes how materialized views, result caches, parallel processing, direct-path inserts, row
prefetching, and the array interface work and how they can be used to improve performance. Each section that
describes an optimization technique is organized in the same way. A short introduction is followed by a description of
how the technique works and when you should use it. All sections end with a discussion of some common pitfalls
and fallacies.
■
In this chapter, several SQL statements contain hints in order to show you examples of their utilization. In any
case, neither real references nor full syntaxes are provided. You can find these in Chapter 2 of the
Oracle Database
SQL Language Reference
manual.
Note
This chapter shows the results of different performance tests. The performance figures are intended only to help
you compare different kinds of processing and to give you a feel for their impact. Remember, every system and every
application has its own characteristics. Therefore, the relevance of using each technique might be very different,
depending on where it's applied.
Materialized View
A
view
is a virtual table based on the result set returned by the query specified at view creation time. Every time a view
is accessed, the query is executed. To avoid executing the query for every access, the result set of the query can be
stored in a
materialized view
. In other words, materialized views simply transform and duplicate data that is already
stored elsewhere.
■
Materialized views can also be used in distributed environments in order to replicate data between databases.
This usage isn't covered in this topic.
Note
