电脑技术学习

使用Oracle Partition Table对日志表进行分区

dn001

  某个系统中有个日志型的表,现在的大小大概超过500MB,与该表相关的语句的执行计划都是对这个表进行全表扫描。

  下面是该表的定义:

  01 create table p_siteaccesslog

  02 (

  03;;logIDvarchar(40) not null,

  04;;infoType;;;;;varchar(20) default 'site',

  05;;siteId; varchar(30),

  06;;columnId;;;;;varchar(30),

  07;;infoId; varchar(30),

  08;;url;;varchar(300),

  09;;location;;;;;varchar(80),

  10;;action; varchar(20),

  11;;accessDate;;;varchar(10),

  12;;accessTime;;;varchar(8),

  13;;sessionID;;;;varchar(90),

  14;;userName;;;;;varchar(30),

  15;;userBrowser;;varchar(50),

  16;;userOS; varchar(30),

  17;;userIp; varchar(30),

  18;;localization;varchar(100),

  19;;constraint PK_p_siteaccesslog primary key (logID)

  20 )

  该表有一个主键,为每条日志分配一个主键,感觉上一百年都不会用上这个主键来查一次数据。所以在新的分区表上将其去掉。

  1 test$ora8i@4.20 SQL> l

  21* select segment_name,sum(bytes/1048576) MB from user_extents group by segment_name order by 2 desc

  3 test$ora8i@4.20 SQL> /

  4

  5 SEGMENT_NAME;;MB

  6 ---------------------------------------- ----------

  7 P_SITEACCESSLOG 536

  由于是日志型的表,第一感觉就是按照时间分区,并淘汰(切换)旧的数据。

  于是沿着这个思路,按照每月一个分区的策略构建分区表:

  01 create table my_p_siteaccesslog

  02 (

  03;;logIDvarchar(40) not null,

  04;;infoType;;;;;varchar(20) default 'site',

  05;;siteId; varchar(30),

  06;;columnId;;;;;varchar(30),

  07;;infoId; varchar(30),

  08;;url;;varchar(300),

  09;;location;;;;;varchar(80),

  10;;action; varchar(20),

  11;;accessDate;;;varchar(10),

  12;;accessTime;;;varchar(8),

  13;;sessionID;;;;varchar(90),

  14;;userName;;;;;varchar(30),

  15;;userBrowser;;varchar(50),

  16;;userOS; varchar(30),

  17;;userIp; varchar(30),

  18;;localization;varchar(100)

  19 )

  20 partition by range (accessDate)

  21 (

  22;;partition pbf2009 values less than ('2009-01-01'),

  23;;partition p200901 values less than ('2009-02-01'),

  24;;partition p200902 values less than ('2009-03-01'),

  25;;partition p200903 values less than ('2009-04-01'),

  26;;partition p200904 values less than ('2009-05-01'),

  27;;partition p200905 values less than ('2009-06-01'),

  28;;partition p200906 values less than ('2009-07-01'),

  29;;partition p200907 values less than ('2009-08-01'),

  30;;partition p200908 values less than ('2009-09-01'),

  31;;partition p200909 values less than ('2009-10-01'),

  32;;partition p200910 values less than ('2009-11-01'),

  33;;partition p200911 values less than ('2009-12-01'),

  34;;partition p200912 values less than ('2010-01-01'),

  35;;partition p201001 values less than ('2010-02-01'),

  36;;partition p201002 values less than ('2010-03-01'),

  37;;partition p201003 values less than ('2010-04-01'),

  38;;partition p201004 values less than ('2010-05-01'),

  39;;partition p201005 values less than ('2010-06-01'),

  40;;partition p201006 values less than ('2010-07-01'),

  41;;partition p201007 values less than ('2010-08-01'),

  42;;partition p201008 values less than ('2010-09-01'),

  43;;partition p201009 values less than ('2010-10-01'),

  44;;partition p201010 values less than ('2010-11-01'),

  45;;partition p201011 values less than ('2010-12-01'),

  46;;partition p201012 values less than ('2011-01-01')

  47 )