Partitioning - Expert Oracle Database Architecture - page 589

TO_CHAR(RANGE_KEY_COLUMN,'DD-
-----------------------------
15-dec-2013 00:00:00
31-dec-2013 23:59:59
EODA@ORA12CR1> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')
2 from range_example partition (part_2);
TO_CHAR(RANGE_KEY_COLUMN,'DD-
-----------------------------
01-jan-2014 00:00:00
31-dec-2014 23:59:59
You might be wondering what would happen if you inserted a date that fell outside of the upper bound.
The answer is that Oracle would raise an error:
EODA@ORA12CR1> insert into range_example
2 ( range_key_column, data )
3 values
4 ( to_date( '01-jan-2015 00:00:00',
5 'dd-mon-yyyy hh24:mi:ss' ),
6 'application data...' );
insert into range_example
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
There are two approaches to the preceding situation—one would be to use Interval partitioning described later
or to use a catch-all partition, which we'll demonstrate now. Suppose you want to segregate 2013 and 2014 dates into
their separate partitions as we have, but you want all other dates to go into a third partition. With range partitioning,
you can do this using the MAXVALUE clause, which looks like this:
EODA@ORA12CR1> CREATE TABLE range_example
2 ( range_key_column date,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2014','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2015','dd/mm/yyyy')),
10 PARTITION part_3 VALUES LESS THAN
11 (MAXVALUE)
12 )
13 /
Table created.
Now when you insert a row into that table, it will go into one of the three partitions—no row will be rejected, since
partition PART_3 can take any value of RANGE_KEY_COLUMN that doesn't go into PART_1 or PART_2 (even null values of
the RANGE_KEY_COLUMN will be inserted into this new partition).