[原]使用Oracle Partition Table对日志表进行分区一例

最新推荐文章于 2025-08-15 10:38:51 发布

weixin_34075268

最新推荐文章于 2025-08-15 10:38:51 发布

阅读量198

点赞数

文章标签：数据库

通过按时间分区并淘汰旧数据，优化日志型表存储与查询效率，减少资源占用，提升系统灵活性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

某个系统中有个日志型的表，现在的大小大概超过500MB，与该表相关的语句的执行计划都是对这个表进行全表扫描。

下面是该表的定义：


create table p_siteaccesslog
(
	logID			varchar(40) not null,
	infoType		varchar(20) default 'site',
	siteId			varchar(30),
	columnId		varchar(30),
	infoId			varchar(30),
	url				varchar(300),
	location		varchar(80),
	action			varchar(20),
	accessDate		varchar(10),     
	accessTime		varchar(8),
	sessionID		varchar(90),
	userName		varchar(30),
	userBrowser		varchar(50),
	userOS			varchar(30),
	userIp			varchar(30),
	localization	varchar(100),
	constraint PK_p_siteaccesslog primary key (logID) 
)

该表有一个主键，为每条日志分配一个主键，感觉上一百年都不会用上这个主键来查一次数据。所以在新的分区表上将其去掉。


test$ora8i@4.20 SQL> l
  1* select segment_name,sum(bytes/1048576) MB from user_extents group by segment_name order by 2 desc
test$ora8i@4.20 SQL> /

SEGMENT_NAME                                     MB
---------------------------------------- ----------
P_SITEACCESSLOG                                 536

由于是日志型的表，第一感觉就是按照时间分区，并淘汰（切换）旧的数据。

于是沿着这个思路，按照每月一个分区的策略构建分区表：


create table my_p_siteaccesslog
(
	logID			varchar(40) not null,
	infoType		varchar(20) default 'site',
	siteId			varchar(30),
	columnId		varchar(30),
	infoId			varchar(30),
	url				varchar(300),
	location		varchar(80),
	action			varchar(20),
	accessDate		varchar(10), 
	accessTime		varchar(8),
	sessionID		varchar(90),
	userName		varchar(30),
	userBrowser		varchar(50),
	userOS			varchar(30),
	userIp			varchar(30),
	localization	varchar(100)
)
partition by range (accessDate) 
(
	partition pbf2009 values less than ('2009-01-01'),
	partition p200901 values less than ('2009-02-01'),
	partition p200902 values less than ('2009-03-01'),
	partition p200903 values less than ('2009-04-01'),
	partition p200904 values less than ('2009-05-01'),
	partition p200905 values less than ('2009-06-01'),
	partition p200906 values less than ('2009-07-01'),
	partition p200907 values less than ('2009-08-01'),
	partition p200908 values less than ('2009-09-01'),
	partition p200909 values less than ('2009-10-01'),
	partition p200910 values less than ('2009-11-01'),
	partition p200911 values less than ('2009-12-01'),
	partition p200912 values less than ('2010-01-01'),
	partition p201001 values less than ('2010-02-01'),
	partition p201002 values less than ('2010-03-01'),
	partition p201003 values less than ('2010-04-01'),
	partition p201004 values less than ('2010-05-01'),
	partition p201005 values less than ('2010-06-01'),
	partition p201006 values less than ('2010-07-01'),
	partition p201007 values less than ('2010-08-01'),
	partition p201008 values less than ('2010-09-01'),
	partition p201009 values less than ('2010-10-01'),
	partition p201010 values less than ('2010-11-01'),
	partition p201011 values less than ('2010-12-01'),
	partition p201012 values less than ('2011-01-01')
)

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

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


insert into my_p_siteaccesslog select * from P_SITEACCESSLOG ; 
commit;

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

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


create table my_p_siteaccesslog_2008
(
	logID			varchar(40) not null,
	infoType		varchar(20) default 'site',
	siteId			varchar(30),
	columnId		varchar(30),
	infoId			varchar(30),
	url				varchar(300),
	location		varchar(80),
	action			varchar(20),
	accessDate		varchar(10), 
	accessTime		varchar(8),
	sessionID		varchar(90),
	userName		varchar(30),
	userBrowser		varchar(50),
	userOS			varchar(30),
	userIp			varchar(30),
	localization	varchar(100)
);

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


