oracle分区表的创建

1.在已知数据表数据量比较大时，我们可以选择创建分区表增强搜索性能

 在此创建以周为单位的分区表

create table APPS.MQ_RECEIVE_LOG
(
  id               NUMBER not null,
  msg_id           VARCHAR2(100),
  calsign          VARCHAR2(20),
  msg_os           VARCHAR2(10),
  msg_type         VARCHAR2(10),
  msg_receive_time DATE,
  remark           VARCHAR2(500),
  msg_content      CLOB
)
partition by range(msg_receive_time) interval(NUMTODSINTERVAL(7,'day'))
(
partition P20191225 values less than (to_date('2019-12-25','YYYY-MM-DD'))
)
           

NUMTOYMINTERVAL 可选值  'YEAR' 'MONTH'
NUMTODSINTERVAL 可选值  'DAY' 'HOUR' 'MINUTE' 'SECOND'
           

2.测试分区表是否真正可分区

插入数据

INSERT INTO "UDMETA"."MQ_RECEIVE_LOG" ("ID", "MSG_ID", "CALSIGN", "MSG_OS", "MSG_TYPE", "MSG_RECEIVE_TIME", "REMARK", "MSG_CONTENT") VALUES ('2', '1', '1', '1', '1', TO_DATE('2020-08-02 15:47:08', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL);
INSERT INTO "UDMETA"."MQ_RECEIVE_LOG" ("ID", "MSG_ID", "CALSIGN", "MSG_OS", "MSG_TYPE", "MSG_RECEIVE_TIME", "REMARK", "MSG_CONTENT") VALUES ('2', '1', '1', '1', '1', TO_DATE('2018-08-02 15:47:08', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL);
           

查看分区，分区成功

select *  from user_tab_partitions  a where a.table_name='MQ_RECEIVE_LOG';