A Practical Guide to Data Warehousing in Oracle – Part 3


One feature that is almost ubiquitous in Oracle data warehousing is the use of the Partitioning Option. Over the past few major releases the feature set associated with partitioning has increased to cover four different partitioning types and improvements in the manageability of partitioned objects.

While this is to be welcomed, don’t
forget the caveat against grabbing every shiny feature of the software, that I
gave in Part
of this series. Therefore, at the same time that you are drooling over
Metalink Note 125314.1 "Boosting Performance by Hash and Composite
you should also be reading Metalink Note 165461.1 "Top
Partitioned Indexes Bugs."
Do not assume that every feature is going
to work flawlessly – if you plan to use Partition Change Tracking for your
materialized view refresh process, then search Metalink for known issues and
test the refresh process several times on your production data volumes on the
same operating system and Oracle versions and patch-levels that you will be
using on production. Also, have a plan ready in case the process fails.

Pretty pessimistic stuff, but there
is nothing like attempting a PCT-based fast refresh and finding your
materialized views mysteriously empty for promoting the use of contingency
plans. Ask me how I know.

Partitioning Benefits

There are three major advantages
of partitioning.

  • Partition
    Pruning – Oracle only accesses a limited set of table partitions if the FROM
    and WHERE clause permit it to.

  • Partition-wise
    Joins – Where two tables that have compatible partitioning schemes are joined ,
    Oracle improves the efficiency of parallel operations by performing the join
    between individual partitions of the tables.

  • Manageability
    – Partitioning allows DDL operations on a large subset of table rows with some
    element of commonality defined through the partitioning type.

Partitioning Types

There are four types of
partitioning available in Oracle 9i. The following descriptions are quoted from
the Data Warehousing Guide.

  • Range
    partitioning maps data to partitions based on ranges of partition key values
    that you establish for each partition. It is the most common type of
    partitioning and is often used with dates. For example, you might want to
    partition sales data into monthly partitions

  • Hash
    partitioning maps data to partitions based on a hashing algorithm that Oracle
    applies to a partitioning key that you identify. The hashing algorithm evenly
    distributes rows among partitions, giving partitions approximately the same

  • List
    partitioning enables you to control explicitly how rows map to partitions. You
    do this by specifying a list of discrete values for the partitioning column in
    the description for each partition.

  • Composite
    partitioning combines range and hash or list partitioning. Oracle first
    distributes data into partitions according to boundaries established by the
    partition ranges. Then, for range-hash partitioning, Oracle uses a hashing
    algorithm to further divide the data into subpartitions within each range
    partition. For range-list partitioning, Oracle divides the data into subpartitions
    within each range partition based on the explicit list you chose.

The documentation also gives many
excellent examples of these methodologies.

Using Partitioning

One of the most common ways of
leveraging range partitioning techniques is to partition fact tables by date
range, where each date range encompasses one load cycle for the table. The data
is loaded into a separate table having exactly the same column definitions, constraints
and indexes as the fact table. The constraints are enabled (sometimes), the
indexes rebuilt (they will have been unusable for the load process to avoid
overhead in maintaining and logging the indexes), and a "partition
exchange" operation is executed to swap the data and index segments of the
table with the data and index segments of a fact table partition. This DDL
operation is extremely fast, and just as importantly, is as fast to reverse.

Hence, it seems that any data
warehouse holding historical data, which is to say virtually all of them, is
incomplete without range partitioning.

Hash partitioning allows even
distribution of data volume between multiple partitions. As each partition can
be assigned its own tablespace, this can be translated into an even
distribution of data rows over devices. A good choice of a hash partitioning
key is a column that has a great many unique values and is commonly used as a
predicate in a user query. Aim to use a number of partitions with an integer
power of two – ie. 2 partitions, or 4, or 8, or 16, or 32 etc., or you run the
risk of unbalancing the number of rows per partition. It is not the end of the
world if you use 30 partitions though, just watch for the imbalance effect.

If you feel an urge to hash a partition
by a column with very few unique values, you are probably in need of list
partitioning. Make sure that you know and plan for all the potential values of
the partitioning key though – you can define a partition to hold unlisted
values, but again you may face imbalance in the number of rows per partition.

By combining range and hash
partitioning into one type of composite partitioning you can get the advantages
of both – compatibility with the efficient loading and deleting of time-based
data through the range partitioning, and load balancing through hash
partitioning within each range partition.

One potential disadvantage of
this composite methodology is that it can easily lead to management of a very
large number of partitions. This can be problematic for queries that do not
have predicates that allow partition pruning, as they can require that every
partition of multiple indexes be touched by the query. One resolution for this
is to merge successive range partitions together – day-level partitions can be
merged in a series of DDL statements to form a single month-level partition.

Index Partitioning

On a partitioned data warehouse
fact table, I can think of no good reason to have unpartitioned indexes or to
have indexes partitioned in any way other than exactly the same as the table, so
that’s all we really need to know about them.


See The Oracle Data
Warehousing Guide
for more detailed information.

See The SQL Reference for
a list of partition-based operations.


See All Articles by Columnist
Dave Aldridge

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles