Oracle数据经验之路就是这样走出来的

信息发布软件

	oracle数据库是一种大型数据库系统，一般应用于商业，政府部门，它的功能很强大，能够处理大批量的数据，在网络方面也用的非常多。Oracle数据库管理系统是一个以关系型和面向对象为中心管理数据的数据库管理软件系统，其在管理信息系统、企业数据处理、因特网及电子商务等领域有着非常广泛的应用。     1.having 子句的用法     having 子句对 group by 子句所确定的行组进行控制，having 子句条件中只允许涉及常量，聚组函数或group by 子句中的列。     2.外部联接"+"的用法     外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。若二者均不带？+?,则二者中无法匹配的均被返回。利用外部联接"+",可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢     select　a.empno from emp a where a.empno not in     （select empno from emp1 where job=?SALE?）；     倘若利用外部联接，改写命令如下：     select a.empno from emp a ,emp1 b     where a.empno=b.empno（+）     and b.empno is null     and b.job=?SALE?;     可以发现，运行速度明显提高。     3.删除表内重复记录的方法     可以利用这样的命令来删除表内重复记录：     delete from table_name a     where rowid< （select max（rowid） from table_name     where column1=a.column1 and column2=a.column2     and colum3=a.colum3 and …）；     不过，当表比较大（例如50万条以上）时，这个方法的效率之差令人无法忍受，需要另想办法（可参看拙文《电信计费中长途重复话单的技术处理》，《计算机与通信》，1999-07）。     4.set transaction 命令的用法     在执行大事务时，有时oracle会报出如下的错误：     ORA-01555:snapshot too old （rollback segment too small）     这说明oracle给此事务随机分配的回滚段太小了，这时可以为它指定一个足够大的回滚段，以确保这个事务的成功执行。例如     set transaction use rollback segment roll_abc;     delete from table_name where …     commit;     回滚段roll_abc被指定给这个delete事务，commit命令则在事务结束之后取消了回滚段的指定。     5.使用索引的注意事项     select,update,delete 语句中的子查询应当有规律地查找少于20%的表行。如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高。     索引可能产生碎片，因为记录从表中删除时，相应也从表的索引中删除。表释放的空间可以再用，而索引释放的空间却不能再用。频繁进行删除操作的被索引的表，应当阶段性地重建索引，以避免在索引中造成空间碎片，影响性能。在许可的条件下，也可以阶段性地truncate表，truncate命令删除表中所有记录，也删除索引碎片。     6.数据库重建应注意的问题     在利用import进行数据库重建过程中，有些视图可能会带来问题，因为结构输入的顺序可能造成视图的输入先于它低层次表的输入，这样建立视图就会失败。要解决这一问题，可采取分两步走的方法：首先输入结构，然后输入数据。命令举例如下 （uesrname:jfcl,password:hfjf,host stingra1,数据文件：expdata.dmp）：     imp jfcl/hfjf@ora1 file=empdata.dmp rows=N     imp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000     commit=Y ignore=Y     第一条命令输入所有数据库结构，但无记录。第二次输入结构和数据，64000字节提交一次。ignore=Y选项保证第二次输入既使对象存在的情况下也能成功。     以上六条小经验是从平时的工作中总结出来的，拿出来与大家分享，希望能帮到大家。 前提：在做insert数据之前，如果是非生产环境，请将表的索引和约束去掉，待insert完成后再建索引和约束。 　　insert into tab1 select * from tab2; commit; 　　这是最基础的insert语句，我们把tab2表中的数据insert到tab1表中。根据经验，千万级的数据可在1小时内完成。但是该方法产生的arch会非常快，需要关注归档的产生量，及时启动备份软件，避免arch目录撑爆。 　　alter table tab1 nologging;  insert /*+ append */ into tab1 select * from tab2;  commit; alter table tab1 logging; 　　该方法会使得产生arch大大减少，并且在一定程度上提高时间，根据经验，千万级的数据可在45分钟内完成。但是请注意，该方法适合单进程的串行方式，如果当有多个进程同时运行时，后发起的进程会有enqueue的等待。注意此方法千万不能dataguard上用，不过要是在database已经force logging那也是没有问题的。 　　insert into tab1 select /*+ parallel */ * from tab2; commit; 　　对于select之后的语句是全表扫描的情况，我们可以加parallel的hint来提高其并发，这里需要注意的是最大并发度受到初始化参数parallel_max_servers的限制，并发的进程可以通过v$px_session查看，或者ps -ef |grep ora_p查看。 　　alter session enable parallel dml;  insert /*+ parallel */ into tab1 select * from tab2; commit; 　　与方法2相反，并发的insert，尚未比较和方法2哪个效率更高(偶估计是方法2快)，有测试过的朋友欢迎补充。 　　insert into tab1 select * from tab2 partition (p1);  insert into tab1 select * from tab2 partition (p2);  insert into tab1 select * from tab2 partition (p3);  insert into tab1 select * from tab2 partition (p4); 　　对于分区表可以利用tab1进行多个进程的并发insert，分区越多，可以启动的进程越多。我曾经试过insert 2.6亿行记录的一个表，8个分区，8个进程，如果用方法2，单个进程完成可能要40分钟，但是由于是有8个分区8个进程，后发进程有enqueue，所以因此需要的时间为40分钟×8;但是如果用方法5，虽然单个进程需要110分钟，但是由于能够并发进程执行，所以总共需要的时间就约为110分钟了。 　　DECLARE TYPE dtarray IS TABLE OF VARCHAR2(20)  INDEX BY BINARY_INTEGER;  v_col1 dtarray; v_col2 dtarray; v_col3 dtarray;  BEGIN SELECT col1, col2, col3 BULK COLLECT INTO v_col1, v_col2, v_col3  FROM tab2;  FORALL i IN 1 .. v_col1.COUNT insert into tab1  WHERE tab1.col1 = v_col1;  END; 　　用批量绑定(bulk binding)的方式。当循环执行一个绑定变量的sql语句时候，在PL/SQL 和SQL引擎(engines)中，会发生大量的上下文切换(context switches)。使用bulk binding，能将数据批量的从plsql引擎传到sql引擎，从而减少上下文切换过程，提升效率。该方法比较适合于在线处理，不必停机。 　　sqlplus -s user/pwd< runlog.txt set copycommit 2;  set arraysize 5000;  copy from user/pwd@sid - to user/pwd@sid - insert tab1 using  select * from tab2; exit EOF 　　用copy的方法进行插入，注意此处insert没有into关键字。该方法的好处是可以设置copycommit和arrarysize来一起控制commit的频率，上面的方法是每10000行commit一次。 个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个 人理解应该分为物理的和逻辑的优化， 物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化的一些原则：　　1）Oracle的运行环境（网络，硬件等） 　　2）使用合适的优化器 　　3）合理配置oracle实例参数 　　4）建立合适的索引（减少IO） 　　5）将索引数据和表数据分开在不同的表空间上（降低IO冲突） 　　6）建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少IO） 　 　 逻辑上优化： 　　1）可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用 　　2）Sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句（如全部大写，全部小写等）oracle解析语句后会放置到共享池中， 如： 　　select * from Emp where name=？这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好 　　3）数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能 　　4）尽量不使用*号，如select * from Emp，因为要转化为具体的列名是要查数据字典， 比较耗时 　　5）选择有效的表名 　　对于多表连接查询，可能oracle的优化器并不会优化到这个程度， oracle 中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表（也就是基础表）选 择数据较少的表，这样排序更快速，如果有link表（多对多中间表），那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如 果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sql 　　6）Where字句规则： 　　Oracle 中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾， 另外！=符号比较的列将不使用索引，列经过了计算（如变大写等）不会使用索引（需要建立起函数）， is null、is not null等优化器不会使用索引 　　7）使用Exits Not Exits 替代 In Not in 　　8）合理使用事务，合理设置事务隔离性，数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数