某个系统中有个日志型的表，现在的大小大概超过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 )
