创建需要redefinition的表,结构同源表


— Create table
create table yhd_so_temp
(id NUMBER(18) not null,
end_user_id NUMBER(18),
order_amount NUMBER(11,2),
order_code CHAR(12),
order_status NUMBER(4),
order_type NUMBER(4),
order_need_cs NUMBER(1),
order_delivery_fee NUMBER(11,2),
order_source NUMBER(4),
back_operator_id NUMBER(18),
order_paid_by_rebate NUMBER(11,2),
order_pay_cfm_bk_op_id NUMBER(18),
order_payment_confirm_date DATE,
order_payment_signal NUMBER(4),
order_payment_method_id NUMBER(18),
order_payment_code VARCHAR2(500),
order_create_time DATE,
order_to_logistics_time DATE,
order_out_of_inventory_status NUMBER(4),
good_receiver_id NUMBER(18),
order_need_invoice NUMBER(1),
parent_so_id NUMBER(18),
delivery_date DATE,
expect_receive_date DATE,
expect_receive_time NUMBER(4),
receive_date DATE,
order_delivery_method_id NUMBER(18),
order_paid_by_account NUMBER(11,2),
order_paid_by_others NUMBER(11,2),
tracker_session_id VARCHAR2(400),
account_payable NUMBER(11,2),
product_amount NUMBER(11,2),
session_id VARCHAR2(400),
data_exchange_flag NUMBER(1),
good_receiver_name VARCHAR2(500),
good_receiver_address VARCHAR2(400),
good_receiver_province VARCHAR2(400),
good_receiver_city VARCHAR2(400),
good_receiver_county VARCHAR2(400),
good_receiver_post_code VARCHAR2(400),
good_receiver_phone VARCHAR2(400),
supplier_process_status NUMBER(4),
po_id NUMBER(18),
order_paid_by_coupon NUMBER(11,2),
cancel_date DATE,
is_leaf NUMBER(1),
order_cs_remark VARCHAR2(2000),
has_imported NUMBER(1),
good_receiver_mobile VARCHAR2(400),
do_cancel_flag NUMBER(10) not null,
so_item_count_change_flag NUMBER(1) not null,
order_paid_by_card NUMBER(11,2) not null,
reference_point NUMBER,
real_point NUMBER,
recompense_points NUMBER not null,
membership_base_point NUMBER not null,
group_ratio NUMBER,
wlt_point NUMBER not null,
track_info VARCHAR2(2000),
is_fragile NUMBER(1),
is_liquid NUMBER(1),
bought_times NUMBER(18),
spec_proc_flag NUMBER(4),
id_card VARCHAR2(400),
order_import_source NUMBER(18),
is_new_good_receiver_hpone NUMBER(1),
partner_amount NUMBER(11,2),
partner_amount_type NUMBER(1),
is_vip NUMBER(1),
good_receiver_city_id NUMBER(18),
good_receiver_country_id NUMBER(18),
good_receiver_county_id NUMBER(18),
good_receiver_province_id NUMBER(18),
allyes_uid VARCHAR2(300),
payment_gateway_id NUMBER(18),
need_allocation NUMBER(1) not null,
warehouse_id NUMBER(18),
delivery_supplierid NUMBER(18),
order_import_do_flag NUMBER(1),
cancel_operator_id NUMBER(18),
mc_site_id NUMBER(8),
order_need_integral NUMBER(9),
virtual_stock_status NUMBER(2),
business_type NUMBER(2),
flag NUMBER(1),
order_finish_time DATE,
update_time DATE,
good_remark VARCHAR2(300),
order_cs_reason VARCHAR2(400),
treatment_time DATE,
treatment_user_id VARCHAR2(400),
is_half_day_delivery NUMBER(1) default 0,
central_version NUMBER(5,2),
order_promotion_discount NUMBER(11,2),
is_need_double_form NUMBER(1) default 0,
creditcard_owner_name VARCHAR2(120),
is_moved_by_hand NUMBER(1) default 0 not null,
order_delivery_person_mobile VARCHAR2(400),
estimate_receive_date DATE,
delivery_remark VARCHAR2(500),
receive_remark VARCHAR2(500),
supplier_delivery_time VARCHAR2(400),
order_delivery_add_fee NUMBER(11,2),
user_ip VARCHAR2(500),
user_guid VARCHAR2(400),
print_num NUMBER(2),
order_create_pay_time DATE,
delivery_method_type NUMBER(8),
delivery_service_type NUMBER(8),
pay_service_type NUMBER(8),
good_receiver_area VARCHAR2(500),
good_receiver_area_id NUMBER(18),
is_new_deliveryorder NUMBER(3),
delivery_level NUMBER(1),
order_weight NUMBER(11,2)
)
partition by range (ORDER_CREATE_TIME)
(
partition P0811 values less than (TO_DATE(‘ 2008-11-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
tablespace data01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition P0812

……….


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session force parallel dml parallel 4;

Session altered.

SQL> alter session force parallel query parallel 4;

Session altered.

SQL> set timing on;

SQL>
SQL>
SQL> begin
2 DBMS_REDEFINITION.START_REDEF_TABLE(uname => ‘EDW1_USER’, orig_table => ‘YHD_SO’,int_table=> ‘YHD_SO_TEMP’,options_flag=>dbms_redefinition.cons_use_rowid);
3 end;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:06:00.41

花了6分钟 速度很不错

下面copy index 约束等，对于分区表建议不要copy index,约束,手工建立local index

SQL> select count (*) from yhd_so_temp

COUNT (*)
————
13474251

SQL> declare
2 num_errors PLS_INTEGER;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname =>’EDW1_USER’,
orig_table =>’YHD_SO’,
int_table =>’YHD_SO_TEMP’,
copy_indexes => 0,
copy_triggers=>true,
copy_constraints=>false,
copy_privileges=>true,
ignore_errors=>false,
num_errors=>num_errors,
copy_statistics=>true);
end;
/ 3 4 5 6 7 8 9 10 11 12 13 14 15 16

PL/SQL procedure successfully completed.

Elapsed: 00:09:52.79

花费了9分钟

创建local index

alter session set workarea_size_policy=manual;

alter session set sort_area_size=1057600;

alter session set db_file_multiblock_read_count=128;

alter table YHD_SO_TEMP
add constraint YHD_PK_SO_N_1 primary key (ID,order_create_time) using index tablespace data01 local;

create index IDX_YHD_SO_PARENT_SO_ID_N_1 on YHD_SO_TEMP (PARENT_SO_ID)
tablespace data01 local parallel 4

create index YHD_IDX_CREATE_TIME_N_1 on YHD_SO_TEMP (TRUNC(ORDER_CREATE_TIME))
tablespace data01 local parallel 4

create index YHD_IDX_ORDER_STATUS_N_1 on YHD_SO_TEMP (ORDER_STATUS)
tablespace data01 local parallel 4

create index YHD_IDX_SO_CREATE_DATE_N_1 on YHD_SO_TEMP (ORDER_CREATE_TIME)
tablespace data01 local parallel 4

create index YHD_IDX_SO_END_USER_ID_N_1 on YHD_SO_TEMP (END_USER_ID)
tablespace data01 local parallel 4

create index YHD_IDX_SO_ORDER_TYPE_N_1 on YHD_SO_TEMP (ORDER_TYPE)
tablespace data01 local parallel 4

create index YHD_IDX_SO_PAYMENT_ID_N_1 on YHD_SO_TEMP (ORDER_PAYMENT_METHOD_ID)
tablespace data01 local parallel 4

create index YHD_UQ_IDX_SO_CODE_N_1 on YHD_SO_TEMP (ORDER_CODE)
tablespace data01 local parallel 4

SYNC同步表，这一步主要是为了减少finish lock table的时间 ，收集新表的statistics,最后不要忘了alter index noparallel

SQL> begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname =>’EDW1_USER’,
orig_table =>’YHD_SO’,
int_table =>’YHD_SO_TEMP’);
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE (
uname =>’EDW1_USER’,
orig_table =>’YHD_SO’,
int_table =>’YHD_SO_TEMP’);
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname =>’edw1_user’,tabname =>’yhd_so’,estimate_percent=> 10,degree =>4, cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter index IDX_YHD_SO_PARENT_SO_ID_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_CREATE_TIME_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_ORDER_STATUS_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_CREATE_DATE_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_END_USER_ID_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_ORDER_TYPE_N_1 noparallel;

Index altered.

SQL> alter index YHD_IDX_SO_PAYMENT_ID_N_1 noparallel;

Index altered.

SQL> alter index YHD_UQ_IDX_SO_CODE_N_1 noparallel;

Index altered.

SQL>

整个过程没有超过1个小时,1300万的数据也不算小 online redefinition 是heap转分区表的一个不错的方法。