ALTER TABLE my_p_siteaccesslog EXCHANGE PARTITION pbf2009 WITH TABLE my_p_siteaccesslog_2008;

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


create table my_p_siteaccesslog_2009
(
	logID			varchar(40) not null,
	infoType		varchar(20) default 'site',
	siteId			varchar(30),
	columnId		varchar(30),
	infoId			varchar(30),
	url				varchar(300),
	location		varchar(80),
	action			varchar(20),
	accessDate		varchar(10), 
	accessTime		varchar(8),
	sessionID		varchar(90),
	userName		varchar(30),
	userBrowser		varchar(50),
	userOS			varchar(30),
	userIp			varchar(30),
	localization	varchar(100)
)
partition by range (accessdate)
(
	partition p200901 values less than ('2009-02-01'),
	partition p200902 values less than ('2009-03-01'),
	partition p200903 values less than ('2009-04-01'),
	partition p200904 values less than ('2009-05-01'),
	partition p200905 values less than ('2009-06-01'),
	partition p200906 values less than ('2009-07-01'),
	partition p200907 values less than ('2009-08-01'),
	partition p200908 values less than ('2009-09-01'),
	partition p200909 values less than ('2009-10-01'),
	partition p200910 values less than ('2009-11-01'),
	partition p200911 values less than ('2009-12-01'),
	partition p200912 values less than ('2010-01-01')
)

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

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


create table my_p_siteaccesslog_exchange
(
	logID			varchar(40) not null,
	infoType		varchar(20) default 'site',
	siteId			varchar(30),
	columnId		varchar(30),
	infoId			varchar(30),
	url				varchar(300),
	location		varchar(80),
	action			varchar(20),
	accessDate		varchar(10), 
	accessTime		varchar(8),
	sessionID		varchar(90),
	userName		varchar(30),
	userBrowser		varchar(50),
	userOS			varchar(30),
	userIp			varchar(30),
	localization	varchar(100)
);

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


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

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

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

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

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

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

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

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

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

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

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

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

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


create table my_p_siteaccesslog_2010
(
	logID			varchar(40) not null,
	infoType		varchar(20) default 'site',
	siteId			varchar(30),
	columnId		varchar(30),
	infoId			varchar(30),
	url				varchar(300),
	location		varchar(80),
	action			varchar(20),
	accessDate		varchar(10), 
	accessTime		varchar(8),
	sessionID		varchar(90),
	userName		varchar(30),
	userBrowser		varchar(50),
	userOS			varchar(30),
	userIp			varchar(30),
	localization	varchar(100)
)
partition by range (accessdate)
(
	partition p201001 values less than ('2010-02-01'),
	partition p201002 values less than ('2010-03-01'),
	partition p201003 values less than ('2010-04-01'),
	partition p201004 values less than ('2010-05-01'),
	partition p201005 values less than ('2010-06-01'),
	partition p201006 values less than ('2010-07-01'),
	partition p201007 values less than ('2010-08-01'),
	partition p201008 values less than ('2010-09-01'),
	partition p201009 values less than ('2010-10-01'),
	partition p201010 values less than ('2010-11-01'),
	partition p201011 values less than ('2010-12-01'),
	partition p201012 values less than ('2011-01-01')
)

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


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

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


test$ora8i@4.20 SQL> l
  1* select segment_name,sum(bytes/1048576) MB from user_extents group by segment_name order by 2 desc
test$ora8i@4.20 SQL> /

SEGMENT_NAME                                     MB
---------------------------------------- ----------
P_SITEACCESSLOG                                 536
MY_P_SITEACCESSLOG_2009                         435
MY_P_SITEACCESSLOG_2008                          51
MY_P_SITEACCESSLOG_2010                     45.6875
MY_P_SITEACCESSLOG                          35.1875
MY_P_SITEACCESSLOG_EXCHANGE                   .0625

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

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