文章詳情頁

使用Oracle Partition Table對日志表進行分區

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

某個系統中有個日志型的表，現在的大小大概超過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> /

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;

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;

順便創建存放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> /

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非常少，主要都是用于數據字典，因此系統的靈活性變得更高了。

上一條：SQL優化過程中常見Oracle HINT的用法下一條：oracle常用經典SQL查詢

相關文章：

1. MySQL分區的優點2. Oracle數據庫中分區表的操作方法詳解3. 在DB2上建立數據庫分區4. SQL Server中合并用戶日志表的方法5. MySQL最佳實踐之分區表基本類型6. ORACLE分區表轉換在線重定義DBMS_REDEFINITION7. oracle分區表創建(自動按年、月、日分區)實戰記錄8. 如何使用分區處理MySQL的億級數據優化9. MySQL數據表分區策略及優缺點分析10. MySQL高級特性——數據表分區的概念及機制詳解

日本不卡不码高清免费观看,久久国产精品久久w女人spa,黄色aa久久,三上悠亚国产精品一区二区三区

使用Oracle Partition Table對日志表進行分區