• 1. SQL优化方案2010年9月版权所有
  • 2. 议题一般原则 其他原则 索引的使用 常用操作符 Oracle系统优化方案 SQL书写的影响 经典SQL语句举例 其他建议
  • 3. SQL语句的执行过程A、用户发出SQL请求,打开游标; B、把SQL语句语法分析,执行计划,数据字典等信息存入内存中共享池内; C、从数据文件中把相关数据块读入数据缓冲区; D、做相应操作,若做修改,先加上行级锁,经确认后,把改过前后记录内容存入重做日志缓冲区内; E、返回结果给用户,关闭游标。 备注:SQL语句大小写敏感的,同样的一个语句,若大小写不同,oracle需分析执行两次,每句后必以“;”结束。
  • 4. 减少和服务器的交互 由于目前是大集中模式,所以使得服务器资源显得格外珍贵,所以推荐设计接口的时候基于如下原则: 1)减少交互次数,接口设计上尽可能抽象,将同类型业务抽象到同一个接口来,实现一次交互能完成客户端几个需求。 2)无法抽象融合的业务,实现接口颗粒变小,最快速度完成,避免造成交互的延迟。
  • 5. 减少和服务器的交互(续)例:客户端登陆动作 目前交互为: 1、通过编码获取名称。 2、校验密码。 3、通过编码获取用户其它信息。 4、通过用户获取角色 目前一次登陆动作,需要和服务器发生至少四次交互动作。100个用户同时登陆,交互次数就是400次,这样服务器压力当然大。 修改后交互直接归纳为: 1、通过编码直接获取所有信息,不存在则返回固定信息等等。 2、其它动作如密码验证等直接在客户端完成。 修改后一次交互即可。
  • 6. 尽量避免全表扫描 a. 全表扫描   全表扫描就是顺序地访问表中每条记录。 ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 b. 通过ROWID访问表  你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息……ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高。
  • 7. 尽量避免全表扫描(续) 消除不必要的大表全表搜索:不必要的全表搜索导致大量不必要的I/O,从而拖慢整个数据库的性能。在一个有序的表中,如果查询返回少于40%的行,或者在一个无序的表中,返回少于7%的行,那么这个查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说,最常见的调优方法是增加索引。可以在表中加入标准的B树索引,也可以加入bitmap和基于函数的索引。
  • 8. 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时,会运用排序及合并的方式连接它们。首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。   例如:   表 TAB1 16,384 条记录   表 TAB2 1 条记录   选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒   选择TAB2作为基础表 (不佳的方法)  select count(*) from tab2,tab1 执行时间26.09秒   
  • 9. 选择最有效率的表名顺序(续) 如果有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   WHERE E.CAT_NO = C.CAT_NO   AND E.LOCN = L.LOCN   AND E.EMP_NO BETWEEN 1000 AND 2000
  • 10. 其他原则
  • 11. SELECT子句中避免使用 ‘ * ‘ 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法。不幸的是,这是一个非常低效的方法。实际上,ORACLE在解析的过程中, 会将‘*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
  • 12. 用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换   例如:   低效: SELECT DISTINCT DEPT_NO,DEPT_NAME   FROM DEPT D,EMP E   WHERE D.DEPT_NO = E.DEPT_NO   高效:  SELECT DEPT_NO,DEPT_NAME   FROM DEPT D   WHERE EXISTS ( SELECT ‘X’   FROM EMP E   WHERE E.DEPT_NO = D.DEPT_NO);   EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
  • 13. 用表连接替换EXISTS 通常来说 , 采用表连接的方式比EXISTS更有效率  SELECT ENAME   FROM EMP E   WHERE EXISTS (SELECT ‘X’   FROM DEPT   WHERE DEPT_NO = E.DEPT_NO   AND DEPT_CAT = ‘A’);   (更高效) SELECT ENAME   FROM DEPT D,EMP E   WHERE E.DEPT_NO = D.DEPT_NO   AND DEPT_CAT = ‘A’ ;  (在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)
  • 14. 用NOT EXISTS替代NOT IN 在子查询中,NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.   例如: SELECT …  FROM EMP  WHERE DEPT_NO NOT IN (SELECT DEPT_NO   FROM DEPT   WHERE DEPT_CAT=’A’);   为了提高效率。改写为:   (方法一: 高效)  SELECT ….   FROM EMP A,DEPT B   WHERE A.DEPT_NO = B.DEPT(+)   AND B.DEPT_NO IS NULL   AND B.DEPT_CAT(+) = ‘A’   (方法二: 最高效) SELECT ….   FROM EMP E   WHERE NOT EXISTS (SELECT ‘X’   FROM DEPT D   WHERE D.DEPT_NO = E.DEPT_NO   AND DEPT_CAT = ‘A’);
  • 15. 使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。   (Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)
  • 16. 通过内部函数提高SQL效率 SELECT H.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.TYPE_DESC;   通过调用下面的函数可以提高效率。 FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2   AS   TDESC VARCHAR2(30);   CURSOR C1 IS   SELECT TYPE_DESC   FROM HISTORY_TYPE   WHERE HIST_TYPE = TYP;   BEGIN   OPEN C1;   FETCH C1 INTO TDESC;   CLOSE C1;   RETURN (NVL(TDESC,’?’));   END;   
  • 17. 通过内部函数提高SQL效率(续)FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2   AS   ENAME VARCHAR2(30);   CURSOR C1 IS   SELECT ENAME   FROM EMP   WHERE EMPNO=EMP;   BEGIN   OPEN C1;   FETCH C1 INTO ENAME;   CLOSE C1;   RETURN (NVL(ENAME,’?’));   END;   SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),   H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)   FROM EMP_HISTORY H   GROUP BY H.EMPNO , H.HIST_TYPE;   (经常在论坛中看到如 ‘能不能用一个SQL写出…。’ 的贴子, 殊不知复杂的SQL往往牺牲了执行效率。 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)
  • 18. 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序,总计等操作。 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。   例如:   低效:   SELECT REGION,AVG(LOG_SIZE)   FROM LOCATION   GROUP BY REGION   HAVING REGION != ‘SYDNEY’   AND REGION != ‘PERTH’   高效: SELECT REGION,AVG(LOG_SIZE)   FROM LOCATION   WHERE REGION != ‘SYDNEY’   AND REGION != ‘PERTH’   GROUP BY REGION   (HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等。 除此而外,一般的条件应该写在WHERE子句中)
  • 19. WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。   例如:   (低效,执行时间156.3秒)  SELECT …   FROM EMP E   WHERE SAL > 50000   AND JOB = ‘MANAGER’   AND 25 < (SELECT COUNT(*) FROM EMP   WHERE MGR=E.EMPNO);   (高效,执行时间10.6秒) SELECT …   FROM EMP E   WHERE 25 < (SELECT COUNT(*) FROM EMP   WHERE MGR=E.EMPNO)   AND SAL > 50000   AND JOB = ‘MANAGER’;
  • 20. 使用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函数高效地得到相同结果  SELECT COUNT(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子句中。
  • 21. 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)   而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。   (TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
  • 22. 共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径。 ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。    数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。   当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句。   这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等)。
  • 23. 共享的语句必须满足三个条件 A. 字符级的比较:   当前被执行的语句和共享池中的语句必须完全相同。   例如:  SELECT * FROM EMP;   和下列每一个都不同 SELECT * from EMP;   Select * From Emp;   SELECT * FROM EMP;   
  • 24. 共享的语句必须满足三个条件(续)B. 两个语句所指的对象必须完全相同:   例如:   用户 对象名 如何访问   Jack sal_limit private synonym    Work_city public synonym    Plant_detail public synonym   Jill sal_limit private synonym    Work_city public synonym    Plant_detail table owner   考虑一下下列SQL语句能否在这两个用户之间共享。   SQL 能否共享 原因   select max(sal_cap) from sal_limit; 不能 每个用户都有一个private synonym - sal_limit , 它们是不同的对象   select count(*) from work_city where sdesc like 'NEW%'; 能 两个用户访问相同的对象public synonym - work_city   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 是表的所有者,对象不同.
  • 25. 共享的语句必须满足三个条件(续)C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)   例如:第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)   a.  select pin , name from people where pin = :blk1.pin; select pin , name from people where pin = :blk1.pin;   b. select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind;
  • 26. 索引的使用
  • 27. 使用索引的目的 加快查询速度 减少I/O操作 消除磁盘排序
  • 28. 用索引提高效率 索引是表的一个概念部分,用来提高检索数据的效率。 实际上,ORACLE使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。   除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列。 通常, 在大型表中使用索引特别有效。 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率。   虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。 索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。   定期的重构索引是有必要的。   ALTER INDEXREBUILD
  • 29. SQL语句索引的利用采用函数处理的字段不能利用索引,如: substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate), 优化处理:sk_rq>=trunc(sysdate) and sk_rq 50,优化处理:ss_df >30 ‘X’ || hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’ sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5 hbs_bh=5401002554,优化处理:hbs_bh=’ 5401002554’,注:此条件对hbs_ bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。 条件内包括了多个本表的字段运算时不能进行索引,如:ys_df>cx_df,无法进行优化 qc_bh || kh_bh=’5400250000’,优化处理:qc_bh=’5400’ and kh_bh=’250000’
  • 30. 用EXISTS替代IN 低效:                                                                         SELECT *  FROM EMP (基础表)   WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO  FROM DEPT WHERE LOC = ‘MELB’)                                         高效:                                                                           SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’   FROM DEPT  WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’) 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的,从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。推荐方案:在业务密集的SQL当中尽量不采用IN操作符,用EXISTS 方案代替
  • 31. 用>=替代>高效: SELECT * FROM EMP WHERE DEPTNO >=4   低效:   SELECT *FROM EMPWHERE DEPTNO >3        两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
  • 32. 使用OR替换不等于操作符(<>、!=) 下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。 select cust_Id,cust_name from customers where cust_rating <> 'aa'; 把上面的语句改成如下的查询语句,这样,在采用基于规则的 优化器而不是基于代价的优化器(更智能)时,将会使用索引。 select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa'; 特别注意:通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。
  • 33. 用UNION替换OR (适用于索引列)高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”   低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
  • 34. 用IN来替换OR低效:   SELECT….FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30  高效 SELECT…FROM LOCATION WHERE LOC_IN IN (10,20,30);
  • 35. 避免在索引列上使用IS NULL和IS NOT NULL使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索引) 推荐方案:用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。 不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。
  • 36. 比较不匹配的数据类型 比较不匹配的数据类型也是比较难于发现的性能问题之一。 注意下面查询的例子,account_number是一个VARCHAR2类型, 在account_number字段上有索引。下面的语句将执行全表扫描。 select bank_name,address,city,state,zip from banks where account_number = 990354; Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了 索引的使用,改成下面的查询就可以使用索引: select bank_name,address,city,state,zip from banks where account_number ='990354'; 特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用, 即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
  • 37. 其他 避免在索引列上使用计算 避免在索引列上使用NOT 用UNION-ALL 替换UNION ( 如果有可能的话) 避免使用耗费资源的操作 带有DISTINCT,UNION,MINUS, ORDER BY的SQL语句会启动SQL引擎 尽量建单个索引
  • 38. 常用操作符
  • 39. > 及 < 操作符(大于或小于操作符)大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的 情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
  • 40. LIKE操作符 LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ ORYY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。
  • 41. UNION操作符UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行 排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最 常见的是过程表与历史表UNION。如: select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后 返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将 两个结果合并后就返回。 select * from gc_dfys union all select * from ls_jg_dfys
  • 42. Oracle系统优化方案
  • 43. 一、ORACLE系统配置优化1、TABLESPACE的使用率 2、CURSOR的数量 3、SGA/PGA的配置优化 4、sort_area_size大小
  • 44. 一、ORACLE系统配置优化——一些有用的脚本1、查询被锁的对象 select c.MACHINE,c.SID,b.object_name from V$LOCKED_OBJECT a ,dba_objects b,v$session c where a.OBJECT_ID=b.object_id and a.SESSION_ID=c.SID 2、将库中的表重新做统计分析 analyze table table_name compute statistics; 3、计算表的空间 selecT sum(bytes) FROM DBA_SEGMENTS WHERE OWNER='ZEXCHANGE' AND segment_type='TABLE‘ and segment_name='HOLD7' ; 4、释放高水线之下的空间 alter table pay deallcate unused keep 0; alter index INDEX_NAME rebuild; 5、查询消耗资源最多的SQL语句 select * from v$sqlarea order by cup_time desc
  • 45. SQL书写的影响
  • 46. SQL书写的影响1、同一功能同一性能不同写法SQL的影响。 如一个SQL在A程序员写的为 Select * from zl_yhjbqk B程序员写的为 Select * from dlyx.zl_yhjbqk(带表所有者的前缀) C程序员写的为 Select * from DLYX.ZLYHJBQK(大写表名) D程序员写的为 Select * from DLYX.ZLYHJBQK(中间多了空格) 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
  • 47. SQL书写的影响(续)2、WHERE后面的条件顺序影响 WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如: Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下' 以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所 以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为 99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy _dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的 比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。
  • 48. SQL书写的影响(续)3、查询表顺序的影响 在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下,ORACLE会按表出现的顺序进行链接,由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)
  • 49. 经典SQL语句举例
  • 50. 整合简单,无关联的数据库访问 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)   例如: 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 E.NAME , D.NAME , C.NAME   FROM CAT C , DPT D , EMP E,DUAL X   WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))   AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))   AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))   AND E.EMP_NO(+) = 1234   AND D.DEPT_NO(+) = 10   AND C.CAT_TYPE(+) = ‘RD’;   (虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者还是要权衡之间的利弊)
  • 51. 复制表 只复制结构,源表名:a 新表名:b select * into b from a where 1<>1
  • 52. 删除重复记录 Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
  • 53. 数据库分页 select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
  • 54. 包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select a from tableA ) except (select a from tableB) except (select a from tableC)
  • 55. 删除重复记录 最高效的删除重复记录方法 ( 因为使用了ROWID)  DELETE FROM EMP E   WHERE E.ROWID > (SELECT MIN(X.ROWID)   FROM EMP X   WHERE X.EMP_NO = E.EMP_NO);
  • 56. 其他建议
  • 57. SQL语句编写建议SQL语句编写建议 1)少使用IN,OR等符号。 2)视图中少使用嵌套查询。 3) 不要为了图方便使用1=0的语句。 4)多使用group by 或者子查询缩小查询范围。 5)建立相关的索引,如果索引匹配少则直接使用 x 6)尽可能使用preparedstatement语法,insert xxxx (a,b) values (?,?), 可以减少SQL解析时间。 7) 尽可能少使用distinct,创建临时表花费很长时间。
  • 58. SQL语句编写建议(续) 8) 、调用PLSQL的执行计划分析器“Explain plan window”,在里面可以分析具体SQL语句的执行计划,然后根据具体情况进行调优。 9) 、直接查询V$SQL表,表中会对当前已执行的SQL语句进行记录,其中某些关键值可以供分析,如共享内存花费Sharable_mem,执行次数Executions,总执行时间Elapsed_time等等,根据分析可以查出某些写法存在问题的SQL。
  • 59. 谢谢!