一、什么时候使用分区表?
1、表中大小超过2G;
2、表中有历史数据,新的数据被添加到新的分区中。
SQL查看表占用空间:
-- 查看表中占用内存大小,固定用法
select segment_name, sum(bytes) / 1024 / 1024 || 'M'
from dba_extents
where segment_name = '表名'
group by segment_name;
二、表分区的优点:
1、提高查询性能:对分区的对象查询自己所在的区域,提高检索速度;
2、增强可用性:如果表中某个分区出现故障,表中其他分区的数据仍可同;
3、易维护:如果表中某个分区出现故障,需要修复数据,只修改该分区即可;
3、均衡I/O:可以把不同的分区映射到磁盘以均衡I/O,改善系统性能。
三、表空间和表分区的概念:(术语要知道)
表空间:
是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表。
表分区:
当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,
只是将表中的数据在物理上存放到多个表空间(物理文件上),这昂再查询数据,不需要每次都扫描整张表。
四、Oracle提供的分区方法
注意:
① 对已存在的表是不能增加分区的,需要建立临时表,分区后再导入百万级(1592292) 数据还是比较快的 (28.469秒) ;
② Oracle 提供了在线重定义表的功能,可增加分区(使用需谨慎)
1、Range-范围分区:根据表中列值的范围将整个表分成不同的部分,如:按照时间进行范围分区;
1.1 范围分区:如年统计、月统计可采用此分区
例:可根据年月日任意分区
手动添加试验一下:出问题了?????
ORA-14400: 插入的分区关键字未映射到任何分区:
原因是时间表分区过期,对于SQL文件中的日期,大于分区表中range设定的日期 后的数据都无法导入
注意:
Range分区新插入的数据时间,不能超过分区设定的最后时间,如:不能超过上面截图圈中的时间。
2、List-列表分区:使用列表值将表划分成几部分;
2.2 列表分区:该分区的特点是某列的值只有几个,基于这样的特点采用列表分区;
3、Hash-哈希分区:使用哈希函数将表分成几部分;
3.1哈希分区:Hash分区是在列值上使用散列算法,将添加的数据放入哪个分区。列的值没有合适的条件,可以使用Hash分区。
-- 创建分区 表名是 a_test, 字段是:pk_id、people_create、state; hash方式创建
create table a_test (pk_id varchar2(50),people_create varchar2(50), state number) partition by hash(people_create) partitions 4;
例: plsql查看
常用语句:
-- 查询当前用户下的分区表
select * from user_tables where partitioned='YES'
-- 查看分区,不能在tables后加别名,要在分区后加别名,例:select * from tables partition(AA) tab;
select * from a_test partition(SYS_P25);
select * from a_test partition(SYS_P26);
select * from a_test partition(SYS_P27);
select * from a_test partition(SYS_P28);
-- 查看分区,嵌套查询加别名
select * from (select * from a_test partition(SYS_P25)) t where t.pk_id = '2007131443'
-- 删除分区, 注意该分区中的数据也会被删除
delete from a_test partition(SYS_P24);
-- 修改现有的分区名
alter table a_test rename partition SYS_P25 to p25;
-- 显示当前用户所有分区表的信息:user_part_tables 固定写法
select * from user_part_tables;
-- 显示当前用户所有分区表的详细分区信息
select * from user_tab_partitions;
-- 增加分区
alter table a_test add partition par_add1;
注意:修改数据可能会遇到:ORA-14402: 更新分区关键字列将导致分区的更改
默认情况下,oracle的分区表对分区字段是不允许进行update操作,可以通过打开表的 row movement 属性对分区字段表的update操作
alter table a_test enable row movement;
提示:可以将主键作为hash分区的算法