oracle表分区

表分区能显著提高数据库性能，以应对千万级以上数据，以下是个实例：

--表分区建立

create tablespace cms_space01 datafile '/opt/oracle/oradata/orcl/cms_01.dbf' size 100m autoextend ON;

create tablespace cms_space02 datafile '/opt/oracle/oradata/orcl/cms_02.dbf' size 100m autoextend ON;

 

CREATE TABLESPACE CMS_SPACE03 DATAFILE '/opt/oracle/oradata/orcl/cms_03.dbf' SIZE 100m AUTOEXTEND ON;

 

CREATE TABLESPACE CMS_SPACE04 DATAFILE '/opt/oracle/oradata/orcl/cms_04.dbf' SIZE 100m AUTOEXTEND ON;

CREATE TABLESPACE CMS_SPACE05 DATAFILE '/opt/oracle/oradata/orcl/cms_05.dbf' SIZE 100m AUTOEXTEND ON;

CREATE TABLESPACE CMS_SPACE06 DATAFILE '/opt/oracle/oradata/orcl/cms_06.dbf' SIZE 100m AUTOEXTEND ON;

CREATE TABLESPACE CMS_SPACE07 DATAFILE '/opt/oracle/oradata/orcl/cms_07.dbf' SIZE 100m AUTOEXTEND ON;

CREATE TABLESPACE CMS_SPACE08 DATAFILE '/opt/oracle/oradata/orcl/cms_08.dbf' SIZE 100m AUTOEXTEND ON;

--创建分区表

CREATE TABLE CMS_INTEFACE_STATUS(

    STATUSID NUMBER(16,0) NOT NULL PRIMARY KEY,

    OBJECTID VARCHAR2(32),

    OBJECTNAME VARCHAR2(128 CHAR),

    ELEMENTTYPE VARCHAR2(32),

    SACTION NUMBER(2),

    CURRENTSTATUS NUMBER(3),

    CREATETIME DATE DEFAULT SYSDATE,

    LASTMODIFYTIME DATE,

    MEMO VARCHAR2(1024 CHAR)

)

PARTITION BY RANGE(CREATETIME)(  

  PARTITION CMS_PART01 VALUES LESS THAN(TO_DATE('2013-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE CMS_SPACE01,

  PARTITION cms_part02 VALUES LESS THAN(TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE cms_space02,

  PARTITION CMS_PART03 VALUES LESS THAN(TO_DATE('2014-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE CMS_SPACE03,

  PARTITION CMS_PART04 VALUES LESS THAN(TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE CMS_SPACE04,

  PARTITION CMS_PART05 VALUES LESS THAN(TO_DATE('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE CMS_SPACE05,

  PARTITION CMS_PART06 VALUES LESS THAN(TO_DATE('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE CMS_SPACE06,

  PARTITION CMS_PART07 VALUES LESS THAN(TO_DATE('2016-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE CMS_SPACE07,

  PARTITION cms_part08 VALUES LESS THAN (MAXVALUE)TABLESPACE cms_space08

);

COMMENT ON COLUMN CMS_INTEFACE_STATUS.STATUSID IS '数据库自增编号';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.OBJECTID IS '内容编号，如为节目单时填写直播频道编号';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.OBJECTNAME IS  '内容名称';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.ELEMENTTYPE IS '内容类型';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.SACTION IS '内容的操作：1、新增；2、修改；3、删除';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.CURRENTSTATUS IS '内容当前状态：1、同步BMS中；2同步BMS完成等待BMS打包；3、BMS打包完成；4、同步OMS中；5、同步OMS完成等待OMS返回；6、同步OMS成功；11、同步BMS失败；16、同步OMS失败';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.CREATETIME IS '创建时间';

COMMENT ON COLUMN CMS_INTEFACE_STATUS.LASTMODIFYTIME IS '最后修改时间';

COMMENT ON COLUMN "VISION"."CMS_INTEFACE_STATUS"."MEMO" IS '描述';

--创建序列

create sequence SEQ_CMS_INTEFACE_STATUS minvalue 1 maxvalue 99999999999999999999999 start with 1 increment by 1 CACHE 20 cycle;