Databases Reference
In-Depth Information
As long as your application can correctly use a number that represents a valid date, there shouldn't be any issues.
As each new day's data are inserted, a new daily partition is created. For example, suppose these data are inserted:
SQL> insert into f_sales values(100,20130130);
SQL> insert into f_sales values(50,20130131);
Two corresponding partitions are automatically created. This can be verified via this query:
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 the corresponding output:
TABLE_NAME PARTITION PART_POS INTERVAL TABLESPACE_N HIGH_VALUE
---------- --------- -------- ---------- ------------ --------------------
F_SALES P1 1 NO USERS 20130101
F_SALES SYS_P3383 2 YES USERS 20130131
F_SALES SYS_P3384 3 YES USERS 20130132
Be aware that the
HIGH_VALUE
column can contain numbers that map to invalid dates. This is to be expected.
For instance, when creating a partition with a
D_DATE_ID
of 20130131, Oracle will calculate the upper boundary to be
the value 20130132. The high boundary value is defined as less than (but not equal to) any values inserted into the
partition. The only reason I mention this here is because if you attempt to perform date arithmetic on the value in
HIGH_VALUE
, you will need to account for potential numbers that map to invalid dates. In this specific example, you
would have to subtract one from the value in
HIGH_VALUE
to obtain a valid date.
As previously shown in this section, a daily interval partitioning scheme based on a number works fine. However,
such a scheme doesn't work as well if you want to create interval partitions by month or year. This is because there is
no number that consistently represents a month or year. If you need date-based interval functionality, then use a date
and not a number.
Partitioning to Match a Parent Table
If you're using Oracle Database 11g or higher, you can use the
PARTITION BY REFERENCE
clause to specify that a
child table should be partitioned in the same way as its parent. This allows a child table to inherit the partitioning
strategy of its parent table. Any parent table partition maintenance operations are automatically applied to the
child record tables.
■
Before the advent of the partitioning-by-reference feature, you had to physically duplicate and maintain the
parent table column in the child table. Doing so not only requires more disk space, but also is a source of error when
maintaining the partitions.
Note
For example, say you want to create a parent
ORDERS
table and a child
ORDER_ITEMS
table that are related by
primary key and foreign key constraints on the
ORDER_ID
column. The parent
ORDERS
table will be partitioned on the
ORDER_DATE
column. Even though it won't contain the
ORDER_DATE
column, you wonder whether you can partition the
