Oracle表分区与索引的创建
-- Create table
create table TT_BAR_RECORD
(
BAR_RECORD_ID NUMBER(20) not null,
OP_CODE VARCHAR2(4),
ZONE_CODE VARCHAR2(30),
WAYBILL_NO VARCHAR2(30),
CONTNR_CODE VARCHAR2(30),
OP_ATTACH_INFO VARCHAR2(100),
STAY_WHY_CODE VARCHAR2(30),
BAR_SCAN_TM DATE not null,
BAR_OPR_CODE VARCHAR2(30),
COURIER_CODE VARCHAR2(30),
PHONE_ZONE VARCHAR2(20),
PHONE VARCHAR2(20),
SUBBILL_PIECE_QTY NUMBER(5),
BAR_UPLOAD_TYPE_CODE NUMBER(4),
WEIGHT_QTY NUMBER(16,2),
OTHER_INFO VARCHAR2(100),
AUTOLOADING VARCHAR2(20),
OBJ_TYPE_CODE NUMBER(4),
CREATE_TM DATE default SYSDATE not null,
ASURA_ROWID VARCHAR2(30),
DEST_ZONE_CODE VARCHAR2(30)
)
partition by range (CREATE_TM)
(
partition TT_BAR_RECORD_P20130818 values less than (TO_DATE(' 2013-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130819 values less than (TO_DATE(' 2013-08-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130820 values less than (TO_DATE(' 2013-08-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130821 values less than (TO_DATE(' 2013-08-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130822 values less than (TO_DATE(' 2013-08-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130823 values less than (TO_DATE(' 2013-08-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130824 values less than (TO_DATE(' 2013-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130825 values less than (TO_DATE(' 2013-08-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130826 values less than (TO_DATE(' 2013-08-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130827 values less than (TO_DATE(' 2013-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130828 values less than (TO_DATE(' 2013-08-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130829 values less than (TO_DATE(' 2013-08-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130830 values less than (TO_DATE(' 2013-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130831 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130901 values less than (TO_DATE(' 2013-09-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130902 values less than (TO_DATE(' 2013-09-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130903 values less than (TO_DATE(' 2013-09-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_LOAD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition TT_BAR_RECORD_P20130930 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace OMP_BASE_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)
;
-- Add comments to the table
comment on table TT_BAR_RECORD
is '运单轨迹表，记录运单中转过程';
-- Add comments to the columns
comment on column TT_BAR_RECORD.BAR_RECORD_ID
is '序列号，标识唯一一条记录，对应XH字段';
comment on column TT_BAR_RECORD.OP_CODE
is '巴枪操作码，表示不同的巴枪操作';
comment on column TT_BAR_RECORD.ZONE_CODE
is '地区代码，可以是总部代码，区部代码或分点部代码';
comment on column TT_BAR_RECORD.WAYBILL_NO
is '运单号，可以是快件运单编号，包号，笼号，袋号或车标号';
comment on column TT_BAR_RECORD.CONTNR_CODE
is '容器编号，即所在的包/袋/笼/车号';
comment on column TT_BAR_RECORD.OP_ATTACH_INFO
is '巴枪操作辅助码，对于不同的操作码它表示不同的含义，比如：装车时表示线路编码；快件为子母件时表示母件号码；卸车时表示操作生成方式；便利店出仓时表示便利店代码等等';
comment on column TT_BAR_RECORD.STAY_WHY_CODE
is '派件状态/问题件代码';
comment on column TT_BAR_RECORD.BAR_SCAN_TM
is '巴枪操作时间';
comment on column TT_BAR_RECORD.BAR_OPR_CODE
is '巴枪操作员工号';
comment on column TT_BAR_RECORD.COURIER_CODE
is '收派员工号';
comment on column TT_BAR_RECORD.PHONE_ZONE
is '电话区号';
comment on column TT_BAR_RECORD.PHONE
is '电话号码';
comment on column TT_BAR_RECORD.SUBBILL_PIECE_QTY
is '件数 （子母件中的子件数量）';
comment on column TT_BAR_RECORD.WEIGHT_QTY
is '计费重量';
comment on column TT_BAR_RECORD.OTHER_INFO
is '其它信息';
comment on column TT_BAR_RECORD.AUTOLOADING
is '巴枪数据来源 —T900 :1; DTX5: 2;, HHT: 3;）';
comment on column TT_BAR_RECORD.OBJ_TYPE_CODE
is '对象类型，即运单号类型，车，包，笼，袋，件的类型编号，运单号码类型，3：母运单，4：子运单，1：包，5：笼、2：车 6：袋';
comment on column TT_BAR_RECORD.ASURA_ROWID
is '阿修罗tt_bar_record表rowid';
comment on column TT_BAR_RECORD.DEST_ZONE_CODE
is '目的地代码';
-- Create/Recreate primary, unique and foreign key constraints
alter table TT_BAR_RECORD
add constraint IPK_TT_BAR_RECORD primary key (BAR_RECORD_ID, CREATE_TM)
using index
tablespace OMP_BASE_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index INK_TT_BAR_RECORD_01 on TT_BAR_RECORD (WAYBILL_NO, OP_CODE)
tablespace OMP_BASE_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INK_TT_BAR_RECORD_02 on TT_BAR_RECORD (CONTNR_CODE, ZONE_CODE)
tablespace OMP_BASE_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);