1、分区表创建： 对轨迹表T_TRACK进行组合分区，首选按轨迹时间T_TRACK_TM进行区间分区，然后，再根据公司代码C_COMP_CDE进行列表分区。
create table T_TRACK (
N_TRACK_ID NUMBER(20) not null,
C_COMP_CDE VARCHAR2(6),
T_TRACK_TM DATE not null,
C_CAR_NO VARCHAR2(50)
)partition by range(T_TRACK_TM)
subpartition by list(C_COMP_CDE)
(
partition P_2009_11 values less than (to_date('2009-12-01','yyyy-MM-dd'))
(
subpartition P_2009_11_P1013 values('P1013')
)
);
2、常用操作语句：
--添加子分区
alter table T_TRACK
modify partition P_2005_01
add subpartition P_2005_01_P1017 values('P1017');
--添加分区
alter table T_TRACK add partition P_2005_04 values less than(to_date('2005-05-01','yyyy-MM-dd'))
(
subpartition P_2005_04_P1013 values('P1013'),
subpartition P_2005_04_P1013 values('P1014'),
subpartition P_2005_04_P1013 values('P1015'),
subpartition P_2005_04_P1013 values('P1016')
)
--删除子分区
alter table T_TRACK drop subpartition p_2005_01_p1017;
--删除分区
alter table T_TRACK drop partition p_2005_04;
3、书写ORACLE包封装创建分区
包声明   /**//*
 需要的权限：
选择视图：dba_tablespace,dba_tab_parition,dba_tab_subpartition
创建表空间的权限
同时，在包体实现中，指明表空间存放路径
 */
