oracle資料表建立分區

標籤：

情境： 遇到1億資料量的資料需要根據使用者名稱做些資料統計分析，想直接做些彙總計算基本沒可能，於是打算先根據日期按照年月建立分區，然後對各個分區分別進行統計，最後匯總結果。

操作方式，建立分區表，然後用ETL工具抽取源並寫入新表。

create table TEMP_TEST (  log_id              NUMBER(20),  user_id             NUMBER(20),  user_name           NVARCHAR2(200),  money               NUMBER(30,8),  history_total       NUMBER(30,8),  order_id            NVARCHAR2(100),  money_type_id       NUMBER(20),  reason_id           NUMBER(20),  operation_user_name NVARCHAR2(200),  operation_user_ip   NVARCHAR2(100),  remark              NVARCHAR2(200),  osn                 NVARCHAR2(100),  isn                 NVARCHAR2(100),  return_sn           NVARCHAR2(100),  repeate_flag        NUMBER(3),  create_date         DATE,  modify_date         DATE,  status              NUMBER(20),  batch_id            NUMBER(38)) nologgingpartition  by range (create_date) (   partition  create_date1500 values less than (TO_DATE(‘2015-01-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1501 values less than (TO_DATE(‘2015-02-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1502 values less than (TO_DATE(‘2015-03-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1503 values less than (TO_DATE(‘2015-04-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1504 values less than (TO_DATE(‘2015-05-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1505 values less than (TO_DATE(‘2015-06-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1506 values less than (TO_DATE(‘2015-07-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1507 values less than (TO_DATE(‘2015-08-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1508 values less than (TO_DATE(‘2015-09-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1509 values less than (TO_DATE(‘2015-10-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1510 values less than (TO_DATE(‘2015-11-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1511 values less than (TO_DATE(‘2015-12-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1512 values less than (TO_DATE(‘2016-01-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1601 values less than (TO_DATE(‘2016-02-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1602 values less than (TO_DATE(‘2016-03-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1603 values less than (TO_DATE(‘2016-04-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1604 values less than (TO_DATE(‘2016-05-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1605 values less than (TO_DATE(‘2016-06-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1606 values less than (TO_DATE(‘2016-07-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1607 values less than (TO_DATE(‘2016-08-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1608 values less than (TO_DATE(‘2016-09-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1609 values less than (TO_DATE(‘2016-10-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1610 values less than (TO_DATE(‘2016-11-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1611 values less than (TO_DATE(‘2016-12-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS,  partition  create_date1612 values less than (TO_DATE(‘2017-01-01 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) tablespace DW_STG_TBS)

 

oracle資料表建立分區