我们将把所有常规表转换为分区表, 这些常规表可能有上百万行记录。 下面给出了现有表的信息: 表名 结构--------- ---------------- TABLE_1 TABLE_2 Same as TABLE_1 TABLE_3 Same as TABLE_1 TABLE_4TABLE_5 Same as TABLE_4 TABLE_6 Same as TABLE_4 TABLE_7 Same as TABLE_4 TABLE_8 Same as TABLE_4 TABLE_9 TABLE_10 Same as TABLE_1TABLE_11 Same as TABLE_1 TABLE_12 ... ... 可以看到,有几个表的结构与其他表的结构相同。现在,希望将这些表进行分区如下: 表名 结构 --------- ------------------------TABLE_1 will also have TABLE_2, TABLE_3, TABLE_10 & TABLE_11 data TABLE_4 will also have TABLE_5, TABLE_6, TABLE_7 & TABLE_8 data TABLE_12 only its data 如何将所有的常规表转换为分区表呢?注意,我们使用日期列中的一个作为分区关键字。最快和最安全的移植方式是什么呢? 实现这种转换非常容易。下面假定t1、t2和t3是你现有的表。表t是希望得到的表(如果需要,以后可以删除t1,将t重命名为t1)。我将采用如下方法:SQL> create table t1 as select sysdate dt, all_objects.* from all_objects; Table created.SQL> create table t2 as select add_months(sysdate,-12) dt, all_objects.* from all_objects; Table created. SQL> create table t3 as select add_months(sysdate,-24) dt, all_objects.* from all_objects; Table created.SQL> create table t (dt, OWNER, OBJECT_NAME, SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY)partition by range(dt) ( partition part2000 values less than (to_date('01-jan-2001','dd-mon-yyyy')), partition part2001 values less than (to_date('01-jan-2002','dd-mon-yyyy')), partition part2002 values less than (to_date('01-jan-2003','dd-mon-yyyy')) ) as select sysdate dt, all_objects.* from all_objects where 1=0; Table created.我只是创建了一个空的分区表,它是按照日期进行分区的,其结构与你现有的表完全相同。其范围为:t1适合一个分区,t2适合另一个,以此类推。你现在要做的事情就是将空的分区part 2000与全表t1相交换,如下: SQL> alter table t 2 exchange partition part2000 3 with table t3 4 without validation 5 / Table altered. 对其他表/分区进行同样操作;分别将t2和part 2001、t3与part 2002交换。现在,t是一个具有三个分区的表,这三个分区分别是前面的表t1、t2和t3。
这些常规表可能有上百万行记录。
下面给出了现有表的信息:
表名 结构--------- ----------------
TABLE_1
TABLE_2 Same as TABLE_1
TABLE_3 Same as TABLE_1
TABLE_4TABLE_5 Same as TABLE_4
TABLE_6 Same as TABLE_4
TABLE_7 Same as TABLE_4
TABLE_8 Same as TABLE_4
TABLE_9
TABLE_10 Same as TABLE_1TABLE_11 Same as TABLE_1
TABLE_12
... ...
可以看到,有几个表的结构与其他表的结构相同。现在,希望将这些表进行分区如下:
表名 结构
--------- ------------------------TABLE_1 will also have TABLE_2, TABLE_3, TABLE_10 & TABLE_11 data
TABLE_4 will also have TABLE_5, TABLE_6, TABLE_7 & TABLE_8 data
TABLE_12 only its data
如何将所有的常规表转换为分区表呢?注意,我们使用日期列中的一个作为分区关键字。最快和最安全的移植方式是什么呢?
实现这种转换非常容易。下面假定t1、t2和t3是你现有的表。表t是希望得到的表(如果需要,以后可以删除t1,将t重命名为t1)。我将采用如下方法:SQL> create table t1 as select sysdate dt,
all_objects.* from all_objects;
Table created.SQL> create table t2
as select add_months(sysdate,-12) dt,
all_objects.* from all_objects;
Table created.
SQL> create table t3
as select add_months(sysdate,-24) dt,
all_objects.* from all_objects;
Table created.SQL> create table t (dt, OWNER,
OBJECT_NAME, SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY)partition by range(dt) (
partition part2000 values less than
(to_date('01-jan-2001','dd-mon-yyyy')),
partition part2001 values less than
(to_date('01-jan-2002','dd-mon-yyyy')),
partition part2002 values less than
(to_date('01-jan-2003','dd-mon-yyyy'))
) as
select sysdate dt, all_objects.*
from all_objects where 1=0;
Table created.我只是创建了一个空的分区表,它是按照日期进行分区的,其结构与你现有的表完全相同。其范围为:t1适合一个分区,t2适合另一个,以此类推。你现在要做的事情就是将空的分区part 2000与全表t1相交换,如下: SQL> alter table t
2 exchange partition part2000
3 with table t3
4 without validation
5 /
Table altered.
对其他表/分区进行同样操作;分别将t2和part 2001、t3与part 2002交换。现在,t是一个具有三个分区的表,这三个分区分别是前面的表t1、t2和t3。