使用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非常少，主要都是用于數據字典，因此系統的靈活性變得更高了。
相關文章：
網公網安備