数据库优化之高效率调优oracle亿级别表.pdfVIP

数据库优化之高效率调优oracle亿级别表

2017年在省公司做一个项目，涉及到一个亿级别的大表操作，过

程中遇到了很多坑，走过后记录如下，方便今后回忆。

Oracle数据库是一种事务性数据库，对删除、修改、新增操作会

产生undo和redo两种日志，当一次提交的数据量过大时，数据库会

产生大量的日志写文件IO操作，导致数据库操作性能下降，尤其是对

一张记录过亿的表格进行操作时需要注意以下事项：

1、操作大表必须知道表有多大

selectsum(bytes)/(1024*1024)asasfrom

user_segmentswheresegment_name=upper(ipdetail);

通过这条命令可以在大表导入及使用时实时查询进度和该表所占

的实际存储空间。

知道一个用户下的所有表各占多大存储空间

selectselectsum(bytes)/(1024*1024)asasfrom

user_segmentswheresegment_name=upper(||tname||)

fromtab;

2、大于1000w记录的表格最好分区或分表处理，如果分表涉及

的业务调整量太大，可以先考虑分区保存

比如可以按照IP数字的范围大概将每2000万条记录放入一个分

区表空间中进行保存。

createtabletestipdetail(ipaddvarchar2(20),numip

number,ispvarchar2(255),countryvarchar2(255),province

varchar2(255),cityvarchar2(255),usedvarchar2(255),create_date

datedefaultsysdate,creater_idnumber,stsnumber)

partitionbyrange(numip)

(partitionip1valueslessthan(1000000000),

partitionip2valueslessthan(1930000000),

partitionip3valueslessthan(2285000000),

partitionip4valueslessthan(3080000000),

partitionipmaxvalueslessthan(3774873599)

)

通过分区处理后，9G的表数据基本均匀的存储在5个表空间中，

经过测试大表分区后比表保存在单一分区上的insert效率高40%，但

是update效率没有显著提升。

3、尽量不要使用全表update操作

如果有对一个字段进行整体修改的计划，建议使用新建表格的方

式。

--新建一张和待修改表格一样表结构的新表

createtablet2asselect*fromipdetailwhere1=2

--修改表格的属性为不归档模式，实际效果不大

altertablet2nologging;

--已不记录日志的形式进行批量插入/*+APPEND*/

insert/*+APPEND*/intoT2select

ipadd,numip,isp,country,province,city,used,create_date,1,0from

ipdetail

--插入结束后一定要记得提交，否则查询会报错

Commit

4、大表update数据前先建索引

比如下面一条语