- 分区类型:RANGE、LIST、HASH
-
RANGE:范围分区PARTITION BY LIST (createdate)
(
PARTITION P01 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD')), // 存储createdate<’2018-01-01’的数据
PARTITION P02 VALUES LESS THAN (TO_DATE('2018-04-01', 'YYYY-MM-DD')), // 存储createdate<’ 2018-04-01‘的数据
PARTITION P99 VALUES LESS THAN (MAXVALUE) // 存储createdate>=’ 2018-04-01‘的数据
);
-
LIST:列表分区PARTITION BY LIST (supid)
(
PARTITION P01 VALUES ('112'), // 存储supid=’112’的数据
PARTITION P02 VALUES ('8518') // 存储supid=’8518'的数据
PARTITION P99 VALUES (DEFAULT) // 存储其他supid的数据
);
-
-
-
HASH:哈希分区(无法控制数据处于哪个分区,相当于hashmap,不知道哪个可以在哪个下标)PARTITION BY HASH (supid)
(
PARTITION P01,
PARTITION P02,
PARTITION P03
);
-
-
方式一:创建表时分区create table INVENTORY (
shopid VARCHAR2(6) not null,
placeid VARCHAR2(20) not null,
goodsid INTEGER not null
)
PARTITION BY LIST (supid)
(
PARTITION P01 VALUES ('112'),
PARTITION P02 VALUES ('8518'),
PARTITION P03 VALUES ('5876'),
PARTITION P04 VALUES ('4524'),
PARTITION P05 VALUES ('3534'),
PARTITION P06 VALUES ('3565'),
PARTITION P07 VALUES ('1148'),
PARTITION P08 VALUES ('24184'),
PARTITION P09 VALUES ('3854'),
PARTITION P10 VALUES ('3852'),
PARTITION P99 VALUES (DEFAULT)
);
- 方式二:创建表后分区(即普通表转分区表,可能已有数据)
-
创建表create table INVENTORY (
shopid VARCHAR2(6) not null,
placeid VARCHAR2(20) not null,
goodsid INTEGER not null
);
-
创建临时表create table INVENTORY_INTERIM (
shopid VARCHAR2(6) not null,
placeid VARCHAR2(20) not null,
goodsid INTEGER not null
)
PARTITION BY LIST (supid)
(
PARTITION P01 VALUES ('112'),
PARTITION P02 VALUES ('8518'),
PARTITION P03 VALUES ('5876'),
PARTITION P04 VALUES ('4524'),
PARTITION P05 VALUES ('3534'),
PARTITION P06 VALUES ('3565'),
PARTITION P07 VALUES ('1148'),
PARTITION P08 VALUES ('24184'),
PARTITION P09 VALUES ('3854'),
PARTITION P10 VALUES ('3852'),
PARTITION P99 VALUES (DEFAULT)
)
-
检查是否能重定义begin
dbms_redefinition.can_redef_table('nhd',’INVENTORY’);
end;
-
开始重定义begin
dbms_redefinition.start_redef_table('nhd',’INVENTORY,'INVENTORY _INTERIM');
end;
-
同步中间表begin
dbms_redefinition.sync_interim_table('nhd',‘INVENTORY,'INVENTORY _INTERIM');
end
-
拷贝依赖对象declare
error_count number := 0;
begin
dbms_redefinition.copy_table_dependents('nhd',’INVENTORY’,'INVENTORY_INTERIM',dbms_redefinition.cons_orig_params,true,true,true,true,error_count);
end;
-
完成重定义begin
dbms_redefinition.finish_redef_table('nhd',’INVENTORY’,'INVENTORY _INTERIM');
end;
-
取消重定义(发生错误或中断时使用)begin
dbms_redefinition.abort_redef_table('nhd','INVENTORY','INVENTORY_INTERIM');
end;
-