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

某个系统中有个日志型的表，现在的大小大概超过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 )

由于数据库是Oracle 10g，不是11g，无法支持interval分区，只能多创建几个未来的分区了。

表创建好之后，就导数据流，由于该表不算太大，500多兆的插入还是可以接受的。

1 insert into my_p_siteaccesslog select * from P_SITEACCESSLOG ;

2 commit;

下一步使用分区切换技术，将旧的数据搬走（不是删掉）

创建用于存放2008年数据大表

01 create table my_p_siteaccesslog_2008

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

使用分区切换，将2008年的数据切换出来：

1 ALTER TABLE my_p_siteaccesslog EXCHANGE PARTITION pbf2009 WITH TABLE my_p_siteaccesslog_2008;

创建专门用于存放2009年数据的分区表：

01 create table my_p_siteaccesslog_2009

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 p200901 values less than ('2009-02-01'),

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

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

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

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

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

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

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

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

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

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

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

34 )

令人非常郁闷的是Oracle竟然不支持两个分区表之间的分区切换，一定要借助一个中间表，这个功能连SQL Server都有了啊。

于是创建一个专门用于切换的中间表：

01 create table my_p_siteaccesslog_exchange

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

下面就是毫无技术含量并且不断重复的就借助中间表将my_p_siteaccesslog中的2009年数据切换到中my_p_siteaccesslog_2009中的代码：

01 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200901 WITH TABLE my_p_siteaccesslog_exchange;

02 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200901 WITH TABLE my_p_siteaccesslog_exchange;

03

04 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200902 WITH TABLE my_p_siteaccesslog_exchange;

05 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200902 WITH TABLE my_p_siteaccesslog_exchange;

06

07 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200903 WITH TABLE my_p_siteaccesslog_exchange;

08 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200903 WITH TABLE my_p_siteaccesslog_exchange;

09

10 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200904 WITH TABLE my_p_siteaccesslog_exchange;

11 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200904 WITH TABLE my_p_siteaccesslog_exchange;

12

13 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200905 WITH TABLE my_p_siteaccesslog_exchange;

14 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200905 WITH TABLE my_p_siteaccesslog_exchange;

15

16 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200906 WITH TABLE my_p_siteaccesslog_exchange;

17 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200906 WITH TABLE my_p_siteaccesslog_exchange;

18

19 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200907 WITH TABLE my_p_siteaccesslog_exchange;

20 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200907 WITH TABLE my_p_siteaccesslog_exchange;

21

22 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200908 WITH TABLE my_p_siteaccesslog_exchange;

23 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200908 WITH TABLE my_p_siteaccesslog_exchange;

24

25 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200909 WITH TABLE my_p_siteaccesslog_exchange;

26 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200909 WITH TABLE my_p_siteaccesslog_exchange;

27

28 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200910 WITH TABLE my_p_siteaccesslog_exchange;

29 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200910 WITH TABLE my_p_siteaccesslog_exchange;

30

31 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200911 WITH TABLE my_p_siteaccesslog_exchange;

32 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200911 WITH TABLE my_p_siteaccesslog_exchange;

33

34 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p200912 WITH TABLE my_p_siteaccesslog_exchange;

35 ALTER TABLE my_p_siteaccesslog_2009 EXCHANGE PARTITION p200912 WITH TABLE my_p_siteaccesslog_exchange;

顺便创建存放2010年数据的分区表

01 create table my_p_siteaccesslog_2010

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 p201001 values less than ('2010-02-01'),

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

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

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

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

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

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

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

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

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

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

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

34 )

并将1月份的数据切换到表my_p_siteaccesslog_2010中：

1 ALTER TABLE my_p_siteaccesslog;;;EXCHANGE PARTITION p201001 WITH TABLE my_p_siteaccesslog_exchange;

2 ALTER TABLE my_p_siteaccesslog_2010 EXCHANGE PARTITION p201001 WITH TABLE my_p_siteaccesslog_exchange;

经过一轮折腾之后，我们看看现各个表的情况：

01 test$ora8i@4.20 SQL> l

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

03 test$ora8i@4.20 SQL> /

04

05 SEGMENT_NAME;;MB

06 ---------------------------------------- ----------

07 P_SITEACCESSLOG 536

08 MY_P_SITEACCESSLOG_2009 435

09 MY_P_SITEACCESSLOG_2008; 51

10 MY_P_SITEACCESSLOG_2010;;45.6875

11 MY_P_SITEACCESSLOG; 35.1875

12 MY_P_SITEACCESSLOG_EXCHANGE.0625

表MY_P_SITEACCESSLOG以后将会改名为P_SITEACCESSLOG正式上线使用，其中仅保留1~2个月的数据用于查询，当需要查询旧数据的时候，应用可以不动，然后将旧的数据切换到MY_P_SITEACCESSLOG（也就是以后的P_SITEACCESSLOG）中就可以使用了。

分区表的切换产生的redo非常少，主要都是用于数据字典，因此系统的灵活性变得更高了。