[转发] ORACLE中SQL查询优化研讨

 

3  SQL优化的相似步骤

   
SQL优化一般需通过发现标题、分析问题、提议消除方式、应用措施、测试品质多少个步骤,如图1所示。“发现难题正是消除难题的八分之四”,因而在SQL调优进度中,定位难点SQL是丰盛关键的一步,一般可依靠ORACLE自带的习性优化学工业具如STATSPACK、TKPROF、AUTOTRACE等帮衬用户进行,同时还相应尊重动态品质视图如V$SQL、V$MYSTAT、V$SYSSTAT等的商讨。

图片 1

图1  SQL优化的相似步骤

4.2  优化I/O

   
过多的I/O操作会占用CPU时间、消耗大批量内存和占用过多的栓锁,因而有要求对SQL的I/O进行优化。优化I/O的最实惠格局就是用索引围观代替全表扫描。

4.2.1  应用基于函数的目录

    基于函数的目录(FUNCTION BASED
INDEX,简记为FBI)提供了目录计算列并在查询中运用那些索引的力量。FBI的原形是对查询所需中间结果举办预处理。假设三个FBI与查询语句中的内嵌函数完全匹配,CBO在变更查询布置时,将自行启用索引范围扫描(INDEX
RANGE SCAN)替换全表扫描(FULL TABLE
SCAN)。考察上边包车型地铁代码段并用AUTOTRACE观看成立FBI前后执行布署的生成。

    select * from emp where upper(ename)=’SCOTT’

    成立FBI前,很显明是全表扫描。 

    Execution Plan

    ……

       1    0   TABLE ACCESS (FULL) OF ‘EMPLOYEES’ (Cost=2 Card=1
Bytes=22)

    idle>CREATE INDEX EMP_UPPER_FIRST_NAME ON
EMPLOYEES(UPPER(FIRST_NAME));

    索引已开立。

    再度运营相同查询,

    Execution Plan

    ……

       1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEES’ (Cost=1
Card=1 Bytes=22)

       2    1   INDEX (RANGE SCAN) OF ‘EMP_UPPER_FIRST_NAME’
(NON-UNIQUE) (Cost=1 Card=1)

   
这一简练的例子充足表达了FBI在SQL查询优化中的作用。FBI所用的函数能够是用户本身创办的函数,该函数越繁杂,基于该函数创立FBI对SQL查询品质的优化功用越强烈。

4.2.2  应用物化视图和查询重写

   
物化视图是三个预总括结果集,在这之中普通包蕴聚集与多表连接等繁杂操作。数据库自动爱护物化视图,且随用户的须求进行刷新。查询重写机制固然用数据库中的替代对象(如物化视图)将用户提交的查询重写为完全两样但成效等价的询问。查询重写对用户透明,用户完全按不奇怪编排访问数据库的询问语句,优化程序(CBO)自动决定是不是对用户提交的询问实行重写。查询重写是增进查询质量的一种特别实惠的方法,尤其是在数据仓库环境中针对汇总、多表连接以及其余高资本的操作方面。

   
上边以一个格外不难的例子来演示物化视图和询问重写在优化SQL查询质量方面包车型大巴效用。

图片 2图片 3Code
1图片 4    select dept.deptno,dept.dname,count(*)
2图片 5    from emp,dept
3图片 6    where emp.deptno=dept.deptno
4图片 7    group by dept.deptno,dept.dname

    /

询问安顿及关键计算数据如下:

    执行陈设:

    —————————————–

    ……

       2    1   HASH JOIN (Cost=5 Card=14 Bytes=224)

       3    2   TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=2 Card=4 Bytes=52)

       4    2   TABLE ACCESS (FULL) OF ‘EMP’ (Cost=2 Card=14 Bytes=42)

    首要总计数据:

    —————————————–

        305  recursive calls

          46  consistent gets

    创设物化视图EMP_DEPT:

图片 8图片 9Code
1图片 10    create materialized view emp_dept build immediate
2图片 11    refresh on demand
3图片 12    enable query rewrite
4图片 13    as
5图片 14    select dept.deptno,dept.dname,count(*)
6图片 15    from emp,dept
7图片 16    where emp.deptno=dept.deptno
8图片 17    group by dept.deptno,dept.dname

    /

    再次实施查询,执行安顿及首要性总结数据如下:

    执行安顿:

    ————————————-

    ……

       1    0   TABLE ACCESS (FULL) OF ‘EMP_DEPT’ (Cost=2 Card=327
Bytes=11445)

    首要计算数据:

    ————————————

         79  recursive calls

         28  consistent gets

   
可知,在确立物化视图在此以前,首先实施八个表的全表扫描,然后开始展览HASH连接,再开始展览分组排序和选用操作;而建立物化视图后,CBO自动将上述复杂操作转换为对物化视图EMP_DEPT的全扫描,相关的计算数据也有了一点都不小的革新,递归调用(RECU本田UR-VSIVE
CALLS)由305降到79,逻辑I/O(CONSISTENT GETS)由46降为28。

