论文部分内容阅读
摘要： 通过河南油田开发数据库数据量统计工作的体会，针对SQL优化在Oracle数据库工作中的应用作以总结和研究。以便对以后的工作有很好的指导促进作用，提高生产、科研工作效率。
关键词： PL/SQL Developer；Oracle；SQL；优化
中图分类号：TP 文献标识码：A 文章编号：1671－7597（2011）0310090－01
1 SQL语句优化技术概述
1.1 SQL在数据库中的工作步骤
SQL语句在数据库中的工作流程分为5个步骤：服务器收到SQL语句、SQL语句解析、数据库的优化器决定执行路径、变量赋值、执行。
1.2 SQL优化的规则
SQL语句优化的目标是使其简单有效，有以下规则：
消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的I/O，从而拖慢整个数据库的性能。在一个有序的表中，如果查询返回少于40%的行，或者在一个无序的表中，返回少于7%的行，那么这个查询都可以调整为使用一个索引来代替全表搜索。
确保最优的索引使用：对于改善查询的速度，这是特别重要的。有时Oracle 可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保Oracle 使用正确的索引。它还包括bitmap 和基于函数的索引的使用。
这些规则看来简单，不过它们占SQL调优任务的90%[2]。
2 油田开发数据库应用实例
在统计静态数据表如表DAA05单井小层数据表时，要求统计出该表的井口数量、列出井号、解释序号两个数据项，在新表中显示井号和researcherList两列，以井号首字母拼音排序，并将同一口井的解释序号列的数据都归入researcherList列中。
这里主要用到SQL语句在Oracle查询中的查询、合并行、和并列和排序几个方面，按照常规的组合查询方式的代码如下图一，输出结果图图二：
在面对有十多万行的数据表时，就要求需要在SQL语句的编写上加以优化简练，当使用create index、ORDER BY或者GROUP BY的语句时，Oracle数据库将会自动执行排序的操作。
当与Oracle建立起一个session时，在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接（dedicated connection），
那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个Program Global Area （PGA）。因此，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disk sorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。
可以将以上语句组合简化至如三：
对比两次查询可以看出，在查询结果输出相同的情况下，未经优化的SQL执行时间为266.569秒，六分多钟。而经过简洁优化后的SQL语句执行时间为66.394秒，仅之前时间的六分之一，也就是此次优化语句的效率提高了6倍。
利用优化语句分别对DAA03（井斜数据表，统计井口数量2331口）、DAA07（油层分组数据表，统计井口数量2731口）、DAA091（射孔井段数据表，统计井口数量2278口）等多个表进行查询统计，总体的工作效率提高了几十倍。
3 总结
在多数情况下，数据库使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。
参考文献：
[1]（美）富尔斯汀，《ORACLE PL/SQL最佳实践（第1版）》，龚波、张平、潘显俊等译，机械工业出版，2008年3月.
作者简介：
王辉，男，中国石化河南油田分公司勘探开发研究院。
关键词： PL/SQL Developer；Oracle；SQL；优化
中图分类号：TP 文献标识码：A 文章编号：1671－7597（2011）0310090－01
1 SQL语句优化技术概述
1.1 SQL在数据库中的工作步骤
SQL语句在数据库中的工作流程分为5个步骤：服务器收到SQL语句、SQL语句解析、数据库的优化器决定执行路径、变量赋值、执行。
1.2 SQL优化的规则
SQL语句优化的目标是使其简单有效，有以下规则：
消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的I/O，从而拖慢整个数据库的性能。在一个有序的表中，如果查询返回少于40%的行，或者在一个无序的表中，返回少于7%的行，那么这个查询都可以调整为使用一个索引来代替全表搜索。
确保最优的索引使用：对于改善查询的速度，这是特别重要的。有时Oracle 可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保Oracle 使用正确的索引。它还包括bitmap 和基于函数的索引的使用。
这些规则看来简单，不过它们占SQL调优任务的90%[2]。
2 油田开发数据库应用实例
在统计静态数据表如表DAA05单井小层数据表时，要求统计出该表的井口数量、列出井号、解释序号两个数据项，在新表中显示井号和researcherList两列，以井号首字母拼音排序，并将同一口井的解释序号列的数据都归入researcherList列中。
这里主要用到SQL语句在Oracle查询中的查询、合并行、和并列和排序几个方面，按照常规的组合查询方式的代码如下图一，输出结果图图二：
在面对有十多万行的数据表时，就要求需要在SQL语句的编写上加以优化简练，当使用create index、ORDER BY或者GROUP BY的语句时，Oracle数据库将会自动执行排序的操作。
当与Oracle建立起一个session时，在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接（dedicated connection），
那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个Program Global Area （PGA）。因此，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disk sorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。
可以将以上语句组合简化至如三：
对比两次查询可以看出，在查询结果输出相同的情况下，未经优化的SQL执行时间为266.569秒，六分多钟。而经过简洁优化后的SQL语句执行时间为66.394秒，仅之前时间的六分之一，也就是此次优化语句的效率提高了6倍。
利用优化语句分别对DAA03（井斜数据表，统计井口数量2331口）、DAA07（油层分组数据表，统计井口数量2731口）、DAA091（射孔井段数据表，统计井口数量2278口）等多个表进行查询统计，总体的工作效率提高了几十倍。
3 总结
在多数情况下，数据库使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。
参考文献：
[1]（美）富尔斯汀，《ORACLE PL/SQL最佳实践（第1版）》，龚波、张平、潘显俊等译，机械工业出版，2008年3月.
作者简介：
王辉，男，中国石化河南油田分公司勘探开发研究院。