Database Reference
In-Depth Information
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'
SYS_P1623 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
If you recall from the range partition section, you would expect that
INSERT
to fail. However, since we are using
interval partitioning, it succeeds and, in fact, creates a new partition
SYS_P1623
. The
HIGH_VALUE
for this partition
is
01-MAR-2014
which, if we were using range partitioning, would imply anything strictly less than
01-MAR-2014
and
greater than or equal to
01-JAN-1900
would go into this partition, but since we have an interval the rules are different.
When the interval is set, the range for this partition is anything greater than or equal to the
HIGH_VALUE-INTERVAL
and
strictly less than the
HIGH_VALUE
. So, this partition would have the range of:
EODA@ORA12CR1> select TIMESTAMP' 2014-03-01 00:00:00'-NUMTOYMINTERVAL(1,'MONTH')
↲
greater_than_eq_to,
2 TIMESTAMP' 2014-03-01 00:00:00' strictly_less_than
3 from dual
4 /
GREATER_THAN_EQ_TO
---------------------------------------------------------------------------
STRICTLY_LESS_THAN
---------------------------------------------------------------------------
01-FEB-14 12.00.00.000000000 AM
01-MAR-14 12.00.00.000000000 AM
That is—all of the data for the month of February, 2014. If we insert another row in some other month, as follows,
we can see that another partition,
SYS_P1624
, is added that contains all of the data for the month of June, 2014:
EODA@ORA12CR1> insert into audit_trail (ts,data) values
2 ( to_date('25-jun-2014','dd-mon-yyyy'), 'xx' );
1 row created.
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'
SYS_P1623 USERS TIMESTAMP' 2014-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
SYS_P1624 USERS TIMESTAMP' 2014-07-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')
