Database Reference
In-Depth Information
■
You might have a question in your mind, especially if you just finished reading the previous chapter on
datatypes. You can see we are partitioning by a
TIMESTAMP
and we are adding an
INTERVAL
of one month to it. In the
“Datatypes” chapter, we saw how adding an
INTERVAL
of one month to a
TIMESTAMP
that fell on January 31
st
would raise
an error, since there is no February 31
st
. Will the same issue happen with interval partitioning? the answer is yes, if you
attempt to use a date such as '
29-01-1990'
(any day of the month after 28 would suffice), you will receive an error
"
ORA-14767: Cannot specify this interval with existing high bounds"
. the database will not permit you to
use a boundary value that is not safe to add the interval to.
Note
On lines 8 and 9, you see the range partitioning scheme for this table; it starts with a single empty partition that
would contain any data prior to
01-JAN-1900
. Presumably, since the table holds an audit trail, this partition will
remain small and empty forever. It is a mandatory partition and is referred to as the
transitional
partition. All data
that is strictly less than this current high value partition will be range partitioned, using traditional range partitioning.
Only data that is created
above
the transitional partition high value will use interval partitioning. If we query the data
dictionary we can see what has been created so far:
EODA@ORA12CR1> select a.partition_name, a.tablespace_name, a.high_value,
2 decode( a.interval, 'YES', b.interval ) interval
3 from user_tab_partitions a, user_part_tables b
4 where a.table_name = 'AUDIT_TRAIL'
5 and a.table_name = b.table_name
6 order by a.partition_position;
PARTITION_ TABLESPACE HIGH_VALUE INTERVAL
---------- ---------- ------------------------------- ------------------------------
P0 USERS TIMESTAMP' 1900-01-01 00:00:00'
So far, we have just the single partition and it is not an
INTERVAL
partition, as shown by the empty
INTERVAL
column. Rather it is just a regular
RANGE
partition right now; it will hold anything strictly less than
01-JAN-1900
.
Looking at the
CREATE TABLE
statement again, we can see the new interval partitioning specific information on
lines 6 through 7:
6 interval (numtoyminterval(1,'month'))
7 store in (users, example )
On line 6 we have the actual interval specification of
NUMTOYMINTERVAL(1,'MONTH')
. Our goal was to store
monthly partitions—a new partition for each month's worth of data—a very common goal. By using a date that is safe
to add a month to (refer to Chapter 12 for why adding a month to a timestamp can be error prone in some cases)—the
first of the month—we can have the database create monthly partitions on the fly, as data arrives, for us.
On line 7 we have specifics:
store in (users,example)
. This allows us to tell the database where to create these
new partitions—what tablespaces to use. As the database figures out what partitions it wants to create, it uses this list
to decide what tablespace to create each partition in. This allows the DBA to control the maximum desired tablespace
size: they might not want a single 500GB tablespace but they would be comfortable with 10 50GB tablespaces. In that
case, they would set up 10 tablespaces and allow the database to use all 10 to create partitions.
Let's insert a row of data now and see what happens:
EODA@ORA12CR1> insert into audit_trail (ts,data) values
2 ( to_timestamp('27-feb-2014','dd-mon-yyyy'), 'xx' );
1 row created.
