当前位置:首页 > 开发 > 数据库 > 正文

Oracle建分区表

发表于: 2012-07-06   作者:cutesunshineriver   来源:转载   浏览:
摘要: 1、按月创建分区 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) ( partit
1、按月创建分区
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、创建全局索引
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、创建本地索引
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
);

Oracle建分区表

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号