oracle分区表按时间自动创建

小咪咪 2022-06-16 07:36 234阅读 0赞

**表分区是一种思想，分区表示一种技术实现。当表的大小过G的时候可以考虑进行表分区，提高查询效率，均衡IO。oracle分区表是oracle数据库提供的一种表分区的实现形式。表进行分区后，逻辑上仍然是一张表，原来的查询SQL同样生效，同时可以采用使用分区查询来优化SQL查询效率，不至于每次都扫描整个表。**

根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))  
根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))  
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))  
根据时分秒: NUMTODSINTERVAL( n, \{ 'DAY'|'HOUR'|'MINUTE'|'SECOND'\})

此时已经有普通表了，我创建了含相同字段的分区表，把数据导入到分区表中，再把原表删掉。

-- Create table（WMS_OPERATION_RECORD）
    create table DPHOMEWMS.WMS_OPERATION_RECORD_TMP
    (
      ID             NUMBER(19) not null,
      WAREHOUSE_ID   NUMBER(19),
      ASN_ID         NUMBER(19),
      PICK_TICKET_ID NUMBER(19),
      RELATION_CODE  VARCHAR2(50),
      OPERATION_TYPE VARCHAR2(50),
      OPERATION_ID   NUMBER(19),
      OPERATION_NAME VARCHAR2(50 CHAR),
      OPERATION_TIME TIMESTAMP(6) DEFAULT systimestamp not null,
      STATUS         VARCHAR2(10),
      TYPE           VARCHAR2(10),
      COUNTS         NUMBER(10)
    )tablespace DPHOMEWMS_DATA
    PARTITION BY RANGE (OPERATION_TIME) interval (numtoyminterval(1, 'month')) 
    STORE IN (DPHOMEWMS_DATA) 
    (
      partition OPERATION_RECORD_P01 values less than (TIMESTAMP' 2017-05-08 00:00:00')
      tablespace DPHOMEWMS_DATA
    );
    -- Add comments to the columns 
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.WAREHOUSE_ID
      is '仓库id';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.ASN_ID
      is '收货单id';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.PICK_TICKET_ID
      is '发货单ID';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.RELATION_CODE
      is '关联单据号(收货单或发货单的code)';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_TYPE
      is '操作类型';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_ID
      is '操作人ID';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_NAME
      is '操作人名称';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_TIME
      is '操作时间';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.STATUS
      is '状态';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.TYPE
      is '明细或者统计次数类型';
    comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.COUNTS
      is '统计次数';
    --创建本地索引
    create index IDX_WMS_OPERATION_RECORD  on DPHOMEWMS.WMS_OPERATION_RECORD_TMP(RELATION_CODE)
    tablespace DPHOMEWMS_INDEX local;
    update DPHOMEWMS.WMS_OPERATION_RECORD set OPERATION_TIME = sysdate where OPERATION_TIME is null;
    insert into DPHOMEWMS.WMS_OPERATION_RECORD_TMP select * from WMS_OPERATION_RECORD;
    drop table DPHOMEWMS.WMS_OPERATION_RECORD;
    alter table DPHOMEWMS.WMS_OPERATION_RECORD_TMP rename to WMS_OPERATION_RECORD;
    alter table DPHOMEWMS.WMS_OPERATION_RECORD enable row movement;

（1）OPERATION\_TIME是分区键，每一个月会自动创建一个分区，分区键不允许为null。

（2）alter table table\_name enable row movement;    是指允许分区表的分区键是可更新，当某一行更新时，如果更新的是分区列，并且更新后的列值不属于原来的这个分区，如果开启了这个选项，就会把这行从这个分区中delete掉，并加到更新后所属的分区，此时就会发生rowid的改变。相当于一个隐式的delete+insert，但是不会触发insert/delete触发器。如果没有开启这个选项，就会在更新时报错。