Question: I have an some sales data that I
would like to range partition, to divide the information by month or quarter
in order to be able to do some reporting. I know that
range partitioning is an option for this. What is the best type of
partitioning for this kind of data?
Answer: Partitioning is definitely the way
to go. Partitions are smaller divisions of tables, indexes or
index-organized tables which can be managed and accessed
independently. Defining an partitioned table involves one or
more columns being used as keys to match the row into the
appropriate partition.
For your purposes, range partitioning makes the most obvious
sense. Range partitioning has been around in Oracle for a long
time. Data with logical ranges of information lend themselves
naturally to range partitions. Common uses of range partitions
include dates, item numbers or serial numbers.
Range partitioning allows an object to be partitioned by a specified
range on the partitioning key. In your case, you can easily create
range partitions by month, quarter or year using a column with a
defined DATE field. With the range partion, each month,
quarter or year would be stored in the appropriately defined
partition.
It is important to note that the range partition for data in the
key column must be defined before rows can be matched to that
partition. For example, if you are storing your data in
monthly range partitions, you will need to define a new range
partition each month before data from that month can be matched and
stored in that partition.
An attempt to match new data for which there is no matching range
partition would result in the following error:
ORA-14400: inserted partition key does not map to
any partition
Until a new range partition for the key column has been created
in the system, the data loading will fail. If you have a large
data warehouse system, this could be a serious problem. The
impacts on reporting, in particular, could be considerable. To
avoid this, most users will create a MAXVALUE partition to catch
everything that does not fall into previously existing range
partitions.
A range partition using MAXVALUE for data on book sales might
look like this:
CREATE TABLE book1 (id NUMBER, c1 DATE)
PARTITION BY RANGE (c1)
(PARTITION book1p1 VALUES LESS THAN
(TO_DATE('2011-01-01', 'YYYY-MM-DD')),
PARTITION book1p2
VALUES LESS THAN (TO_DATE('2011-02-01', 'YYYY-MM-DD')),
PARTITION book1p3 VALUES LESS THAN (MAXVALUE) );
If MAXVALUE is defined as a partition bound for the nth
element of the partition list, then any other higher range is
irrelevant (and illegal) for values defined in the n+ partition
element.
For example, if a partition is defined as:
partition by range (flda, fldb, fldc, fldd)
partition pa values less than (v11, v21, v31, v41)
partition pb values less than (v12, v22, v32, v42)
partition pc values less than (v13, MAXVALUE, v33, v43)
partition pd values less than (v14, MAXVALUE, v34, v44)
then, having defined partition pc, partition pd is irrelevant and
illegal. Partition pc might as well be:
partition pc value less than (v13,
MAXVALUE, MAXVALUE, MAXVALUE);
Generally, adding range partitions is a manual process.
Arup Nanda has
this article on a tool for automatically adding range
partitions.
Interval
partitioning is an new feature with Oracle 11g and offers an
enhancement to range partitioning.
Also see my notes on hash
partitioning and list
partition tips.
 |
|
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.
|