--包声明
create or replace package GPS_PARTITION is
/**//*判断表空间是否已经存在*/
function tableSpaceExist(tableSpaceName varchar2) return boolean;
/**//*创建表空间*/
procedure createTableSpace(tableSpaceName varchar2);
/**//*判断分区是否存在*/
function partitionExist(tableName varchar2,partitionName varchar2) return boolean;
/**//*判断子分区是否存在*/
function subPartitionExist(tableName varchar2,subPartitionName varchar2) return boolean;
/**//*删除分区*/
procedure dropPartition(tableName varchar2,partitionName varchar2);
/**//*删除子分区*/
procedure dropSubPartition(tableName varchar2,subPartitionName varchar2);
--供DBA使用
/**//*新建当年的分区,供年初始DBA调用*/
procedure createAYearPartition(tableName varchar2,startMonth date);
/**//*新建某月分区(子分区按公司分，从公司表中取)，供每月DBA调用*/
procedure addAMonthPartition(tableName varchar2,theMonth date);
--供应用程序调用
/**//*新增某公司一年内的所有分区*/
procedure addACompOneYearPartition(tableName varchar2,startMonth date,compCde varchar2);
/**//*新增某公司一月的分区*/
procedure addACompOneMonthPartition(tableName varchar2,theMonth date,compCde varchar2);
end GPS_PARTITION;
包实现： create or replace package body GPS_PARTITION is
/**//*公司游标*/
cursor corpCursor is
select distinct(c_comp_cde) from t_sys_corp where c_is_available='1';
upperTableName varchar2(80);
upperTableSpaceName varchar2(80);--TS_P1013
upperCompCde varchar2(5);
tableSpaceDir varchar2(100):='d:\oracle\tbtest\';
/**//*判断表空间是否已经存在*/
function tableSpaceExist(tableSpaceName varchar2) return boolean is
resultNum number :=0;
begin
upperTableSpaceName := UPPER(tableSpaceName);
resultNum:=0;
dbms_output.put_line('判断表空间'||upperTableSpaceName||'是否存在:');
select count(*) into resultNum from dba_tablespaces where tablespace_name=upperTableSpaceName;
dbms_output.put_line('结果是(大于0存在，小于0不存在)：'||resultNum);
if (resultNum>0) then
return true;
else
return false;
end if;
end;
/**//*创建表空间*/
procedure createTableSpace(tableSpaceName varchar2)
is
tablespaceSql varchar2(500);
begin
tablespaceSql:='create tablespace '||tableSpaceName||' datafile ''' ||
tableSpaceDir||tableSpaceName||'.dbf'''||' size 10M reuse default storage(initial 512K next 512K minextents 8 maxextents 4096 pctincrease 0) online permanent';
dbms_output.put_line(tablespaceSql);
execute immediate tablespaceSql;
end;
/**//*判断分区是否存在*/
function partitionExist(tableName varchar2,partitionName varchar2) return boolean is
resultNum number :=0;
begin
upperTableName := UPPER(tableName);
dbms_output.put_line('判断分区'||partitionName||'在表'||tableName||'中是否存在:');
resultNum:=0;
select count(*) into resultNum from dba_tab_subpartitions where partition_name=partitionName and table_name=upperTableName;
dbms_output.put_line('select count(*) into resultNum from dba_tab_subpartitions where partition_name='||partitionName||' and table_name='||upperTableName);
dbms_output.put_line('结果是(大于0存在，小于0不存在)：'||resultNum);
if (resultNum>0) then
return true;
else
return false;
end if;
end;
/**//*判断子分区是否存在*/
function subPartitionExist(tableName varchar2,subPartitionName varchar2) return boolean
is
resultNum number :=0;
begin
upperTableName := UPPER(tableName);
dbms_output.put_line('判断子分区'||subPartitionName||'在表'||tableName||'中是否存在:');
resultNum:=0;
select count(*) into resultNum from dba_tab_subpartitions where table_name=upperTableName and subpartition_name=subPartitionName;
dbms_output.put_line('select count(*) into resultNum from dba_tab_subpartitions where subpartition_name='||subPartitionName||' and table_name='||upperTableName);
dbms_output.put_line('结果是(大于0存在，小于0不存在)：'||resultNum);
if (resultNum>0) then
return true;
else
return false;
end if;
end;
/**//*删除分区*/
procedure dropPartition(tableName varchar2,partitionName varchar2)
is
begin
upperTableName := UPPER(tableName);
execute immediate
'alter table '||upperTableName||'drop partition'||partitionName;
end;
/**//*删除子分区*/
procedure dropSubPartition(tableName varchar2,subPartitionName varchar2)
is
begin
upperTableName := UPPER(tableName);
execute immediate
'alter table '||upperTableName||'drop subpartition'||subPartitionName;
end;
/**//*新建当年的分区,供年初始DBA调用*/
procedure createAYearPartition(tableName varchar2,startMonth date)
is
monthNum number;
theMonth date;
begin
upperTableName := UPPER(tableName);
theMonth := startMonth;
monthNum := to_char(startMonth,'MM');
while monthNum12 loop
addAMonthPartition(upperTableName,theMonth);
theMonth := theMonth+numtoyminterval(1,'month');
monthNum := to_char(theMonth,'MM');
end loop;
if monthNum=12 then
addAMonthPartition(upperTableName,theMonth);
end if;
end;
/**//*新建某月分区(子分区按公司分，从公司表中取)，供每月DBA调用*/
procedure addAMonthPartition(tableName varchar2,theMonth date)
is
compCde varchar2(5);
begin
upperTableName := UPPER(tableName);
if corpCursor%ISOPEN then
close corpCursor;
end if;
open corpCursor;
fetch corpCursor into compCde;
if corpCursor%found then
addACompOneMonthPartition(tableName,theMonth,compCde);
end if;
while corpCursor%found loop
fetch corpCursor into compCde;
if corpCursor%found then
addACompOneMonthPartition(tableName,theMonth,compCde);
end if;
end loop;
close corpCursor;
end;
/**//*新增某公司一月的分区*/
procedure addACompOneMonthPartition(tableName varchar2,theMonth date,compCde varchar2)
is
yearStr varchar2(4);--年
monthNum number; --月
partitionName varchar2(9);--P_2009_12
subPartitionName varchar2(15);--P_2009_12_P1013
maxDate varchar2(10);
sqlStr varchar2(2000);--要执行的SQL语句
begin
yearStr:=to_char(theMonth,'yyyy');
upperTableName := UPPER(tableName);
upperCompCde := UPPER(compCde);
upperTableSpaceName := 'TS_'||upperCompCde||'_'||yearStr;
if not tableSpaceExist(upperTableSpaceName) then
createTableSpace(upperTableSpaceName);
end if;
yearStr:=to_char(theMonth,'yyyy');
monthNum := to_char(theMonth,'MM');
if monthNum=12 then
maxDate:=yearStr+1;
maxDate:=maxDate||'-01'||'-01';
else
maxDate:=yearStr||'-'||lpad((monthNum+1),2,'0')||'-01';
end if;
partitionName:='P_'||yearStr||'_'||lpad((monthNum),2,'0');
subPartitionName:=partitionName||'_'||upperCompCde;
if partitionExist(upperTableName,partitionName) then
--分区存在
if not subPartitionExist(upperTableName,subPartitionName) then
sqlStr:='alter table '||upperTableName||' modify partition '||partitionName
||' add subpartition '||subPartitionName||' values('''||upperCompCde||''') tablespace '||upperTableSpaceName;
end if;
else
--分区不存在
sqlStr:='alter table '||upperTableName||' add partition '||partitionName
||' values less than(to_date('''||maxDate||''',''yyyy-MM-dd''))('
||' subpartition '||subPartitionName||' values('''||upperCompCde||''')'||' tablespace '||upperTableSpaceName||' )';
end if;
if length(sqlStr)>0 then
dbms_output.put_line('执行创建分区或子分区语句：'||sqlStr);
execute immediate sqlStr;
end if;
end;
/**//*新增某公司一年内的所有分区*/
procedure addACompOneYearPartition(tableName varchar2,startMonth date,compCde varchar2)
is
monthNum number;
theMonth date;
begin
upperTableName := UPPER(tableName);
upperCompCde := UPPER(compCde);
theMonth := startMonth;
monthNum := to_char(startMonth,'MM');
while monthNum12 loop
addACompOneMonthPartition(upperTableName,theMonth,compCde);
theMonth := theMonth+numtoyminterval(1,'month');
monthNum := to_char(theMonth,'MM');
end loop;
if monthNum=12 then
addACompOneMonthPartition(upperTableName,theMonth,compCde);
end if;
end;
end GPS_PARTITION;
4.新增公司时，通过IBATIS调用存储过程为此公司添加一年的分区
/** *//**
* 为公司添加轨迹表一年的分区
* @param compCde
* @param date
* @throws Exception
*/
public static void addACompOneYearPartition(String compCde,String date,DAOBase dao) throws Exception {
MapString,String> keys = new HashMapString,String>();
keys.put("tableName", "T_GPS_TRACK");
keys.put("compCde", compCde);
keys.put("theMonth", date);
dao.doView("track.addACompOneYearPartition", keys);
}
IBATIS配置文件：
parameterMap id="partitionParam" class="java.util.Map">
parameter property="tableName" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
parameter property="theMonth" jdbcType="DATE" javaType="java.lang.String" mode="IN" />
parameter property="compCde" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
span style="color: #800000">parameterMap>
procedure id="addACompOneYearPartition" parameterMap="partitionParam">
{call gps_partition.addACompOneYearPartition(?,to_date(?,'yyyy-MM-dd'),?)}
span style="color: #800000">procedure>
5、查询表中数据时，传入分区： /**
* 取得最靠近(=)某时间点的终端的轨迹
* @param temiId
* @param theDate
* @return
* @throws Exception
*/
public Track getCloserLeftTrack(String temiId,
String theDate,String compCde) throws Exception {
Map> keys = new HashMapString, String>();
keys.put("temiId", temiId);
keys.put("theDate", theDate);
keys.put("subPartitionName", TrackPatitionUtils.getSubPartitionName(compCde, theDate));
Object obj = dao.doView("track.getCloserLeftTrack", keys);
if(obj!=null)
return (Track) obj;
return null;
} /**
* 取子分区名称
* 格式：P_YYYY_MM_COMPCDE (如：P_2009_12_P3456)
* @param compCde 公司代码
* @param date
* @return
* @throws ParseException
*/
public static String getSubPartitionName(String compCde,String date) throws ParseException{
Assert.notNullAndEmpty(compCde, "取轨迹表分区名称，公司代码不能为空!");
return getPartitionName(date)+"_"+compCde;
}
/**
* 取分区名称
* 格式：P_YYYY_MM(如：P_2009_12)
* @param date
* @return
* @throws ParseException
*/
public static String getPartitionName(String date) throws ParseException{
Assert.notNullAndEmpty(date, "取轨迹表分区名称，时间不能为空!");
date = DateUtils.format(DateUtils.parse(date, "yyyy-MM-dd"), "yyyy_MM");
return "P_"+date;
}
select * from T_GPS_TRACK
isNotEmpty property="subPartitionName" close=" " open=" ">
subpartition($subPartitionName$)
span style="color: #800000">isNotEmpty>
isEmpty property="subPartitionName">
isNotEmpty property="patitionName" close=" " open=" ">
partition($patitionName$)
span style="color: #800000">isNotEmpty>
span style="color: #800000">isEmpty>
where c_device_no=#deviceNo#
|