4.2.3  将反复造访的小表读入CACHE

   
逻辑I/O总是快于物理I/O。倘诺数据库中存在被应用程序往往造访的小表,可将那些表强行读入KEEP池,从而幸免物理I/O的产生。

2  影响SQL品质的原委

   
影响SQL质量的要素众多,如开头化参数设置不创造、导入了不标准的系统及方式计算数据从而影响优化程序(CBO)的正确性判断等,那一个往往和DBA密切相关。纯粹从SQL语句出发,小编认为影响SQL质量不外乎以下多个根本原由:

    (1)在大记录集上拓展高资金操作,如运用了滋生排序的谓词等。

   
(2)过多的I/O操作(含物理I/O与逻辑I/O),最特出的正是未建立适宜的目录,导致对查询表进行全表扫描。

   
(3)处理了太多的无效记录,如在多表连接时过滤条件地方不当导致中间结果集带有了太多的不算记录。

    (4)未丰富利用数据库提供的成效,如查询的并行化处理等。

第(4)个原因处理起来相对简单。杂谈将本着前五个原因论述如何增强SQL查询语句的质量。

摘  要 
 数据库品质问题平素是决策者及技术职员共同关怀的症结,影响数据库质量的一个要害因素正是SQL查询语句的低效用。故事集首先分析了造成SQL查询语句质量低下的多个大规模原因以及SQL调优的相似步骤,然后分别指向怎么着下降I/O操作、在查询语句中如何避免对查询结果的高开支操作以及在多表连接时怎么压实查询效用展开精通析。

1  引言

   
随着网络应用不断发展,系统品质已越来越引起决策者的赏识。影响系统质量的要素居多,低效的SQL语句就是里面二个不行忽略的要害原由。故事集首先分析导致SQL质量低下的常见原因,然后分析SQL调优应遵从的形似步骤,最终从什么降低I/O、幸免对查询结果的高花费操作和多表连接中怎么样进步SQL质量进行了研究。鉴于目前ORACLE在数据库商场上的主导地位,故事集将只针对ORACLE举行研讨。

4.1  优化排序操作

   
排序的资本分外昂扬,当在查询语句中使用了滋生结果集排序的谓词时,SQL品质必然碰到震慑。

4.1.1 排序进度分析

   
当待排序数据集不是太大时,服务器在内部存款和储蓄器(排序区)完成排序操作,假诺排序需求越来越多的内部存款和储蓄器空间,服务器将展开如下处理:

    (1) 将数据分为两个小的汇聚,对每一集合举办排序。

    (2)
服务器向磁盘申请方今间和空间间,将排好序的中游结果写入一时段,再对此外的集纳举办排序。

    (3)
在拥有的聚集均排好序后,服务器再将它们举办联合获得终极的结果,假使排序区尺寸太小,合并不可能二遍到位时,将分多次拓展。

   
从上述分析可见,排序是一种拾叁分高昂的操作,它消耗多量的CPU时间和内部存款和储蓄器,触发磁盘分页和置换操作,由此若是有只怕,大家就应有在SQL语句中尽量幸免排序操作。

4.1.2  SQL中挑起排序的操作

    SQL查询语句中挑起排序的操作差不多有:O凯雷德DEENCORE BY 和GROUP BY
从句;DISTINCT修饰符;UNION、INTE昂科雷SECT、MINUS集合操作符;多表连接时的排序合并连接(SO兰德酷路泽T
ME酷路泽GE JOIN)等。

4.1.3  如何幸免排序

    1)建立合适的目录

   
对经常开始展览排序和连接操作的字段建立目录。在创制目录后,当服务器向那么些字段发出排序请求时,将直接引用索引而不开始展览排序操作;当举办等值连接查询操作时,若建立连接的字段未建立目录,服务器进行的是排序合并连接(SO奥迪Q5T
ME索罗德GE JOIN),连接操作的长河如下:

    对展开连接的四个或四个表分别开展全扫描;

    对每三个表中的行集分别开始展览全排序;

    合并排序结果。

    假使创设连接的字段已创造目录,服务器实行嵌套循环连接(NESTED LOOP
JOINS),该连接格局不须要其余排序,其进程如下:

    对驱动表展开全表扫描;

    对回到的每一行选择接二连三字段值实施索引惟一扫描;

    利用从索引围观中回到的ROWID值在从表中定位记录;

    合并主、从表中的相当记录。

    由此,建立索引可制止多数排序操作。

    2)用UNIION ALL替换UNION

   
