数据库SQL优化总结

1、DML脚本在插入数据超过1000条时，以批量插入的方式进行，否则执行会很慢。

示例：

begin
delete from 表名 where RULE_ID='R0067' and DRUG_NAME_TYPE='G';
insert into 表名(xxx) values (xxx);
insert into 表名(xxx) values (xxx);
insert into 表名(xxx) values (xxx);
end;
/
commit;

 

2、对大数据量的表添加字段并设置默认值。

反例：

alter table 表名 add is_deduction NUMBER(1) default 2;

正例：

alter table 表名 add is_deduction NUMBER(1) default 2 not null;

原理：

1. 这是oracle11g对add column语法的增强特性。
2. 在11g之前，如果对大数据表扩充字段且该字段有默认值，在没有使用not null时，执行add column语句后，数据库会立即执行不带where条件的update操作将默认值更新到原表的所有记录中，此时数据库会对表加上最高级别Exclusive （专用）模式锁来锁定全表，在执行DDL操作时需要更多的UNDO空间，这不仅影响对这张表的正常增删改操作，还有可能影响其他业务功能（因为UNDO空间有可能不够用）。
3. 从11g开始，针对大数据表给定默认值的add column语句开销大量UNDO空间的问题进行了优化，默认值将更新到字典表中（COL$），以后每次需要使用该字段时，默认值都是从数据字典中查询到的，此时由于操作的是字典表，这样就减少了新增字段时的DDL语句时间，也减少了UNDO空间开销。
4. 因此oracle建议对大数据表新增字段时，如果该字段需要设置默认值，请加上not null语句，可以避免因UNDO空间开销过大导致的性能问题。

 

转载于:https://my.oschina.net/u/3237413/blog/871524