Partition Bounds for Range Partitioning

In a range-partitioned table or index, the partitioning key of each row is compared
with a set of upper and lower bounds to determine which partition the row belongs in:
■ Every partition of a range-partitioned table or index has a noninclusive upper
bound, which is specified by the VALUES LESS THAN clause.
■ Every partition except the first partition also has an inclusive lower bound, which
is specified by the VALUES LESS THAN on the next-lower partition.
The partition bounds collectively define an ordering of the partitions in a table or
index. The first partition is the partition with the lowest VALUES LESS THAN clause,
and the last or highest partition is the partition with the highest VALUES LESS THAN
clause

Comparing Partitioning Keys with Partition Bounds

If you attempt to insert a row into a table and the row's partitioning key is greater than or equal to the partition bound for the highest partition in the table, the insert will fail.
When comparing character values in partitioning keys and partition bounds,
characters are compared according to their binary values. However, if a character
consists of more than one byte, Oracle compares the binary value of each byte, not of
the character. The comparison also uses the comparison rules associated with the
column data type. For example, blank-padded comparison is done for the ANSI CHAR
data type. The NLS parameters, specifically the initialization parameters NLS_SORT
and NLS_LANGUAGE and the environment variable NLS_LANG, have no effect on the
comparison.
The binary value of character data varies depending on which character set is being
used (for example, ASCII or EBCDIC). For example, ASCII defines the characters A
through Z as less than the characters a through z, whereas EBCDIC defines A through
Z as being greater than a through z. Thus, partitions designed for one sequence will
not work with the other sequence. You must repartition the table after importing from
a table using a different character set.

MAXVALUE
You can specify the keyword MAXVALUE for any value in the partition bound value_
list. This keyword represents a virtual infinite value that sorts higher than any other value for the data type, including the NULL value.
For example, you might partition the OFFICE table on STATE (a CHAR(10) column)
into three partitions with the following partition bounds:
■ VALUES LESS THAN ('I'): States whose names start with A through H
■ VALUES LESS THAN ('S'): States whose names start with I through R
■ VALUES LESS THAN (MAXVALUE): States whose names start with S through Z,
plus special codes for non-U.S. regions

Nulls
NULL cannot be specified as a value in a partition bound value_list. An empty
string also cannot be specified as a value in a partition bound value_list, because it is treated as NULL within the database server.
For the purpose of assigning rows to partitions, Oracle Database sorts nulls greater
than all other values except MAXVALUE. Nulls sort less than MAXVALUE.

This means that if a table is partitioned on a nullable column, and the column is to
contain nulls, then the highest partition should have a partition bound of MAXVALUE
for that column. Otherwise the rows that contain nulls will map above the highest
partition in the table and the insert will fail.

DATE Datatypes
If the partition key includes a column that has the DATE datatype and the NLS date
format does not specify the century with the year, you must specify partition bounds
using the TO_DATE function with a 4-character format mask for the year. Otherwise,
you will not be able to create the table or index. For example, with the sales_range
table using a DATE column:
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
COMPRESS
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));
When you query or modify data, it is recommended that you use the TO_DATE
function in the WHERE clause so that the value of the date information can be
determined at compile time. However, the optimizer can prune partitions using a
selection criterion on partitioning columns of type DATE when you use another format,
as in the following examples:
SELECT * FROM sales_range
WHERE sales_date BETWEEN TO_DATE('01-JUL-00', 'DD-MON-YY')
AND TO_DATE('01-OCT-00', 'DD-MON-YY');
SELECT * FROM sales_range
WHERE sales_date BETWEEN '01-JUL-2000' AND '01-OCT-2000';
In this case, the date value will be complete only at runtime. Therefore you will not be able to see which partitions Oracle is accessing as is usually shown on the
partition_start and partition_stop columns of the EXPLAIN PLAN statement
output on the SQL statement. Instead, you will see the keyword KEY for both columns.


Multicolumn Partitioning Keys
When a table or index is partitioned by range on multiple columns, each partition
bound and partitioning key is a list (or vector) of values. The partition bounds and
keys are ordered according to ANSI SQL2 vector comparison rules. This is also the
way Oracle orders multicolumn index keys.
To compare a partitioning key with a partition bound, you compare the values of their
corresponding columns until you find an unequal pair and then that pair determines
which vector is greater. The values of any remaining columns have no effect on the
comparison.

Implicit Constraints Imposed by Partition Bounds
If you specify a partition bound other than MAXVALUE for the highest partition in a
table, this imposes an implicit CHECK constraint on the table. This constraint is not
recorded in the data dictionary, but the partition bound itself is recorded.

Posted in: Partitioning