Oracle数据库SQL语句优化初探
Oracle数据库SQL语句优化初探
摘要数学库的应用越来越多,数据查询使用较频繁,同时查询也加重了数据库的负荷,本文阐述sql查询的内部原理、oracle优化器及访问方式,通过对oracle数据库sql语句的优化提高数据库性能。
关键词 oracle;sql;优化
中图分类号tp392 文献标识码a 文章编号
1674-6708(2010)31-0213-02
随着网络信息技术的不断发展,数据库技术应用越来越广泛。
数据库的调优工作涉及内容很广,从系统的规划、库表的设计、sql语句的编写、物理设备和网络设备的性能、内存和存储空间的分配等等都影响着系统的性能,通过优化,我们可以大大的提高系统运行
效率和存储空间的利用率,达到用有限的资源实现一个高效的应用系统,因此,科学地构造数据库结构,合理使用查询语句及查询方法,是成功开发和应用数据库系统的重要环。
本文将阐述sql查询的内部原理、oracle优化器及访问方式、oracle数据库sql优化原则。
1 sql查询内部原理
查询在处理过程中分为4个阶段:将查询转换为内部格式阶段、将内部格式转换为规范格式阶段、为执行选择低层过程阶段、生成并选择最低的查询计划阶段。
如图:
1.1阶段1:将查询转换为内部格式阶段
这一阶段主要是进行语法分析,将原查询转换为数据库内部格式,
以便于机器处理,不符合语法规范的报错返回,为sql优化过程铺平道路。
1.2阶段2:将内部格式转换为规范格式
在这一阶段,数据库优化器将执行一系列“保证能够优化”的优化过程,是不会去考虑实际数据的值和数据库的存取路径;优化器
将查询的内部表示转换为等价的规范格式。
比如说将“a=b替换为b=a或者是p and q 替换为q and p”,这样做的目的是消除语句表面上的差异,以便能够找到一种在某些方面比原查询更为高效的表示方法。
比如说能够将表达式(a join b)where restriction on a 转为等价高效的表达式(a where restriction on a)join b。
1.3阶段3:为执行选择低层过程
在这一阶段,优化器考虑的是索引、物理存取路径、数据值的分布、数据的物理聚集存储等问题。
基本的策略就是将查询表达式看成一系列的“低层操作”,对于每一个可能的低层操作,都有一组可用的低层过程,而每一个低层过程都会有一个相关的代价计算公式(如磁盘i/o代价,cpu利用率等)。
1.4阶段4:生成并选择最低代价的查询计划
这一阶段就是构造一组查询计划,选择一个最优,也就是代价最
小的查询计划。
因为每个计划都绑定一系列的低层过程,每一个过程对应查询中的一个低层操作。
优化器此时将所有的过程进行计算,选择一个代价最低的执行。
2 oracle的优化器及表的访问方式
oracle的优化器共有3种:rule (基于规则)、cost (基于成本)、choose (选择性)。
设置缺省的优化器,可以通过对init.ora文件中optimizer_mode参数的各种声明,如
rule,cost,choose,all_rows,first_rows。
你当然也在sql句级或是会话级对其进行覆盖。
为了使用基于成本的优化器,必须经常运行analyze 命令,以增加数据库中的对象统计信息的准确性。
如果数据库的优化器模式设置为选择性,那么实际的优化器模式将和是否运行过分析命令有关。
如果表已经被分析过,优化器模式将自动成为cbo,反之,数据库将采用rule形式的优化器。
在缺省情况
下,oracle采用choose优化器,为了避免那些不必要的全表扫描,
必须尽量避免使用choose优化器,而直接采用基于规则或者基于成本的优化器。
oracle数据库访问table的方式有两种:全表扫描和通过rowid 访问表。
全表扫描就是顺序地访问表中每条记录。
oracle采用一次读入多个数据块的方式优化全表扫描。
通过rowid访问表,因rowid 包含了表中记录的物理位置信息,这样可以提高访问表的效率,同
时oracle采用索引实现了数据和存放数据的物理位置(rowid)之间的联系, 通常索引提供了快速访问rowid的方法,因此那些基于索引列的查询就可以得到性能上的提高。
3 sql语句优化工作主要应该从6个面进行调整
一是去掉不必要的大型表的全表扫描;二是缓存小型表的全表扫描 ;三是检验优化索引的使用 ;四是检验优化的连接技术;五是尽
可能减少执行计划的代价;六是共享sql。
例如:在查询语句的使用中,尽量减少大型表的全表扫描,如果使用大型表的全表扫描会极大地加重数据库的负载,降低数据库的运行性能,同时合理使用索引技术,不要使用in、not in、<>、is null 等操作符,这些操作将不会使用索引,并引起数据库会进行全表扫描;注重多表查询的连接技术,缓存小型表的全表扫描,可以通过将小型表置入缓冲区高速缓存的keep池中实现,oracle的这个功能可以大大地提高sql的执行性能并节省内存的使用;尽可能使sql共享,在实际操作中注意sql语句的一致性,提高sql语句的“命中率”,因为提高sql共享程度可以不重复解析相同的sql语句,在第一次解析之后,oracle将sql语句存放在内存中,这块位于系统全局区域sga的共享池中的内存可以被所有的数据库用户共享,因此,当你执行一个sql语句(比如游标)时,如果它和之前的执行过的语句完全相同,oracle就能很快获得已经被解析的语句以及最好的执行路径。
在大型数据库的应用中,查询是一个非常重要又使用频繁的操作,因而查询sql的编写技巧将极大地影响数据库的性能,以上的6个方面仅反映了sql优化的一些应用技术,在实际工作中还有更多领域需要进一步研究与探讨,如sql查询的授权与分级操作等,以更进一步提高数据库性能。
参考文献
[1]john carnell,等著.oracle 8i 应用高级编程-java,pl/sql
和xml深入开发.清华大学出版社.
[2]rama velpuri,等著.新版oracle8故障解决手册.机械工业出版社.。
Oracle优化SQL语句,提高效率
Oracle 优化SQL 语句，提高效率我们都了解索引是相关表概念部分，主要是提高检索数据的相关效率，当Oracle 使用了较为复杂的自平衡 B-tree 结构时。
我们一般是通过索引查询数据 比全表扫描要快。
当 Oracle 找出执行查询和 Update 语句的最好路径时 , Oracle 优化将使用索引。
同样在联结多个表时使用索引也能够提高效率。
另一个使用索引的好处是 , 他提供了主键 （primary key ） 的唯一性验证。
那些 LON （或 LONCRAW 数据类型,您能够索引几乎任何的列。
通常,在大型表中使用 索引特别有效. 当然,您也会发现, 在扫描小表时，使用索引同样能提高效率。
虽然使用索引能得到查询效率的提高 , 但是我们也必须注意到他的代价。
索引需要空间来存储 ,也需要定期维护 , 每当有记录在表中增减或索引列被 修改时 , 索引本身也会被修改。
这意味着每条记录的 INSERT ，DELETE ， UPDATE 将为此多付出 4、 5 次的磁盘 I/O 。
因为索引需要额外的存储空间和处理，那 些不必要的索引反而会使查询反应时间变慢。
定期的重构索引是有必要的：ALTER INDEX REBUILD1.用 EXISTS 替换 DISTINCT当提交一个包含一对多表信息 （ 比如部门表和雇员表 ） 的查询时，避免在SELECT?句中使用DISTINCT 。
一般能够考虑用 EXIST 替换，EXISTS 使查询更 为迅速，因为RDBM 核心模块将在子查询的条件一旦满足后， 立即返回结果。
例 子：（低效）: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHEREEXISTS （ SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO ）;2.SQL 语句用大写的；因为 Oracle 总是先解析SQL 语句，把小写的字母转 换成大写的再执行。
OracleSQL的优化
Oracle SQL的优化标签：oraclesql优化date数据库subquery2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类：Oracle Basic Knowledge（208）SQL的优化应该从5个方面进行调整：1.去掉不必要的大型表的全表扫描2.缓存小型表的全表扫描3.检验优化索引的使用4.检验优化的连接技术5.尽可能减少执行计划的CostSQL语句：是对数据库(数据)进行操作的惟一途径；消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；可以有不同的写法；易学，难精通。
SQL优化：固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。
应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致ORACLE优化器：在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是要么结果表达式能够比源表达式具有更快的速度要么源表达式只是结果表达式的一个等价语义结构不同的SQL结构有时具有同样的操作（例如：= ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。
1 常量优化：常量的计算是在语句被优化时一次性完成，而不是在每次执行时。
下面是检索月薪大于2000的的表达式：sal > 24000/12sal > 2000sal*12 > 24000如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。
优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。
否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。
2 操作符优化：优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。
Oracle SQL性能优化方法研究(doc 16页)
Oracle SQL性能优化方法研究(doc 16页)Oracle SQL性能优化方法探讨12综述ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要经过反反复复的过程。
在数据库建立时，就能根据应用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有很大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积累经验，从而更好地进行数据库的调优。
数据库性能调优的方法●调整内存●调整I/O●调整资源的争用问题●调整操作系统参数●调整数据库的设计●调整应用程序本文针对应用程序的调整，来说明对数据库性能如何进行优化。
3 表分区的应用对于海量数据的表，可以考虑建立分区以提高操作效率。
建立分区一般以关键字为分区的标志，也可以以其他字段作为分区的标志，但效率不如关键字高。
建立分区的语句在建表时可以进行说明：create table TABLENAME(<field list>)partition by range (PutOutNo)(partitionPART1 values lessthan (200312319999) partitionPART2 values lessthan (200412319999) partition PART3 values lessthan (200512319999) 。
建好分区后，数据的逻辑存储方式进行了优化TABLEN 200200200。
这样，在进行大部分数据查询，数据更新和数据插入时，Oracle自动判断操作应该在哪个分区进行，避免了整表操作，提高了执行的效率4访问Table的方式ORACLE 采用两种访问表中记录的方式:●全表扫描全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.●通过ROWID访问表可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.5共享SQL语句为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径.ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL 语句必须完全相同(包括空格,换行等).共享的语句必须满足三个条件:●字符级的比较:当前被执行的语句和共享池中的语句必须完全相同.例如:SELECT * FROM EMP;和下列每一个都不同SELECT * from EMP;Select * From Emp;SELECT * FROM EMP;●两个语句所指的对象必须完全相同:例如:用户对象名如何访问Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJill sal_limit private synonymWork_city public synonymPlant_detail table owner下列SQL语句不能在这两个用户之间共享.select max(sal_cap) from sal_limit;原因每个用户都有一个private synonym - sal_limit , 它们是不同的对象下列SQL语句能在这两个用户之间共享.select count(*) from work_city where sdesc like 'NEW%';原因：两个用户访问相同的对象public synonym - work_city下列SQL语句不能在这两个用户之间共享.select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id原因：用户jack 通过private synonym 访问plant_detail 而jill 是表的所有者,对象不同.两个SQL语句中必须使用相同的名字的绑定变量(bind variables)例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)1.select pin , name from people where pin = :blk1.pin;select pin , name from people where pin = :blk1.pin;2.select pin , name from people where pin = :blk1.ot_ind;select pin , name from people where pin = :blk1.ov_ind;6选择最有效率的表名顺序ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM 子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.例如: 表TAB1 16,384 条记录,表TAB2 1 条记录选择TAB2作为基础表(最好的方法)select count(*) from tab1,tab2选择TAB2作为基础表(不佳的方法)select count(*) from tab2,tab1如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.例如: EMP表描述了LOCATION表和CATEGORY表的交集.SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN将比下列SQL更有效率SELECT * FROM EMP E , LOCATION L , CATEGORY C WHEREE.CAT_NO = C.CAT_NO AND E.LOCN =L.LOCN AND E.EMP_NO BETWEEN 1000 AND 20007WHERE子句中的连接顺序．ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:(低效)SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效)SELECT … FROM EMP E WHERE25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO) AND SAL >50000 AND JOB = ‘MANAGER’;8SELECT子句中避免使用’*’当在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.可是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.9减少访问数据库的次数当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等等. 由此可见, 减少访问数据库的次数, 就能实际上减少ORACLE的工作量.例如,以下有三种方法可以检索出雇员号等于0342或0291的职员.方法1 (最低效)SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;方法2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECTEMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO …,..,.. ;…..OPEN C1(291);FETCH C1 INTO …,..,.. ;CLOSE C1;END;方法3 (高效)SELECT A.EMP_NAME , A.SALARY ,A.GRADE,B.EMP_NAME , B.SALARY ,B.GRADE FROM EMP A,EMP B WHEREA.EMP_NO = 342 ANDB.EMP_NO = 291; 10使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如:SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE‘SMITH%’;SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE‘SMITH%’;你可以用DECODE函数高效地得到相同结果SELECTCOUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%’;类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.11整合简单,无关联的数据库访问如果有几个简单的数据库查询语句,可以把它们整合到一个查询中(即使它们之间没有关系)例如:SELECT NAME FROM EMP WHERE EMP_NO = 1234;SELECT NAME FROM DPT WHERE DPT_NO = 10 ;SELECT NAME FROM CAT WHERE CAT_TYPE = ‘RD’;上面的3个查询可以被合并成一个:SELECT , , FROM CAT C , DPT D , EMPE,DUAL X WHERE NVL(‘X’,X.DUMMY)= NVL(‘X’,E.ROWID(+)) ANDNVL(‘X’,X.DUMMY) =NVL(‘X’,D.ROWID(+)) ANDNVL(‘X’,X.DUMMY) =NVL(‘X’,C.ROWID(+)) ANDE.EMP_NO(+) = 1234 AND D.DEPT_NO(+)= 10 AND C.CAT_TYPE(+) = ‘RD’;虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以还是要权衡之间的利弊12删除重复记录最高效的删除重复记录方法( 因为使用了ROWID)DELETE FROM EMP E WHEREE.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO =E.EMP_NO);13用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(注意：TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)14尽量多使用COMMIT只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源:●回滚段上用于恢复数据的信息.●被程序语句获得的锁●redo log buffer 中的空间●ORACLE为管理上述3种资源中的内部花费15计算记录条数和一般的观点相反, count(*) 比count(1)稍快, 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如COUNT(EMPNO)(并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)16用Where子句替换HAVING子句避免使用HA VING子句, HA VING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:低效:SELECT REGION，A VG(LOG_SIZE) FROM LOCATION GROUP BY REGION HA VING REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’高效SELECT REGION，A VG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION(HA VING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中) 17减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:低效SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)高效SELECT TAB_NAME FROMTABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)Update 多个Column 例子:低效:UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;高效:UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;18通过内部函数提高SQL效率.SELECTH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*) FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H WHERE H.EMPNO = E.EMPNO AND H.HIST_TYPE = T.HIST_TYPE GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.T YPE_DESC;通过调用下面的函数可以提高效率.FUNCTIONLOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN V ARCHAR2ASTDESC V ARCHAR2(30);CURSOR C1 ISSELECT TYPE_DESCFROM HISTORY_TYPEWHERE HIST_TYPE = TYP;BEGINOPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC,’?’));END;FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN V ARCHAR2ASENAME V ARCHAR2(30);CURSOR C1 ISSELECT ENAMEFROM EMPWHERE EMPNO=EMP;BEGINOPEN C1;FETCH C1 INTO ENAME;CLOSE C1;RETURN (NVL(ENAME,’?’));END;SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),H.HIST_TYPE,LOOKUP_HIST_TYP E(H.HIST_TYPE),COUNT(*)FROM EMP_HISTORY HGROUP BY H.EMPNO ,H.HIST_TYPE;19使用表的别名(Alias)当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)20用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.低效:SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPTWHERE LOC = ‘MELB’)高效:SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)21用NOT EXISTS替代NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例如:SELECT … FROM EMP WHE RE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);为了提高效率.改写为:(方法一: 高效)SELECT …. FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT(+) ANDB.DEPT_NO IS NULL ANDB.DEPT_CAT(+) = ‘A’(方法二: 最高效)SELECT …. FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ F ROM DEPTD WHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = ‘A’);22识别低效执行的SQL语句用下列SQL工具找出低效SQL:SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,ROUND((BUFFER_GETS-DISK_RE ADS)/BUFFER_GETS,2) Hit_radio,ROUND(DISK_READS/EXECUTION S,2) Reads_per_run,SQL_TEXTFROM V$SQLAREAWHERE EXECUTIONS>0AND BUFFER_GETS > 0AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8ORDER BY 4 DESC;(虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)23使用TKPROF 工具来查询SQL性能状态SQL trace 工具收集正在执行的SQL 的性能状态数据并记录到一个跟踪文件中.这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化系统.设置SQL TRACE在会话级别: 有效ALTER SESSION SET SQL_TRACE TRUE设置SQL TRACE 在整个数据库有效仿, 必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录(设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态. 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数. 可以参考ORACLE手册来了解具体的配置. )24用EXPLAIN PLAN 分析SQL语句EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL 的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.(通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便. )举例:SQL> list1 SELECT *2 FROM dept, emp3* WHERE emp.deptno = dept.deptnoSQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/SQL> /14 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (FULL) OF 'EMP'3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)Statistics----------------------------------------------------------0 recursive calls2 db block gets30 consistent gets0 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed通过以上分析,可以得出实际的执行步骤是:1. TABLE ACCESS (FULL) OF 'EMP'2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'4. NESTED LOOPS (JOINING 1 AND 3)注: 目前许多第三方的工具如TOAD 和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的可以选用它们.25实时批量的处理在我们的应用中，大部分是JSP控制业务逻辑的编写，但是当业务逻辑比较复杂，复杂的情况有两种●牵涉的表逻辑处理比较多●牵涉的表数据处理量比较大这时尽量采用数据库内建过程处理内建过程处理的优点：●内建过程是在数据库端执行的，sql语句的解析，数据的处理全部在内部完成，不需要额外的开销，效率可以提高。
Oracle数据库中SQL语句的优化与分析
第 ７步 ：Ｒｎ ｔｅ Ｓａ ｅｅ ｔ ｕ ｈ ｔ ｔｍ ｎ
第 ８步 ：Ｆ ｔ ｈ Ｒ ｗ ｆ ａ Ｑ ｅ ｙ ｅｃ ｏ ｓｏ ｕｒ
第 ９步 ：Ｃ ｏ ｅ ｔ ｅ Ｃ ｒ ｏ ｌ ｓ ｈ ｕ ｓ ｒ
下面 来详 细分 析这 些步骤 ： 第 １ ：Ｃ ｅ ｔ ｕ ｓ ｒ 步 ｒ ａ ｅ ａ Ｃ ｒ ｏ
执 行每 个 Ｓ Ｌ 句 ，Ｏａ ｌ Ｑ语 ｒ ｃｅ需要 实现 很多 步骤 。Ｏａ ｌ ｒ ｃｅ用 来 执 行 语句 的这 些 步骤 的组 合 被 称之 为执 行 计 划 。执 行 计 划 是 Ｓ Ｌ 化 中最为 复杂 也是最 为关 键 的部分 ，只有 知道 了 Ｏａ ｌ 在 Ｏ优 ｒｃｅ 内部到 底是 如何执 行 该 Ｓ Ｌ 句后 ，我们才 能知 道优 化器 选择 的 Ｏ语 执 行计 划是 否为 最优 的 。如何 分析 执行计 划 ，从 而找 出影 响性 能
ｓ ｌ ｃｅ ｓｓ ｉａ ｌ ｒｎｏ ． ｅ ｅ ｔｄ ａ ｕ ｔｂｅｏ ｔ
Ｋｅ ｗｏ ｄ ：ＱＬＯｒｃｅＯｐｉ ｚｔ ｎ ｙ ｒ ｓＳ ； ａ ｌ； ｔ ａｉ ｍｉ ｏ
一ቤተ መጻሕፍቲ ባይዱ
、
引 言
第 ６步 ： Ｐ ｒ ｌ ｅ ｉ ｅ ｔ ｅ Ｓ ａ ｅ ｅ ｔ ａ ａ ｌ ｌ ｚ ｈ ｔ ｔ ｍ ｎ
Ｓ ，ｎｙ ｋ ｏ ｈ ｗ ａ ｌ ｉｔｍａｌ ｘ ｃ ｔ ＱＬ ｓａｅ ｎ， ａ ｏ ｆ ｍ ｈ ｔｔｅｅ ｅｕｉｎｐａ ｅｅｔｅｏ ｔ ｚ ｔ ｎ ＱＬ ｏ ｌ ｎ ｗ ｏ Ｏｒｃｅ ｎｅ ｌ ｅ ｅｕｅＳ ｔｔｍｅ ｔ ｃｎ ｃｎ ｒ ｔａ ｈ ｘ ｃ ｔ ｌ ｗｈ ｒ ｈ ｐｉ ａｉ ｒ ｙ ｗｅ ｉ ｏ ｎ ｍｉ ｏ
的主要 问题 。下面 先 从分析 Ｓ Ｌ 句执 行步 骤 开始介 绍 ，再介 绍 Ｏ 语 如 何分 析执行 计划 。优 化器 有 时也被 称 为查询 优 化器 ，这 是因 为 查 询是 影响数 据库 性 能最主 要 的部分 ，优 化器 是所 有 关系数 据库 引擎 中的最神 秘 、最 富挑 战性 的部件 之一 ，从 性 能的角 度看 也是 最 重要 的部分 ，它 性 能的高低 直接 关 系到数 据库 性能 的好坏 。 二 、０ ａ ｌ ｒ ｃ ｅ的优化规 则 （ ）什 么是优 化 一
浅谈Oracle数据库SQL性能优化
浅谈Oracle数据库SQL性能优化摘要：随着计算机信息网络技术的不断发展，数据库系统取得很大突破。
面临网络化时代的进步，人们对网络信息的需求的也变得逐渐走向多元化。
网络信息数据库存取技术逐渐被广泛运用，数据库系统规模也越来越大。
目前Oracle 就是被广泛应用的一种数据库，其信息存储量能满足人们日益增长的需求，但为了能够保证其能够流畅稳定安全地运行，应当对其进行一定的优化措施。
关键词：Oracle数据库；SQL优化随着数据库技术功能逐步增加，应用范围逐渐扩展，效果也是日渐明显。
随着网络信息吞吐量的逐步增加，数据库系统在对数据进行处理时算法变得十分繁琐。
数据库系统如果长时间的超负荷工作就会变得反应迟钝影响效率，甚至可能导致死锁。
由于天天都将会有大量的SQL语句访问Oracl数据库系统，系统需要很多时间来处理这些访问，而SQL语句直接影响到Oracl数据库系统性能，所以运用对SQL语句优化的方法来提升ORACLE数据库的性能显得十分必要。
1、对SQL进行优化的必要性数据库系统作为数据管理的主要组成部分主要作用是存储供相关人员查阅大量信息，实现网络资源共享。
查询操作在数据库系统的各种操作中居于首位，直接关系到数据库系统的运行状态。
假如数据查询操作量过大，会给系统带来很大的负担，系统反应速度变慢，严重者可能就会引起系统瘫痪。
因此，为了保证数据库系统的高效正常运行，必须对SQL语句进行优化[1]。
图1.1SQL语句优化2、SQL优化的目标往往由于SQL的结构设计的问题，很可能使得正常运行的一个数据库系统出现性能问题。
所以必须对SQL语句进行必要的调整，达到有效提升数据库系统性能的目的。
对SQL结构的优化本质就是简化繁琐的数据结构，常规方法一般就是对SQL语法进行一些调整，基本方法是把程序中繁琐的SQL语句结构简化，保持服务器的搜索数据能力处于最佳运行状态，有效降低程序中表扫描的时间，促使所以功能得以充分发挥，尽量使服务器的处理器时间和输入输出时间保持平衡。
浅谈Oracle数据库SQL优化
浅谈Oracle数据库SQL优化摘要】Oracle数据库是当前应用最广泛的大型数据库之一，其性能优化直接关系到系统的运行效率。
本文主要阐述了如何通过SQL调整来实现Oracle数据库性能的优化。
【关键词】Oracle数据库;SQL;性能优化【中图分类号】TP311.138【文献标识码】A【文章编号】1009-9646(2009)04-0036-021引言随着Oracle数据库在各种大型商业应用系统中的广泛使用，Oracle数据库优化在系统实际应用中发挥着越来越重要的作用，数据库优化已成为数据库维护过程中的重要内容。
Oracle数据库可以从以下几个方面来进行优化。
首先，在数据库设计阶段对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下，使系统性能达到最佳，系统开销达到最小，为下一步的调整创造良好的先决条件。
然后可在此基础上进行网络调整、磁盘调整、实例调整、对象调整，最后进行SQL调整，最终实现数据库系统整体优化。
本文主要从SQL调整分析如何实现对数据库的优化。
2Oracle SQL调整的目标2.1去掉不必要的大表全表扫描。
一般情况下，可以通过添加索引的方法来去掉不必要的全表扫描。
2.2缓存小表全表扫描。
如果全表扫描是最好的访问方式，可以通过将小表放入KEEP池中的方法将其缓存，以提高访问速度。
2.3检验优化索引的使用。
检查每一个索引，确保Oracle使用正确的索引。
2.4检验优化的连接方法。
3Oracle SQL调整的过程3.1定位严重影响系统性能的SQL语句。
从V$sqlarea视图和stats$sql_summary表中获得SQL语句的列表，这些列表可以根据以下参数进行排序。
3.1.1executions，定位执行频繁的查询。
3.1.2 disk_reads，定位高磁盘读取量的查询。
3.1.3 rows_processed，定位处理大量记录的查询。
3.1.4 buffer_gets，定位高缓存读取量的查询。
浅析Oracle SQL性能优化(doc 8页)
浅析Oracle SQL性能优化(doc 8页)Oracle SQL 性能优化1.选择最有效率的表名顺序例如:表TAB1 16,384 条记录表TAB2 1 条记录选择TAB2作为基础表(最好的方法)select count(*) from tab1,tab2 执行时间0.96秒选择TAB1作为基础表(不佳的方法)select count(*) from tab2,tab1 执行时间26.09秒如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.例如:EMP表描述了LOCATION表和CATEGORY 表的交集.SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 ANDWHERE SAL > 50000AND JOB = ‘MANAGER'AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效,执行时间10.6秒)SELECT …FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO)AND SAL > 50000AND JOB = ‘MANAGER';2.SELECT子句中避免使用‘ * ‘3.用Where子句替换HA VING子句避免使用HA VING子句, HA VING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:低效: SELECT REGION，A VG(LOG_SIZE) FROM LOCATIONGROUP BY REGIONHA VI NG REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH'高效SELECT REGION，A VG(LOG_SIZE) FROM LOCATIONWHERE REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH'GROUP BY REGION4.减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多个Column 例子:低效: UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;5.使用表的别名当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.6.用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.低效:SELECT *FROM EMP (基础表)WHERE EMPNO > 0AND DEPTNO IN (SELECT DEPTNO FROM DEPTWHERE LOC = ‘MELB')高效:SELECT *FROM EMP (基础表)WHERE EMPNO > 0AND EXISTS (SELECT ‘X'FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')7.用NOT EXISTS替代NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例如:SELECT … FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT='A');为了提高效率.改写为:(方法一: 高效)SELECT ….FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = ‘A'(方法二: 最高效)SELECT ….FROM EMP EWHERE NOT EXISTS (SELECT ‘X' FROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = ‘A');8.用表连接替换EXISTS通常来说, 采用表连接的方式比EXISTS更有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT ‘X'FROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = ‘A');(更高效)SELECT ENAMEFROM DEPT D,EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = ‘A' ;9.用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换例如:低效:SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP EWHERE D.DEPT_NO = E.DEPT_NO高效:SELECT DEPT_NO,DEPT_NAMEFROM DEPT DWH ERE EXISTS ( SELECT ‘X'FROM EMP EWHERE E.DEPT_NO = D.DEPT_NO); EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.10.基础表的选择基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, SQL语句中基础表的选择是不一样的. 如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的连接条件都有索引对应, 在这种情况下, 基础表就是FROM 子句中列在最后的那个表.举例:SELECT , B.MANAGERFROM WORKER A,LODGING BWHERE A.LODGING = B.LODING;由于LODGING表的LODING列上有一个索引, 而且WORKER表中没有相比较的索引, WORKER表将被作为查询中的基础表.11.用UNION替换OR (适用于索引列)通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.高效:SELECT LOC_ID , LOC_DESC , REGION FROM LOCATIONWHERE LOC_ID = 10UNIONSELECT LOC_ID , LOC_DESC , REGION FROM LOCATIONWHERE REGION = “MELBOURNE”低效:SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10 OR REGION = “MELBOURNE”如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.注意:WHERE KEY1 = 10 (返回最少记录)OR KEY2 = 20 (返回最多记录)ORACLE 内部将以上转换为WHERE KEY1 = 10 AND((NOT KEY1 = 10) AND KEY2 = 20)12.用IN来替换OR下面的查询可以被更有效率的语句替换:低效:SELECT….FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30高效SELECT…FROM LOCATIONWHERE LOC_IN IN (10,20,30);13.避免在索引列上使用IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．举例:如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.举例:低效: (索引失效)SELECT …FROM DEPARTMENTWHERE DEPT_CODE IS NOT NULL;高效: (索引有效)SELECT …FROM DEPARTMENTWHERE DEPT_CODE >=0;14.总是使用索引的第一个列16. 用WHERE替代ORDER BYORDER BY 子句只在两种严格的条件下使用索引.ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.ORDER BY中所有的列必须定义为非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.例如:表DEPT包含以下列:DEPT_CODE PK NOT NULLDEPT_DESC NOT NULLDEPT_TYPE NULL非唯一性的索引(DEPT_TYPE)低效: (索引不被使用)SELECT DEPT_CODEFROM DEPTORDER BY DEPT_TYPEEXPLAIN PLAN:SORT ORDER BYTABLE ACCESS FULL高效: (使用索引)SELECT DEPT_CODEFROM DEPTWHERE DEPT_TYPE > 0EXPLAIN PLAN:TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX译者按:ORDER BY 也能使用索引! 这的确是个容易被忽视的知识点. 我们来验证一下:15.需要当心的WHERE子句某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.不使用索引:SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT !=0;使用索引:SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT >0;下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用了索引.不使用索引:SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTIONWHEREACCOUNT_NAME||ACCOUNT_TYPE='AM EXA';使用索引:SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTIONWHERE ACCOUNT_NAME = ‘AMEX' AND ACCOUNT_TYPE=' A';下面的例子中, ‘+'是数学函数. 就象其他数学函数那样, 停用了索引.不使用索引:SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTIONWHERE AMOUNT + 3000 >5000;使用索引:SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTIONWHERE AMOUNT > 2000 ;下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描.不使用索引:SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTIONWHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);使用索引:SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTIONWHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,'%');17, 避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORD ER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT 需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.16.优化GROUP BY提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.低效:SELECT JOB , A VG(SAL)FROM EMPGROUP JOBHA VING JOB = ‘PRESIDENT'OR JOB = ‘MANAGER'高效:SELECT JOB , A VG(SAL)FROM EMPWHERE JOB = ‘PRESIDENT'OR JOB = ‘MANAGER'。
OracleSQL语句之常见优化方法总结
OracleSQL语句之常见优化方法总结下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法：1、SQL语句尽量用大写的；因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。
2、使用表的别名：当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。
这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误。
3、选择最有效率的表名顺序(只在基于规则的优化器(RBO)中有效)：ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理，在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
4、WHERE子句中的连接顺序：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
5、SELECT子句中避免使用 * ：ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
6、减少访问数据库的次数：ORACLE在内部执行了许多工作: 解析SQL语句,估算索引的利用率,绑定变量,读数据块等。
7、整合简单、无关联的数据库访问：如果有几个简单的数据库查询语句,可以整合到一个查询中(即使它们之间没有关系)。
8、在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。
9、删除重复记录：最高效的删除重复记录方法：1.DELETE FROM TEMP E2.WHERE E.ROWID > (SELECT MIN(X.ROWID)3.FROM TEMP1 X WHERE X.TEMP_NO = E.TEMP_NO);10、减少对表的查询：在含有子查询的SQL语句中,要特别注意减少对表的查询。
论Oracle数据库的性能优化问题
论Oracle数据库的性能优化问题Oracle数据库是一款流行的企业级数据库软件，但其性能优化问题也是不可避免的。
在实际应用中，如果Oracle数据库出现性能问题，将有严重的影响和损失。
因此，本文将讨论如何优化Oracle数据库的性能问题。
首先，针对Oracle数据库的性能瓶颈，可以通过调整数据库参数来提高性能。
Oracle数据库有很多参数可以配置，例如，缓存区大小、连接数、内存分配等。
通过针对不同的应用场景调整不同的参数配置，可以最大化地利用数据库的性能。
其次，针对SQL的性能问题，可以通过改进SQL语句来提高性能。
SQL优化是一项复杂的工作，但可以通过分析SQL执行计划来发现性能瓶颈，例如，缺乏索引、大表连接、高开销的子查询等。
并可以通过添加索引、优化查询语句等方式来提高数据库的性能。
除此之外，还可以通过加强硬件设备等方面来提升数据库性能。
例如，扩展数据库服务器的内存和硬盘容量，可以提高数据库的读写速度。
而使用高速网络设备如IB网络和10/100G以太网设备等，也可提高数据库的数据传输速度。
此外，Oracle数据库的性能优化也需要管理进程的支持与配合。
例如，数据库管理员需要监控数据库服务器硬件和软件性能，例如Oracle数据库的内部锁、等待事件、I/O活动等等。
在监控到性能问题后，需要在业务空档期进行优化，如调整SQL语句、更改数据库参数等。
总之，提高Oracle数据库的性能需要全面考虑软硬件配置、SQL语句等多个方面的因素。
通过合理的参数配置、SQL优化和硬件支持等方式，可以优化数据库的性能，提高应用的稳定性和响应速度。
ORACLE SQL语句优化总结
1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.（2） WHERE子句中的连接顺序．：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.（3） SELECT子句中避免使用‘ * ‘：ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间（4）减少访问数据库的次数：ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200（6）使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.（7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)（8）删除重复记录：最高效的删除重复记录方法 ( 因为使用了ROWID)例子：DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);（9）用TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)（10）尽量多使用COMMIT：只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费（11）用Where子句替换HAVING子句：避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having 比较了。