Database Reference
In-Depth Information
and doing the index range scan in this way would be terrible (refer to Chapter 11 for more details on this). A full table
scan is called for to process many of your queries, but you end up having to scan millions of records, most of which
won't apply to your query. Using an intelligent partitioning scheme, you can segregate the data by quarter such
that when you query the data for any given quarter, you will full scan only that quarter's data. This is the best of all
possible solutions.
In addition, in a data warehouse/decision-support system environment, parallel query is used frequently.
Here, operations such as parallel index range scans or parallel fast full index scans are not only meaningful, but also
beneficial to us. We want to maximize our use of all available resources, and parallel query is a way to do it. So, in this
environment, partitioning stands a very good chance of speeding up processing.
Reduced Contention in an OLTP System
The last general benefit area for partitioning is potentially increasing concurrency by decreasing contention in an
OLTP system. Partitions can be used to spread the modifications of a single table out over many physical partitions.
The idea being if you have a segment experiencing high contention, turning it into many segments could have the side
effect of reducing that contention proportionally.
For example, instead of having a single table segment with a single index segment, you might have 20 table
partitions and 20 index partitions. It could be like having 20 tables instead of 1 (and 20 indexes instead of 1), hence
contention would be decreased for this shared resource during modifications.
Table Partitioning Schemes
There are currently nine methods by which you can partition tables in Oracle:
•
Range partitioning
: You may specify ranges of data that should be stored together. For
example, everything that has a timestamp within the month of
Jan-2014
will be stored in
partition 1, everything with a timestamp within
Feb-2014
in partition 2, and so on. This is
probably the most commonly used partitioning mechanism in Oracle.
•
Hash partitioning
: You saw this in the first example in this chapter. A column (or columns) has
a hash function applied to it, and the row will be placed into a partition according to the value
of this hash.
•
List partitioning
: You specify a discrete set of values, which determines the data that should be
stored together. For example, you could specify that rows with a
STATUS
column value in
( 'A', 'M', 'Z' )
go into partition 1, those with a
STATUS
value in
( 'D', 'P', 'Q' )
go
into partition 2, and so on.
•
Interval partitioning
: This is very similar to range partitioning with the exception that the
database itself can create new partitions as data arrives. With traditional range partitioning,
the DBA was tasked with pre-creating partitions to hold every possible data value, for now
and into the future. This typically meant that a DBA was tasked with creating partitions on a
schedule—to hold next months' or next weeks' data. With interval partitioning, the database
itself will create partitions as new data arrives that doesn't fit into any existing partition based
on a rule specified by the DBA.
•
Reference partitioning
: This allows a child table in a parent/child relationship enforced by
a foreign key to inherit the partitioning scheme of the parent table. This makes it possible
to equi-partition a child table with its parent table without having to denormalize the data
model. In the past, a table could only be partitioned based on attributes it physically stored;
reference partitioning in effect allows you to partition a table based on attributes from its
parent table.
