Oracle分区表用法

分区表的用处

我们oracle中的表数据最终是保存到磁盘文件中.默认情况下表中的数据都是放到一起的,但表中的数据一多,对表的操作就会变得较慢.而分区表是把一个表中的数据保存到不同地方去.这样会带来如下好处.

1.减小表中数据损坏的可能性,数据分散到不同地方了嘛.另外可以单独对不同的分区做备份与恢复操作.

2.提高IO性能,表分我后可能就保存到不同的磁盘上去了.这样可以并行的读取表中数据.

 

当表中数据大于2G时建议使用分区表.

 

分区表类型

我们可能会想到既然要把一个表中数据分散到不同地方,那根据个啥标准来分呢.总共有这么4大类标准.

Oracle分区表分为四类：范围分区表；列表分区表；哈希分区表；组合分区表

 

range范围分区表

就是根据某一列的值来做判断,把不同的行保存到不同的地方

假如创建一个分区表,以列id值来做判断依据分区

createtable part_tb(idnumber, info varchar2(500))partitionbyrange(id)

(

partition part1 valueslessthan(100)tablespaceusers,

partition part2 valueslessthan(200)tablespacesystem,

partition part3 valueslessthan(maxvalue)tablespacesystem

)

 

往表中插入如下两行数据

insertinto part_tb values(38,'vlaues is 38');  --存入分区part1

insertinto part_tb values(520,'vlaues is 520'); --存入分区part2

 

查找数据

我们可以把分区表当作一般表来操作.直接查询select * from part_tb;--返回两行数据

但还有一种特殊操作,直接指定查找哪个分区中的信息

select *from part_tb partition(part1); --此时只返回id小于100的数据

 

分区的修改

(注:这时的分区修改是针对所有类型的分区表有效)

 

添加分区

假如你创建表之后又突然想在原有的表上增加分区可以这样

altertable part_tb addpartition part4 valueslessthan(300);

不过你执行下发会会报错,会提示

ORA-14074: partition bound must collate higher than that of the last partition

因为之前创建表时你用到了lessthan(maxvalue).如果没有这个东东就能成功添加

 

那现在出现这种情况时怎么解决呢,有两种方法,一是先删除分区part3于添加,另一个是split分区.

 

删除分区

altertable part_tb droppartition PART3;

 

拆分分区 :split

合并分区: merge

 

list列表分区表

列表分区跟范围分区也类似,只不过范围分区一般适用于针对一些数值范围,而列表分区一般用于判断某些字符串.

假如创建一个表,通过判断城市信息来分区

createtable user_info(idnumber, user_name varchar2(500), city varchar2(100))partitionbylist(city)

(

partition part1values('BeiJing')tablespaceusers,

partition part2values('ChangSha')tablespacesystem,

partition part3values(default)tablespacesystem

)

 

插入数据

 

insertinto user_info values(1,'arwen','BeiJing');--part1

insertinto user_info values(1,'weiwen','ChangSha');-- part2

 

查找数据

select *from user_info partition(part2);

 

看到这里你是不想到如果一个超级大的表中有用户信息,如果是普通表要查找某一类用户的信息要等半天才有反应.弄成分区表是不是可以一下子就查出结果来了啊.

 

hash哈希分区表

前面讲的范围分区表,列表分区表都是某一列具有确定的信息可以做为依据.但假如某一列的信息是杂乱无章的,你想让随机分区下咋整呢.这就可以用哈希分区.

假如创建如下哈希分区表

createtable order_info(order_numbernumber, info varchar2(100))partitionbyhash(order_number)

(

partition part1tablespaceusers,

partition part2tablespacesystem

)

 

插入数据

insertinto order_info values(12,'buy car');

insertinto order_info values(888,'buy house');

 

查找数据

select *from order_info partition(part1);

 

由于数据是随机存取的所以你不能保证哪些行存到哪个分区.

 

 

组合分区表

综合上面三种分区方法,大的分区下面又有小分区.

在Oracle 10g中有如下两种组合方法

范围－哈希复合分区（range-hash)

范围－列表复合分区（range-list）

 

在Oracle 11g中

又增加了range-range,list-range,

list-list,list-hash，并且 11g里面还支持Interval分区和虚拟列分区

 

下面举个简单的例子看下范围-列表复合分区(range-list).

 

createtable compound(arrange_idnumber,list_infovarchar2(500))

partitionbyrange(arrange_id)subpartitionbylist(list_info)

(

partition part1 valueslessthan(100)

(subpartition part11 values('car'),

subpartition part12 values(default)

),

 

partition part2 valueslessthan(200)

);

 

插入数据

 

insertinto compound values(50,'car');

insertinto compound values(60,'books');

 

查找数据

 

select *from compound partition(part1)

select *from compound subpartition(part11)