文章詳情頁

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

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

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

下面是該表的定義：

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)建好之后，就導數(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ù)含量并且不斷重復的就借助中間表將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ù)用于查詢，當需要查詢舊數(shù)據(jù)的時候，應用可以不動，然后將舊的數(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學習記錄之KEY分區(qū)引發(fā)的血案3. 在DB2上建立數(shù)據(jù)庫分區(qū)4. SQL server 2005的表分區(qū)5. 詳解MySQL分區(qū)表6. sql server 2005中的表分區(qū)7. 講解Oracle移動數(shù)據(jù)文件到新分區(qū)的過程8. MySQL分區(qū)的優(yōu)點9. 正確認識DB2數(shù)據(jù)庫分區(qū)兼容性和并置概念10. Oracle中使用自治事務保存日志表條目

排行榜

					
					Mysql入門系列：建立MYSQL客戶機程序的一般過程
mysql的like模式
詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化
Mysql入門系列：MYSQL圖像數(shù)據(jù)的處理
SQL Server事務日志意外增大的處理方法
MySql導出后再導入數(shù)據(jù)時出錯問題
SQLite3中自增主鍵相關(guān)知識總結(jié)
Sql Server2005學習日記(01)
盤點SqlServer 分頁方式和拉姆達表達式分頁
經(jīng)驗分享：Informix和Oracle存儲過程的異同
AIX環(huán)境下實現(xiàn)DB2向Windows 2000遷移
				

熱門標簽

国产成人精品亚洲777人妖,欧美日韩精品一区视频,最新亚洲国产,国产乱码精品一区二区亚洲

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