Database Reference
In-Depth Information
Having said that, we should also note starting with Oracle 12
c
, you can now perform PDML on SecureFiles
LOB
s
without having to partition. In general, you no longer need to partition to use PDML.
■
Note
We will cover parallel operations in more detail in Chapter 14.
Query Performance
In the area of strictly read query performance (
SELECT
statements), partitioning comes into play with two types of
specialized operations:
•
Partition elimination
: Some partitions of data are not considered in the processing of the
query. We have already seen an example of partition elimination.
•
Parallel operations
: Examples of this are parallel full table scans and parallel index range
scans.
However, the benefit you can derive from these depends very much on the type of system you are using.
OLTP Systems
You should not look toward partitions as a way to massively improve query performance in an OLTP system. In fact, in
a traditional OLTP system, you must apply partitioning with care so as to not
negatively
affect runtime performance.
In a traditional OLTP system, most queries are expected to return virtually instantaneously, and most of the retrievals
from the database are expected to be via very small index range scans. Therefore, the main performance benefits of
partitioning listed previously would not come into play. Partition elimination is useful where you have full scans of
large objects, because it allows you to avoid full scanning large pieces of an object. However, in an OLTP environment,
you are not full scanning
large objects (if you are, you have a serious design flaw). Even if you partition your indexes,
any increase in performance achieved by scanning a smaller index will be miniscule—if you actually achieve an
increase in speed at all. If some of your queries use an index
and
they cannot eliminate all but one partition from
consideration, you may find your queries actually run slower after partitioning since you now have 5, 10, or 20 small
indexes to probe, instead of one larger index. We will investigate this in much more detail later when we look at the
types of partitioned indexes available to us.
As for parallel operations, as we'll investigate in more detail in the next chapter, you do not want to do a parallel
query in an OLTP system. You would reserve your use of parallel operations for the DBA to perform rebuilds, create
indexes, gather statistics, and so on. The fact is that in an OLTP system, your queries should already be characterized
by very fast index accesses, and partitioning will not speed that up very much, if at all. This does not mean that you
should avoid partitioning for OLTP; it means that you shouldn't expect partitioning to offer massive improvements
in performance. Most OLTP applications are not able to take advantage of the times where partitioning is able to
enhance query performance, but you can still benefit from the other possible partitioning benefits: administrative
ease, higher availability, and reduced contention.
Data Warehouse Systems
In a data warehouse/decision-support system, partitioning is not only a great administrative tool, but something that
will speed up processing. For example, you may have a large table on which you need to perform an ad hoc query.
You always do the ad hoc query by sales quarter, as each sales quarter contains hundreds of thousands of records
and you have millions of online records. So, you want to query a relatively small slice of the entire data set, but it is
not really feasible to index it based on the sales quarter. This index would point to hundreds of thousands of records,
