Oracle表分区与索引的创建

create table TT_FLOW_TEST
(
  id                 NUMBER not null,
  transfer_batch_key VARCHAR2(30) not null,
  plan_begin_tm      DATE not null,
  transfer_zone_code VARCHAR2(30) not null,
  transfer_area_code VARCHAR2(30),
  batch_code         VARCHAR2(30) not null,
  dist_code          VARCHAR2(30),
  area_code          VARCHAR2(30),
  product_code       VARCHAR2(30),
  bill_num           NUMBER(10),
  weight             NUMBER(16,2),
  data_type          NUMBER(2) not null,
  report_dt          DATE
)
PARTITION BY RANGE (report_dt)
    INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
    (PARTITION FUZZY_FLOW_P20140703 VALUES LESS THAN (TO_DATE('2014-11-19', 'YYYY-MM-DD')))
tablespace OMP_DW_DATA1;
-- Add comments to the table
comment on table TT_TSFR_FUZZY_FLOW
  is '中转班次流向明细表';
-- Add comments to the columns
comment on column TT_FLOW_TEST.id
  is '业务主键';
comment on column TT_FLOW_TEST.transfer_batch_key
  is '中转班次key';
comment on column TT_FLOW_TEST.plan_begin_tm
  is '班次计划开始日期';
comment on column TT_FLOW_TEST.transfer_zone_code
  is '中转网点代码';
comment on column TT_FLOW_TEST.transfer_area_code
  is '中转网点所属地区';
comment on column TT_FLOW_TEST.batch_code
  is '班次编码';
comment on column TT_FLOW_TEST.dist_code
  is '城市代码';
comment on column TT_FLOW_TEST.area_code
  is '地区代码';
comment on column TT_FLOW_TEST.product_code
  is '产品类型';
comment on column TT_FLOW_TEST.bill_num
  is '票数';
comment on column TT_FLOW_TEST.weight
  is '重量';
comment on column TT_FLOW_TEST.data_type
  is '数据类型';
comment on column TT_FLOW_TEST.report_dt
  is '分区字段';
-- Create/Recreate indexes
create index INDEX_TT_FLOW_TEST on TT_FLOW_TEST (batch_code,DATA_TYPE,REPORT_DT)
  tablespace OMP_DW_DATA1;
-- Create/Recreate primary, unique and foreign key constraints
alter table TT_FLOW_TEST
  add constraint PK_TT_FLOW_TEST primary key (ID)
  using index
  tablespace OMP_DW_DATA1;

--按月分区
PARTITION BY RANGE (report_dt)
    INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
    (PARTITION P_PKP_Report_TIME VALUES LESS THAN (TO_DATE('2014-12-01', 'YYYY-MM-DD')));

--oracle 小知识
1.oracle 查看表大小
SELECT SUM(T.BYTES) / 1024 / 1024 / 1024
  FROM DBA_SEGMENTS T
WHERE T.SEGMENT_NAME = 'TT_TSFR_FUZZY_ABNORMAL';
2.oracle 使用hash解析会比较快，用全表扫描的时候是索引的大小和表大小差不多的时候
/*+ USE_HASH(TT_CONVEYANCE_DETAIL_UNLOAD,TT_WaybillInfo) FULL(TT_CONVEYANCE_DETAIL_UNLOAD)*/
/*+ INDEX(t,IDX_BATCHCODE_LOCAL)*/  强制走索引
3.oracle 并行
/*+ PARALLEL(R 3) */