Database Reference
In-Depth Information
EODA@ORA12CR1> insert into partitioned partition(fy_2014)
2 select to_date('31-dec-2014','dd-mon-yyyy')-mod(rownum,360), rownum
3 from dual connect by level <= 70000
4 /
70000 rows created.
EODA@ORA12CR1> insert into partitioned partition(fy_2015)
2 select to_date('31-dec-2015','dd-mon-yyyy')-mod(rownum,360), rownum
3 from dual connect by level <= 70000
4 /
70000 rows created.
EODA@ORA12CR1> create index partitioned_idx_local
2 on partitioned(id)
3 LOCAL
4 /
Index created.
EODA@ORA12CR1> create index partitioned_idx_global
2 on partitioned(timestamp)
3 GLOBAL
4 /
Index created.
This sets up our warehouse table. The data is partitioned by fiscal year and we have the last two years' worth
of data online. This table has two indexes: one is
LOCAL
and the other is
GLOBAL
. Now it's the end of the year and we
would like to do the following:
1.
Remove the oldest fiscal year data. We do not want to lose this data forever; we just want to
age it out and archive it.
2.
Add the newest fiscal year data. It will take a while to load it, transform it, index it, and so
on. We would like to do this work without impacting the availability of the current data, if
at all possible.
The first step is to set up an empty table for fiscal year 2014 that looks just like the partitioned table. We'll use this
table to exchange with the
FY_2014
partition in the partitioned table, turning that partition into a table and in turn
emptying out the partition in the partitioned table. The net effect is that the oldest data in the partitioned table will
have been in effect removed after the exchange:
EODA@ORA12CR1> create table fy_2014 ( timestamp date, id int );
Table created.
EODA@ORA12CR1> create index fy_2014_idx on fy_2014(id);
Index created.
