Oracle 11g table smart monthly Partition

Oracle 11g table smart monthly partition before the log table more than 7 million data, involved in the log table business query speed is very slow, a month of data query is about 30 seconds, the study found that oracle has a table Partition Function of 11 GB, and more intelligent partitioning by month: the first step is to make sure that the table can be partitioned by www.2cto.com, first, create a partition table -- INTERVAL partition -- this is actually an Enhanced Function of range partitions. This function can be used to automatically add new partitions as needed, this saves you the need to ADD or SPLIT new partitions. Create table SYS_LOG_TEM (pid NUMBER not null, yhdm VARCHAR2 (30), bmdm VARCHAR2 (12), pdaid VARCHAR2 (512), simid VARCHAR2 (50), logmodule VARCHAR2 (1 ), logtype VARCHAR2 (4), operatetype CHAR (1), methodname VARCHAR2 (100), methodinfo VARCHAR2 (2000), execstatus VARCHAR2 (1), execerrorinfo VARCHAR2 (1000), alltimespent NUMBER, proxytimespent NUMBER, logcontent VARCHAR2 (4000), logtime DATE default sysdate, mem O VARCHAR2 (200), returnflag CHAR (1), returninfo VARCHAR2 (1000), gpsx VARCHAR2 (20), gpsy VARCHAR2 (20) partition by range (logtime) INTERVAL (NUMTOYMINTERVAL (1, 'month') (PARTITION P1 values less than (TO_DATE ('2017-4-1 ', 'yyyy-MM-DD '))); (because the current minimum data in my table is in February, I asked it to partition from February.) Step 2, insert old log table data into the new partition table insert into SYS_LOG_TEM select * from SYS_LOG; insert into SYS_LOG_TEM select pid, yhdm, bmdm, pdaid, s Imid, logmodule, logtype, operatetype, methodname, methodinfo, execstatus, execerrorinfo, alltimespent, proxytimespent, logcontent, nvl (logtime, to_date ('2017-06-01 00:00:00 ', 'yyyy-MM-dd HH24: mi: ss'), memo, returnflag, returninfo, gpsx, gpsy from SYS_LOG; it took me 7 minutes to process 7272844 data entries. Here, the reason for the null processing of the logtim field is that once the oracle table is partitioned by month, The time field cannot be null. Otherwise, the ora-14402 update will be reported during the update. partition keyword column will cause partition changes update sys_log_tem set logtime = n Ull where logtime = to_date ('2017-06-01 00:00:00 ', 'yyyy-MM-dd HH24: mi: ss'). I wonder if there is any other solution to this problem, however, when doing this, I should also be able to take a custom time, which is also justified by comparing the test query speed on plsql-the execution time of table queries by monthly partition: 0.468 seconds select count (pid) from (select sysLog. pid, sysLog. yhdm, sysLog. operateType, sysLog. methodName, sysLog. pdaId, sysLog. simId, sysLog. logContent, sysLog. logModule, sysLog. logTime from sys_log_tem sysLog, Acl_User u where sysLog. yhdm (+) = u. yhdm and sys Log. logTime> = to_date ('2017-06-01 15:48:03 ', 'yyyy-mm-dd HH24: MI: ss') and sysLog. logTime <= to_date ('1970-06-30 15:48:11 ', 'yyyy-mm-dd HH24: MI: ss') -- this is the execution time of a table without partitions: 18 seconds select count (pid) from (select sysLog. pid, sysLog. yhdm, sysLog. operateType, sysLog. methodName, sysLog. pdaId, sysLog. simId, sysLog. logContent, sysLog. logModule, sysLog. logTime from Sys_Log sysLog, Acl_User u where sysLog. yhdm (+) = u. yhdm and s YsLog. logTime> = to_date ('2017-06-01 15:48:03 ', 'yyyy-mm-dd HH24: MI: ss') and sysLog. logTime <= to_date ('2017-06-30 15:48:11 ', 'yyyy-mm-dd HH24: MI: ss ')) I just created a primary key for the new partition table, but I didn't get it because I found that the query speed is much slower than that of the old table if I created a new date index, I don't know why. Anyway, the newly created table has a primary key. The structure of view SQL is as follows: www.2cto.com -- Create tablecreate table SYS_LOG (pid NUMBER not null, yhdm VARCHAR2 (30), bmdm VARCHAR2 (12), pdaid VARCHAR2 (512), simid VARCHAR2 (50), logmo Dule VARCHAR2 (1), logtype VARCHAR2 (4), operatetype CHAR (1), methodname VARCHAR2 (100), methodinfo VARCHAR2 (2000), execstatus VARCHAR2 (1 ), execerrorinfo VARCHAR2 (1000), alltimespent NUMBER, proxytimespent NUMBER, logcontent VARCHAR2 (4000), logtime DATE default sysdate, memo VARCHAR2 (200), returnflag CHAR (1 ), returninfo VARCHAR2 (1000), gpsx VARCHAR2 (20), gpsy VARCHAR2 (20) partition by range (LOGTIME) (Partition P1 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited), partition SYS_P22 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ') tablespace SYSTEM pctfree 10 p Ctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited), partition SYS_P23 values less than (TO_DATE ('2012-06-01 00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ') tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited ), partition SYS_P24 values less than (TO_DATE ('2 012-07-01 00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited), partition SYS_P25 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ') tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (Initial 64 K next 1 M minextents 1 maxextents unlimited), partition SYS_P26 values less than (TO_DATE ('2012-09-01 00:00:00 ', 'syyyy-MM-DD HH24: MI: ss ', 'nls _ CALENDAR = GREGORIAN ') tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited ), partition sys_p2 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: SS ', 'Nls _ CALENDAR = GREGORIAN') tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited ), partition SYS_P28 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents Unlimited), partition SYS_P29 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN ')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited); -- Create/Recreate primary, unique and foreign key constraints alter table SYS_LOG add constraint PID primary key (PID) using index tab Lespace SYSTEM pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K next 1 M minextents 1 maxextents unlimited); 11g Partition Table automatically created by time (Interval Partitioning) I used the new oracle 11G INTERVAL partition feature, which is actually an enhanced range partition feature. This feature can be used to automatically add new partitions as needed, this saves you the need to ADD or SPLIT new partitions. If it is 9i, you must write the partition date according to the log value in your table. Because the log table increases data in real time, you have to manually create new partitions on a regular basis, for example, alter table sys_log_tem add PARTITION logtime values less than ('2017-06-01 00:00:00 ') tablespace system ;..... the log query speed after partitioning is not generally high!