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';