文章詳情頁

使用Oracle Partition Table對日志表進(jìn)行分區(qū)

瀏覽：64日期：2023-11-11 16:53:29

某個系統(tǒng)中有個日志型的表，現(xiàn)在的大小大概超過500MB，與該表相關(guān)的語句的執(zhí)行計(jì)劃都是對這個表進(jìn)行全表掃描。

下面是該表的定義：

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 )

該表有一個主鍵，為每條日志分配一個主鍵，感覺上一百年都不會用上這個主鍵來查一次數(shù)據(jù)。所以在新的分區(qū)表上將其去掉。

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> /

5 SEGMENT_NAME;;MB

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

7 P_SITEACCESSLOG 536

由于是日志型的表，第一感覺就是按照時間分區(qū)，并淘汰（切換）舊的數(shù)據(jù)。

于是沿著這個思路，按照每月一個分區(qū)的策略構(gòu)建分區(qū)表：

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 )

由于數(shù)據(jù)庫是Oracle 10g，不是11g，無法支持interval分區(qū)，只能多創(chuàng)建幾個未來的分區(qū)了。

表創(chuàng)建好之后，就導(dǎo)數(shù)據(jù)流，由于該表不算太大，500多兆的插入還是可以接受的。

1 insert into my_p_siteaccesslog select * from P_SITEACCESSLOG ;

2 commit;

下一步使用分區(qū)切換技術(shù)，將舊的數(shù)據(jù)搬走（不是刪掉）

創(chuàng)建用于存放2008年數(shù)據(jù)大表

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

使用分區(qū)切換，將2008年的數(shù)據(jù)切換出來：

1 ALTER TABLE my_p_siteaccesslog EXCHANGE PARTITION pbf2009 WITH TABLE my_p_siteaccesslog_2008;

創(chuàng)建專門用于存放2009年數(shù)據(jù)的分區(qū)表：

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竟然不支持兩個分區(qū)表之間的分區(qū)切換，一定要借助一個中間表，這個功能連SQL Server都有了啊。

于是創(chuàng)建一個專門用于切換的中間表：

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

下面就是毫無技術(shù)含量并且不斷重復(fù)的就借助中間表將my_p_siteaccesslog中的2009年數(shù)據(jù)切換到中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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

順便創(chuàng)建存放2010年數(shù)據(jù)的分區(qū)表

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月份的數(shù)據(jù)切換到表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;

經(jīng)過一輪折騰之后，我們看看現(xiàn)各個表的情況：

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> /

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個月的數(shù)據(jù)用于查詢，當(dāng)需要查詢舊數(shù)據(jù)的時候，應(yīng)用可以不動，然后將舊的數(shù)據(jù)切換到MY_P_SITEACCESSLOG（也就是以后的P_SITEACCESSLOG）中就可以使用了。

分區(qū)表的切換產(chǎn)生的redo非常少，主要都是用于數(shù)據(jù)字典，因此系統(tǒng)的靈活性變得更高了。

上一條：SQL優(yōu)化過程中常見Oracle HINT的用法下一條：oracle常用經(jīng)典SQL查詢

相關(guān)文章：

1. Oracle?range時間范圍自動分區(qū)的創(chuàng)建方式2. MySQL分區(qū)表和分桶表的操作詳解3. 正確認(rèn)識DB2數(shù)據(jù)庫分區(qū)兼容性和并置概念4. Oracle表分區(qū)詳解5. MySQL最佳實(shí)踐之分區(qū)表基本類型6. 如何使用分區(qū)處理MySQL的億級數(shù)據(jù)優(yōu)化7. 教你在AIX上安裝IBM DB2 9版本的分區(qū)環(huán)境8. Mysql臨時表及分區(qū)表區(qū)別詳解9. ORACLE分區(qū)表轉(zhuǎn)換在線重定義DBMS_REDEFINITION10. sql server 2005中的表分區(qū)

排行榜

					
					淺談如何將Oracle導(dǎo)出到XML文件
Mybatis Limit實(shí)現(xiàn)分頁功能
探討SQL Server 2005.NET CLR編程
Oracle數(shù)據(jù)庫中的字符處理技巧總結(jié)
建立合理的索引提高SQL Server的性能
個人經(jīng)驗(yàn)總結(jié)：DB2數(shù)據(jù)庫技術(shù)關(guān)鍵領(lǐng)域列表
解決mybatis中的mapper命名問題
安裝SQL Server 2005時出現(xiàn)計(jì)數(shù)器錯誤
SQLite教程（二）：C/C++接口簡介
從問題入手幫你解決Oracle殺死死鎖進(jìn)程
SQL Server 2005性能測試實(shí)踐-CPU篇 編譯與重編譯
				

熱門標(biāo)簽

av一区二区在线观看_亚洲男人的天堂网站_日韩亚洲视频_在线成人免费_欧美日韩精品免费观看视频_久草视

使用Oracle Partition Table對日志表進(jìn)行分區(qū)