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;
 
 
Search WWH ::




Custom Search