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#?
?
|