Database Reference
In-Depth Information
TABLE_NAME PARTITION_ HIGH_VALUE INTERVAL
---------------- ---------- ------------------------------- -------------------------
ORDERS PART_2014 TIMESTAMP' 2015-01-01 00:00:00'
ORDERS PART_2015 TIMESTAMP' 2016-01-01 00:00:00'
ORDERS SYS_P1626 TIMESTAMP' 2017-01-01 00:00:00' NUMTOYMINTERVAL(1,'YEAR')
ORDER_LINE_ITEMS PART_2014
ORDER_LINE_ITEMS PART_2015
ORDER_LINE_ITEMS SYS_P1626 YES
Two partitions named
SYS_P1626
were created, with the parent table partition having a high value of 2017-01-01.
If desired, you can rename the partitions via the
ALTER TABLE
command:
EODA@ORA12CR1> alter table orders rename partition sys_p1626 to part_2016;
Table altered.
EODA@ORA12CR1> alter table order_line_items rename partition sys_p1626 to part_2016;
Table altered.
■
See the “Interval Partitioning” section of this chapter for an example of automating the renaming of partitions
via PL/SQL.
Tip
Virtual Column Partitioning
Virtual column partitioning allows you to partition based on a SQL expression. This type of partitioning is useful when
a table column is overloaded with multiple business values and you want to partition on a portion of that column.
For example, let's say you have a
RESERVATION_CODE
column in a table:
EODA@ORA12CR1> create table res(reservation_code varchar2(30));
Table created.
And the first character in the
RESERVATION_CODE
column defines a region from which the reservation originated.
For the purposes of this example, let's say a first character of an
A
or
C
map to the
NE
region, and values of
B
map to the
SW
region, and values of
D
map to the
NW
region.
Next some test data is inserted into the table:
EODA@ORA12CR1> insert into res (reservation_code)
2 select chr(64+(round(dbms_random.value(1,4)))) || level
3 from dual connect by level < 100000;
EODA@ORA12CR1> select * from res;