Databases Reference
In-Depth Information
Adding Weekly Partitions, Based on Date
You can also have Oracle add partitions by other increments of time, such as a week; for example,
create table f_sales(
sales_amt number
,d_date_dtt date)
partition by range (d_date_dtt)
interval(numtodsinterval(7,'day'))
store in (p1_tbsp, p2_tbsp, p3_tbsp)
(partition p1 values less than (to_date('01-01-2013', 'dd-mm-yyyy'))
tablespace p1_tbsp);
As data are inserted into future weeks, new weekly partitions will be created automatically; for example,
SQL> insert into f_sales values(100, sysdate+7);
SQL> insert into f_sales values(200, sysdate+14);
Running this query verifies that partitions have automatically been added:
select table_name, partition_name, partition_position part_pos
,interval, tablespace_name, high_value
from user_tab_partitions
where table_name = 'F_SALES'
order by table_name, partition_position;
Here is some sample output:
TABLE_NAME PARTITION PART_POS INTERVAL TABLESPACE_N HIGH_VALUE
---------- --------- -------- ---------- ------------ ------------------------------
F_SALES P1 1 NO P1_TBSP TO_DATE(' 2013-01-01 00:00:00'
F_SALES SYS_P3725 2 YES P3_TBSP TO_DATE(' 2013-01-15 00:00:00'
F_SALES SYS_P3726 3 YES P1_TBSP TO_DATE(' 2013-01-22 00:00:00'
In this way, Oracle automatically manages the addition of weekly partitions to the table.
Adding Daily Partitions, Based on Number
Recall from the section “Partitioning by Range,” earlier in this chapter, how a number field (
D_DATE_ID
) was used as
a range-based partition key. Suppose you want to create daily interval partitions in a table with such a partitioning
strategy automatically. In this situation, you need to specify an
INTERVAL
of one. Here is an example:
create table f_sales(
sales_amt number
,d_date_id number)
partition by range (d_date_id)
interval(1)
(partition p1 values less than (20120101));
