当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。
(1)范围分区(range)——我们这篇博文的内容;
(2)哈希分区(hash);
(3)列表分区(list);
(4)范围-哈希复合分区(range-hash);
(5)范围-列表复合分区(range-list)。
Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中。
如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值。
在按时间分区时, 如果某些记录暂无法预测范围,可以创建 maxvalue 分区,所有不在指定范围内的记录都会被存储到 maxvalue 所在分区中。
numtodsinterval(<x>,<c>)
,其中x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 (day
,hour
,minute
,second
)
select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
对于分区索引,需要区分创建的是全局索引(global index)还是本地索引(local index)。
即可以分区,也可以不分区。即可以建range 分区,也可以建hash 分区, 即可建于分区表,又可创建于非分区表上,就是说,全局索引是完全独立的,因此它也需要我们更多的维护操作。
特点:
其分区形式与表的分区完全相同,依赖列相同,存储属性也相同。 对于本地索引,其索引分区的维护自动进行,就是说你add/drop/split/truncate 表的分区时, 本地索引会自动维护其索引分区。
特点:
创建本地索引示例
create index zt_wgxx_re_date_time on zt_wgxx(RECEIVE_DATE_TIME) local
创建好分区表后,分区字段的属性不能修改的,否则会报错ORA-14060: 不能更改表分区列的数据类型或长度
Oracle 11g中推出的Interval特性,是针对Range类型分区的一种功能拓展,Interval支持的Range分区键类型只有number
、date
、timestamp
三种类型。其他类型字段做alter table tablename set INTERVAL()时会报错ORA-14751: 间隔分区表的分区列的数据类型无效
当START_TIME的值为NULL时,然后向分区对象表插入数据,数据库不知道将该条记录插入到哪个分区,导致报错ORA-14300: 分区关键字映射到超出允许的最大分区数的分区
ORA-14752: 间隔表达式不是正确类型的常数,应该查看是否写错了,日和周的是 NUMTODSINTERVAL,年的和月的 是 NUMTOYMINTERVAL
ORA-14767: 无法使用现有上限指定此间隔,你时间写的不对,时间不可以超过指定时间,避免这种错误其实很简单,最简单的是利用28日作为分区上限
先说下,里面有两个地方需要注意,一个是分区字段名,这里用的是:WORKDATE
,需要有个分区字段;
另外一个是划分分区时间的 less than(XXXX)
,这个时间是比较特殊,超过这个时间会自动创建额外的分区,但暂时这么理解吧,我一般管它叫默认时间,也可以创建多个,但是我都只创建一个,后续会自增。
对于是月的,年的建议设置时间为每个月的01月1日。
create table TTT(WORKDATE date)
PARTITION BY RANGE (WORKDATE) INTERVAL (NUMTODSINTERVAL(1,'day'))
( partition P_1995 values less than (TO_DATE('1995-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')));
create table TTT(WORKDATE date)
PARTITION BY RANGE (WORKDATE) INTERVAL (NUMTOYMINTERVAL(1,'month'))
( partition P_1995 values less than (TO_DATE('1995-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')));
create table TTT(WORKDATE date)
PARTITION BY RANGE (WORKDATE) INTERVAL (NUMTOYMINTERVAL(1,'year'))
( partition P_1995 values less than (TO_DATE('1995-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')));
DATE类型:建立的分区表说明2003-1-1之前的数据放入P01分区中,之后的数据每天一个分区
CREATE TABLE tablename (hid number,hdate date)
PARTITION BY RANGE(hdate) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
( PARTITION p01 VALUES LESS THAN (to_date('2003-1-1','yyyy-mm-dd')));
number类型:建立的分区表说明100之前的数据放入P01分区中,之后的数据每100放入一个新一个分区,比如102放入一个分区p02,203放入一个分区p03 如果只有100以内的数据,还没有大于100的数据,直接插入1111则一样自动建立一个分区,USER_TAB_PARTITIONS.HIGHVALUE显示为1200,再插入103一样会自动建立一个分区,USER_TAB_PARTITIONS.HIGHVALUE显示为200
CREATE TABLE tablename (hid number,hdate date)
PARTITION BY RANGE(hid) INTERVAL (100)
( PARTITION p01 VALUES LESS THAN (100));
--修改每100为单位分区则可以手工修改如下
alter table tablename set INTERVAL (100);
--修改每天分区则可以手工修改如下
alter table tablename set INTERVAL (NUMTODSINTERVAL(1, 'DAY'));
--修改为每周分区则如下
alter table tablename set INTERVAL(numtodsinterval(7,'day'));
--修改为每月分区则如下
alter table tablename set INTERVAL(numtoyminterval(1,'MONTH'));
--修改为每年分区则如下
alter table tablename set INTERVAL(numtoyminterval(1,'YERA'));
--numtodsinterval(<x>,<c>) ,x是一个数字,c是一个字符串,c只能是'DAY'、'HOUR'、'MINUTE'、'SECOND'
--numtoyminterval(<x>,<c>) ,x是一个数字,c是一个字符串,c只能是'YEAR'、'MONTH'
create table ZT_WGXX
(
ID VARCHAR2(50) not null
constraint PK_ZT_WGXX
primary key,
PROJECT_ID VARCHAR2(50),
CAR_ID VARCHAR2(50),
TRAFFICCOMPANY_ID VARCHAR2(50),
TYPE VARCHAR2(50),
WGNR VARCHAR2(2000),
CREATE_DATE DATE not null,
CREATE_BY VARCHAR2(50) not null,
UPDATE_DATE DATE,
UPDATE_BY VARCHAR2(50),
REMARKS VARCHAR2(250),
DEL_FLAG NUMBER(4),
MAP_X VARCHAR2(50),
MAP_Y VARCHAR2(50),
SYSTEM_FLAG VARCHAR2(50),
ARTIFICIAL_FLAG VARCHAR2(50),
RECEIVE_DATE_TIME DATE,
RECEIVE_DATE VARCHAR2(250),
RECEIVE_DATE_END VARCHAR2(250),
XWFSDD_NAME VARCHAR2(500)
) PARTITION BY RANGE (RECEIVE_DATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(PARTITION ZT_WGXX_pt01 VALUES LESS THAN (to_date('2000-1-1', 'yyyy-mm-dd')));
说明:
INTERVAL 分区,按天分区,需要用到函数NUMTODSINTERVAL