UNION在实行表链接后会筛选掉重复的笔录,所以在表链接后会对所发出的结果集进行排序运算,删除重复的笔录再再次来到结果。大多数采用中是不会产生重复记录的,最广大的是进度表与历史表UNION
。因而,选取UNION ALL操作符替代UNION,因为UNION
ALL操作只是简短的将四个结实合并后就回来。

参考文献

[1]Thomas Kyte.Effective Oracle by Design:Design and Build
High-performance Oracle
Application[M],The McGral- Hill
Companies,Inc,2003

[2]Kevin Loney,George Koch,Oracle 9i:The Complete
Reference[M],The McGral-Hill Companies,Inc,2002

[3] Oracle9i SQL Reference release 2(9.2)[OL/M],2002.10.
http://www.oracle.com/technology/

[4] Oracle9i Data Warehousing Guide release 2(9.2) [OL/M],2002.03.
http://www.oracle.com/technology/

[5]Alexey Danchenkov,Donald Burleson,Oracle Tuning:The Definitive
Reference[OL/M],Rampant Techpress,2006.

[6] Oracle9i Database Concepts release 2(9.2) [OL/M],2002.08.
http://www.oracle.com/technology/

[7] Oracle9i supplied plsql packages and types reference release
2(9.2) [OL/M],2002.12. http://www.oracle.com/ technology/

5  结束语

   
SQL语言在数据库应用中占有十一分关键的地位,其性质的高低直接影响着全体音讯类别的可用性。随想从事电影工作响SQL质量的最重要的多少个地点动手,分析了怎么着优化SQL查询的I/O、幸免高资金的排序操作和优化多表连接。需求强调的少数是,驾驭SQL语句所缓解的难点比SQL调优本人更珍视,因而SQL调优须要系统一分配析人士、开发职员和数据库管理员密切合营。

4  SQL语句的优化

4.3  多表连接优化

   
最能体现查询复杂性的正是多表连接,多表连接操作往往要花费多量的CPU时间和内部存款和储蓄器,因而多表连接查询品质优化往往是SQL优化的主要性与困难。

4.3.1  消除此而外部连接

   
通过消除了那几个之外部连接,不仅使获得的询问更便于读取,而且品质也时时能够收获校正。一般的思路是,有以下形式的查询:

图片 18图片 19Code
1图片 20    SELECT …,OUTER_JOINED_TABLE.COLUMN
2图片 21    FROM SOME_TABLE,OUTER_JOINED_TO_TABLE
3图片 22    WHERE …=OUTER_JOINED_TO_TABLE(+)

    可转换为如下方式的询问:

   

图片 23图片 24Code
1图片 25SELECT …,(SELECT COLUMN FROM OUTER_ JOINED_TO_TABLE WHERE …)FROM SOME_TABLE;

4.3.2  谓词前推,优化中间结果

   
多表连接的习性低下多数是因为再三再四操作与过滤操作的程序不创制,抢先八分之四用户在编排多表连接查询时,总是先进行接二连三操作再选取过滤条件,那导致服务器做了太多的无用功。针对那类问题,其优化思路就是尽量将过滤谓词前推,使不符合条件的记录提前被筛选掉,只对符合条件的个别笔录举行连接处理,那样可加倍的滋长SQL查询作用。

   
如下图所示的星形模型,现要总结以来四个月进货的货物在种种销售渠道上的行销业绩。

图片 26

图2  产品销售的星形模型

    标准连接查询如下:

图片 27图片 28Code
1图片 29    Select a.prod_name,sum(b.sale_quant),
2图片 30    sum(c.sale_quant),sum(d.sale_quant)
3图片 31    From product a,tele_sale b,online_sale c,store_sale d
4图片 32    Where a.prod_id=b.prod_id and a.prod_id=c.prod_id 
5图片 33    and a.prod_id=d.prod_id And a.order_date>sysdate-90
6图片 34    Group by a.prod_id;

   
启用内嵌视图,且将规范a.order_date>sysdate-90前移,优化后代码如下:

   

图片 35图片 36Code
 1图片 37    Select a.prod_name,b.tele_sale_sum,c.online_sale_sum,d.store_sale_sum From product a,
 2图片 38    (select sum(sal_quant) tele_sale_sum from product,tele_sale
 3图片 39    Where product.order_date>sysdate-90 and product.prod_id =tele_sale.prod_id) b,
 4图片 40    (select sum(sal_quant) online_sale_sum
 5图片 41    from product,tele_sale
 6图片 42    Where product.order_date>sysdate-90 and product.prod_id =online_sale.prod_id) c,
 7图片 43    (select sum(sal_quant) store_sale_sum 
 8图片 44    from product,store_sale
 9图片 45    Where product.order_date>sysdate-90 and product.prod_id =store_sale.prod_id) d, 
10图片 46
11图片 47    Where a.prod_id=b.prod_id and 
12图片 48    a.prod_id=c.prod_id and a.prod_id=d.prod_id;

    关键词   ORACLE;SQL;优化;连接

相关文章