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) */