Oracle建分區(qū)表

    1、按月創(chuàng)建分區(qū)
    create table T_MHDP
    (
    ID VARCHAR2(50) not null,
    FLT_AIRCODE VARCHAR2(4),
    FLT_NUMBER NUMBER(7),
    FLT_DATE DATE
    ) partition by range (FLT_DATE)
    (
    partition PART201105 values less than (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TS_MHDP_1105
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    ),
    partition PART201106 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TS_MHDP_1106
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    ),
    partition PART201107 values less than (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TS_MHDP_1107
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    ),
    partition PARTDEFAULT values less than (TO_DATE(' 9999-12-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace TS_MHDP
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    )
    );
    2、創(chuàng)建全局索引
    create index INDEX_MHDP_PK on T_MHDP(ID)
    tablespace TS_INDEX_MHDP
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    );
    3、創(chuàng)建本地索引
    CREATE INDEX INDEX_MHDP_PK ON T_MHDP(ID) LOCAL (
    PARTITION PART201105 TABLESPACE TS_MHDP_INDEX_1105,
    PARTITION PART201106 TABLESPACE TS_MHDP_INDEX_1106,
    PARTITION PART201107 TABLESPACE TS_MHDP_INDEX_1107,
    PARTITION PARTDEFAULT TABLESPACE TS_MHDP_INDEX
    );