脚本：建立分区表实现数据分区存储
2017-12-06 13:51:49 访问(2604) 赞(0) 踩(0)
create table UTB_LBS_LOC_HIS_SUPERMAP
(
id VARCHAR2(50) not null,
lbsvehitem_id NUMBER not null,
lbsmapairport_id NUMBER not null,
lbsdeviceitem_id NUMBER not null,
loc_time DATE not null,
loc_month INTEGER not null,
getloc_time DATE not null,
savedb_time DATE default sysdate not null,
longitude NUMBER(18,9) not null,
latitude NUMBER(18,9) not null,
originallongitude NUMBER(18,9) not null,
originallatitude NUMBER(18,9) not null,
speed NUMBER(18,2) not null,
altitude NUMBER(18,2) not null,
direction INTEGER not null,
locationtext VARCHAR2(255) not null,
lbshruser_id NUMBER default 0 not null,
emlocstatusv INTEGER not null,
empositioningmodev INTEGER not null,
gnsssatellitenumber INTEGER not null,
gprssignalintensity INTEGER not null,
isoverspeedalert CHAR(1) not null,
isoutofboundsalert CHAR(1) not null,
isfatiguedrivingalert CHAR(1) not null,
isaccoff CHAR(1) not null,
emgnssfaultv INTEGER not null,
emdevicepowerstatusv INTEGER not null,
emadditionequipmentfaultv INTEGER not null
)
partition by range(loc_month)
(
partition part_01 values less than(2) tablespace TBS_LBS_SINOPORT,
partition part_02 values less than(3) tablespace TBS_LBS_SINOPORT,
partition part_03 values less than(4) tablespace TBS_LBS_SINOPORT,
partition part_04 values less than(5) tablespace TBS_LBS_SINOPORT,
partition part_05 values less than(6) tablespace TBS_LBS_SINOPORT,
partition part_06 values less than(7) tablespace TBS_LBS_SINOPORT,
partition part_07 values less than(8) tablespace TBS_LBS_SINOPORT,
partition part_08 values less than(9) tablespace TBS_LBS_SINOPORT,
partition part_09 values less than(10) tablespace TBS_LBS_SINOPORT,
partition part_10 values less than(11) tablespace TBS_LBS_SINOPORT,
partition part_11 values less than(12) tablespace TBS_LBS_SINOPORT,
partition part_12 values less than(maxvalue) tablespace TBS_LBS_SINOPORT
)
-- Add comments to the table
comment on table UTB_LBS_LOC_HIS_SUPERMAP
is '历史定位数据 超图平台 #ID# #UTB_LBS_LOC_HIS_SUPERMAP#';
-- Add comments to the columns
comment on column UTB_LBS_LOC_HIS_SUPERMAP.id
is '历史定位数据 超图平台 #ID#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.lbsvehitem_id
is '车辆 #VehItem_Id# #LbsVehItem_Id#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.lbsmapairport_id
is '所属机场 考虑车辆换部门的情况，保留该车辆当时的归属 海口/三亚/太原 #MapAirport_Id# #LbsMapAirport_Id#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.lbsdeviceitem_id
is '设备 设备和定位时间组合唯一 #DeviceItem_Id# #LbsDeviceItem_Id#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.loc_time
is '定位时间 #Loc_Time#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.loc_month
is '定位月份 #Loc_Month#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.getloc_time
is '获得定位数据时间 数据接收程序获得定位数据时间 #GetLoc_Time#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.savedb_time
is '写入数据库时间 共同考量写入时效 #SaveDB_Time#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.longitude
is '经度 #Longitude#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.latitude
is '纬度 #Latitude#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.originallongitude
is '原始经度 #OriginalLongitude#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.originallatitude
is '原始纬度 #OriginalLatitude#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.speed
is '速度 #Speed#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.altitude
is '高程 #Altitude#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.direction
is '方向 北从0开始 #Direction#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.locationtext
is '位置 #LocationText#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.lbshruser_id
is '驾驶员 0代表没有驾驶员 #HrUser_Id# #LbsHrUser_Id#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.emlocstatusv
is '定位状态 未定位/无效定位/已定位 #EmLocStatusV#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.empositioningmodev
is '定位模式 使用GPS定位/使用北斗定位等 #EmPositioningModeV#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.gnsssatellitenumber
is '定位卫星数 没有捕获该数值，则填-1 #GnssSatelliteNumber#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.gprssignalintensity
is '无线通信网络信号强度 没有捕获该数值，则填-1 #GprsSignalIntensity#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.isoverspeedalert
is '超速报警 #IsOverSpeedAlert#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.isoutofboundsalert
is '越界报警 #IsOutOfBoundsAlert#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.isfatiguedrivingalert
is '疲劳驾驶 #IsFatigueDrivingAlert#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.isaccoff
is 'Acc点火关闭 #IsAccOff#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.emgnssfaultv
is 'GNSS故障 #EmGnssFaultV#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.emdevicepowerstatusv
is '设备电源状态 #EmDevicePowerStatusV#';
comment on column UTB_LBS_LOC_HIS_SUPERMAP.emadditionequipmentfaultv
is '附加设备故障 #EmAdditionEquipmentFaultV#';
-- Create/Recreate primary, unique and foreign key constraints
alter table UTB_LBS_LOC_HIS_SUPERMAP
add primary key (ID)
using index
tablespace TBS_LBS_SINOPORT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
--Create/Recreate Index
create index IX_UTB_LBS_LOC_HIS_SUPERMAP_LOCTIME ON UTB_LBS_LOC_HIS_SUPERMAP(lbsvehitem_id,loc_time)
上一条：
下一条：
相关评论
发表评论