论文部分内容阅读
摘 要：利用oracle分区和压缩技术对日益膨胀的数据库空间进行优化，从而提高查询效率。本文介绍了分区的基本原理、原则和方法以及数据库压缩技术。
关键词：oracle；优化；分区；压缩
中图分类号：TP311.13
随着电信业务发展，用户资料和业务量数据暴增，尤其是生产运营数据集中到省之后，分公司每日要将生产数据按全量或增量的方式同步到本地数据库，每月将各类用户资料和量收清单在本地做好备份，创建和完善本地数据集市。在部分工作时段，数据表访问频繁，数据库负荷超重，严重影响数据处理和查询的效率。根据实际情况，主要通过数据表分区和压缩来优化oracle数据库。
1 表分区
1.1 分区的基本原理和优点
表分区，是把表里的数据在物理上分别存放到不同磁盘，查询数据时，只需要访问一个较小片段，而无需扫描整个表。进行分区后，逻辑上仍然是一张完整的表，对于数据库操作员来讲，在SQL DML命令上，和访问普通表没有区别。但对于数据库，由于分别从不同磁盘读取数据，可以有效的降低磁盘的I/O冲突，减少不必要的数据流量，从而提高了查询性能。当某个分区出现故障时，只需要修复该分区的数据，其他分区的数据不会受影响。分区也增加了删除和备份的灵活性，可以独立删除或备份某一分区的数据，大大缩短操作时间。
1.2 分区表确定的主要原则
（1）大小超过2G或者记录数超过千万；（2）对于经常需要维护，例如按时间批量删除的表，对这类表进行分区，删除大量数据时可以有效减少系统开销；（3）表中大部分数据都是只读数据，通过分区可以将只读数据存储在只读表空间里，这对于数据库的备份也是非常有益的；（4）基于表的大部分查询应用，只访问表中少量的数据，可以充分利用分区来缩小数据查询范围。
1.3 分区的主要方法
分区的主要方法有：（1）范围分区；（2）列表分区；（3）散列分区；（4）组合范围散列分区。本文主要对工作中使用较多的范围分区和列表分区做介绍。
（1）范围分区：就是对数据表中某个字段值的范围进行分区，根据这个值的范围，决定将该数据存储在哪个分区上。这种分区方式最常见，而且多数是采用日期字段来分区。例如业务受理订单表，存放着若干年的受理历史订单，而且还不断增加新纪录，可以按照业务受理时间来分。按照查询需求，查询往年数据相对较少，可以一年一个分区，当年的数据访问频率较高，可以按月分区。创建分区表语句：
create table tb_ba（id number（12），…act_date date）
partition by range（act_date）
（partition tb_b_2013 values less than to_date（'20140101'，'yyyymmdd'）） tablespace tbs2013，
partition tb_b_201401 values less than （to_date （'20140201’，’yyyymmdd’））
tablespace tbs201401
…
partition tb_ba_other values less than （maxvalue） tablespace tb_ba_other）；
（2）列表分区：这种分区的特点是某一列的值只有几个。例如服务资料表，产品类型基本是固定数量的，日常查询频率较高的只有少数几种。可以对移动、宽带、专线各做一个分区，其余产品做一个分区。
create table tb_c_serv（id number，prod_id number）
partition by list （id）
（partition YD values （3205） tablespace tb_cm1，--移动
partition KD values （47） tablespace tb_cm2，--宽带
…
partition QT values （default） tablespace tb_cm6）；
（3）散列分区：这类分区是在列值上使用散列算法，来确定行放入哪个分区，这种分区分区大小比较一致，数据量均匀。如果列值没有适合的条件，可以考虑这种分区。
（4）组合范围散列分区：这类分区是结合了范围分区和列表分区，先按某一列做范围分区，然后再按某列做列表分区，相当于分区内再做子分区。
1.4 分区维护
删除分区：alter table tb_ba drop partition tb_ba_other；
增加分区：界限应高于现有的最后一个分区界限。
alter table tb_ba add partition tb_b_yyyymm values less then 日期）表空间；
合并分区：alter table tb_ba merge partition tb_b_2012，tb_b_2013 into tb_b_2013；
清空分区数据：alter table tb_ba truncate partition tb_b_2012
分区表及分区信息分别存放在user_part_tables和user_tab_partitions这两个数据字典。
2 数据库压缩
2.1 数据库压缩的优点
数据表压缩后，首先节约了磁盘空间，查询时读取的是压缩后的数据，查询的数据块会比未压缩之前大大减少。减少磁盘I/O节约下来的时间要比在内存中解压缩数据所需的时间多，所以能提高查询操作的性能。同时，由于压缩和解压都在Oracle内部进行，外部网络只传输压缩后的数据块，从而减少了网络负载。Oracle 11G允许在所有的DML操作包括增、删、改期间进行，所有应用都可以启用压缩，并使用智能算法保证写操作时负载最小，对于增、删、改操作，性能可能会略有下降，但从获得的好处来看是值得的。通信行业每月量、收这类海量数据清单，都会占用大量空间，导致磁盘空间不时爆满，这类清单基本都是只读的，压缩势在必行。
2.2 常用压缩命令
压缩可以在创建表空间、表、索引时设置，通常是在常规语法后加compress，也可以对现有表空间、表、索引进行压缩。
create table T（id number）compress；--创建压缩表
alter table T move compress；--将现有表T改为压缩表
alter table T move nocompress；--取消表T压缩
alter table T modify partition pmax compress；--压缩表分区
alter index IDX1 rebuild compress–压缩索引
值得说明的是，有时由于sql语句不当，导致出现笛卡尔积，临时表空间也会异常暴涨，当临时表空间不足时，数据库运算速度就会异常的慢，当临时表空间迅速扩展到最大空间时，一般就不会自动清理了，这时可以执行命令缩减临时表空间数据文件的尺寸。alter database tempfile'/u01/oradata/site/temp01.dbf' resize 10240M；当对表分区内部数据进行大量UPDATE或delete操作后，最好也要扫描一下分区内部空块，进行磁盘压缩。
3 结束语
Oracle数据库性能优化是一个系统工程，涉及的方面很多，需要开发和维护人员在大量的实践工作中，不断总结和积累经验，根据实际情况不断调整，合理利用数据库空间，使得数据库性能优越，跑得更快。
参考文献：
[1]林树泽.Oracle数据库管理之道[M].北京：清华大学出版社，2012.
[2]谭怀远.让Oracle跑得更快2-基于海量数据的数据库设计与优化[M]，北京：电子工业出版社，2011.
作者简介：董晓婷（1979.01-），女，山东寿光人，数据分析师，中级工程师，本科，研究方向：数据统计、分析、挖掘。
作者单位：中国电信股份有限公司汕头分公司，广东汕头 515041
关键词：oracle；优化；分区；压缩
中图分类号：TP311.13
随着电信业务发展，用户资料和业务量数据暴增，尤其是生产运营数据集中到省之后，分公司每日要将生产数据按全量或增量的方式同步到本地数据库，每月将各类用户资料和量收清单在本地做好备份，创建和完善本地数据集市。在部分工作时段，数据表访问频繁，数据库负荷超重，严重影响数据处理和查询的效率。根据实际情况，主要通过数据表分区和压缩来优化oracle数据库。
1 表分区
1.1 分区的基本原理和优点
表分区，是把表里的数据在物理上分别存放到不同磁盘，查询数据时，只需要访问一个较小片段，而无需扫描整个表。进行分区后，逻辑上仍然是一张完整的表，对于数据库操作员来讲，在SQL DML命令上，和访问普通表没有区别。但对于数据库，由于分别从不同磁盘读取数据，可以有效的降低磁盘的I/O冲突，减少不必要的数据流量，从而提高了查询性能。当某个分区出现故障时，只需要修复该分区的数据，其他分区的数据不会受影响。分区也增加了删除和备份的灵活性，可以独立删除或备份某一分区的数据，大大缩短操作时间。
1.2 分区表确定的主要原则
（1）大小超过2G或者记录数超过千万；（2）对于经常需要维护，例如按时间批量删除的表，对这类表进行分区，删除大量数据时可以有效减少系统开销；（3）表中大部分数据都是只读数据，通过分区可以将只读数据存储在只读表空间里，这对于数据库的备份也是非常有益的；（4）基于表的大部分查询应用，只访问表中少量的数据，可以充分利用分区来缩小数据查询范围。
1.3 分区的主要方法
分区的主要方法有：（1）范围分区；（2）列表分区；（3）散列分区；（4）组合范围散列分区。本文主要对工作中使用较多的范围分区和列表分区做介绍。
（1）范围分区：就是对数据表中某个字段值的范围进行分区，根据这个值的范围，决定将该数据存储在哪个分区上。这种分区方式最常见，而且多数是采用日期字段来分区。例如业务受理订单表，存放着若干年的受理历史订单，而且还不断增加新纪录，可以按照业务受理时间来分。按照查询需求，查询往年数据相对较少，可以一年一个分区，当年的数据访问频率较高，可以按月分区。创建分区表语句：
create table tb_ba（id number（12），…act_date date）
partition by range（act_date）
（partition tb_b_2013 values less than to_date（'20140101'，'yyyymmdd'）） tablespace tbs2013，
partition tb_b_201401 values less than （to_date （'20140201’，’yyyymmdd’））
tablespace tbs201401
…
partition tb_ba_other values less than （maxvalue） tablespace tb_ba_other）；
（2）列表分区：这种分区的特点是某一列的值只有几个。例如服务资料表，产品类型基本是固定数量的，日常查询频率较高的只有少数几种。可以对移动、宽带、专线各做一个分区，其余产品做一个分区。
create table tb_c_serv（id number，prod_id number）
partition by list （id）
（partition YD values （3205） tablespace tb_cm1，--移动
partition KD values （47） tablespace tb_cm2，--宽带
…
partition QT values （default） tablespace tb_cm6）；
（3）散列分区：这类分区是在列值上使用散列算法，来确定行放入哪个分区，这种分区分区大小比较一致，数据量均匀。如果列值没有适合的条件，可以考虑这种分区。
（4）组合范围散列分区：这类分区是结合了范围分区和列表分区，先按某一列做范围分区，然后再按某列做列表分区，相当于分区内再做子分区。
1.4 分区维护
删除分区：alter table tb_ba drop partition tb_ba_other；
增加分区：界限应高于现有的最后一个分区界限。
alter table tb_ba add partition tb_b_yyyymm values less then 日期）表空间；
合并分区：alter table tb_ba merge partition tb_b_2012，tb_b_2013 into tb_b_2013；
清空分区数据：alter table tb_ba truncate partition tb_b_2012
分区表及分区信息分别存放在user_part_tables和user_tab_partitions这两个数据字典。
2 数据库压缩
2.1 数据库压缩的优点
数据表压缩后，首先节约了磁盘空间，查询时读取的是压缩后的数据，查询的数据块会比未压缩之前大大减少。减少磁盘I/O节约下来的时间要比在内存中解压缩数据所需的时间多，所以能提高查询操作的性能。同时，由于压缩和解压都在Oracle内部进行，外部网络只传输压缩后的数据块，从而减少了网络负载。Oracle 11G允许在所有的DML操作包括增、删、改期间进行，所有应用都可以启用压缩，并使用智能算法保证写操作时负载最小，对于增、删、改操作，性能可能会略有下降，但从获得的好处来看是值得的。通信行业每月量、收这类海量数据清单，都会占用大量空间，导致磁盘空间不时爆满，这类清单基本都是只读的，压缩势在必行。
2.2 常用压缩命令
压缩可以在创建表空间、表、索引时设置，通常是在常规语法后加compress，也可以对现有表空间、表、索引进行压缩。
create table T（id number）compress；--创建压缩表
alter table T move compress；--将现有表T改为压缩表
alter table T move nocompress；--取消表T压缩
alter table T modify partition pmax compress；--压缩表分区
alter index IDX1 rebuild compress–压缩索引
值得说明的是，有时由于sql语句不当，导致出现笛卡尔积，临时表空间也会异常暴涨，当临时表空间不足时，数据库运算速度就会异常的慢，当临时表空间迅速扩展到最大空间时，一般就不会自动清理了，这时可以执行命令缩减临时表空间数据文件的尺寸。alter database tempfile'/u01/oradata/site/temp01.dbf' resize 10240M；当对表分区内部数据进行大量UPDATE或delete操作后，最好也要扫描一下分区内部空块，进行磁盘压缩。
3 结束语
Oracle数据库性能优化是一个系统工程，涉及的方面很多，需要开发和维护人员在大量的实践工作中，不断总结和积累经验，根据实际情况不断调整，合理利用数据库空间，使得数据库性能优越，跑得更快。
参考文献：
[1]林树泽.Oracle数据库管理之道[M].北京：清华大学出版社，2012.
[2]谭怀远.让Oracle跑得更快2-基于海量数据的数据库设计与优化[M]，北京：电子工业出版社，2011.
作者简介：董晓婷（1979.01-），女，山东寿光人，数据分析师，中级工程师，本科，研究方向：数据统计、分析、挖掘。
作者单位：中国电信股份有限公司汕头分公司，广东汕头 515041