数据库查询优化

数据库课程设计-数据库查询优化概述5.1查询处理理论5.2查询优化理论5.3代数优化5.4物理优化5.5基于Oracle的SQL优化策略5.1查询处理理论-查询处理阶段RDBMS查询处理阶段：1.查询分析2.查询检查3.查询优化4.查询执行5.1查询处理理论-查询处理步骤5.1查询处理理论-查询处理步骤1、查询分析对查询语句进行扫描、词法分析和语法分析从查询语句中识别出语言符号进行语法检查和语法分析5.1查询处理理论-查询处理步骤2、查询检查根据数据字典对合法的查询语句进行语义检查根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查检查通过后把SQL查询语句转换成等价的关系代数表达式RDBMS一般都用查询树(语法分析树)来表示扩展的关系代数表达式5.1查询处理理论-查询处理步骤3、查询优化查询优化：选择一个高效执行的查询处理策略查询优化分类：代数优化：指关系代数表达式的优化物理优化：指存取路径和底层操作算法的选择查询优化方法选择的依据：基于规则(rulebased)基于代价(costbased)基于语义(semanticbased)5.1查询处理理论-查询处理步骤4、查询执行依据优化器得到的执行策略生成查询计划代码生成器(codegenerator)生成执行查询计划的代码5.1查询处理理论-示例选择操作的实现［例1］Select*fromswhere<条件表达式>考虑<条件表达式>的几种情况：C1：无条件；C2：sno＝'200215121'；C3：age>20；C4：dno＝'CS'ANDage>20；5.1查询处理理论-示例选择操作典型实现方法：1.简单的全表扫描方法对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出适合小表，不适合大表2.索引扫描方法适合选择条件中的属性上有索引(例如B+树索引或Hash索引)通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组5.1查询处理理论-示例［例1-C2］以C2为例，Sno＝’200215121’，并且Sno上有索引使用索引得到Sno为‘200215121’

元组的指针通过元组指针在S表中检索到该学生［例1-C3］以C3为例，Sage>20，并且Sage上有B+树索引使用B+树索引找到Sage＝20的索引项，以此为入口点在B+树的顺序集上得到Sage>20的所有元组指针通过这些元组指针到S表中检索到所有年龄大于20的学生。5.1查询处理理论-示例［例1-C4］以C4为例，Sdept＝‘CS’ANDSage>20，如果Sdept和Sage上都有索引：算法一：分别用上面两种方法分别找到Sdept＝‘CS’的一组元组指针和Sage>20的另一组元组指针求这2组指针的交集算法二：找到Sdept＝‘CS’的一组元组指针，通过这些元组指针到S表中检索对得到的元组检查另一些选择条件(如Sage>20)是否满足把满足条件的元组作为结果输出。5.1查询处理理论-连接操作连接操作是查询处理中最耗时的操作之一本节只讨论等值连接(或自然连接)最常用的实现算法［例2］SELECT*FROMS，SCWHERES.Sno=SC.Sno；5.1查询处理理论-连接操作1.嵌套循环方法(nestedloop)2.排序-合并方法(sort-mergejoin或mergejoin)3.索引连接(indexjoin)方法4.HashJoin方法5.1查询处理理论-连接操作1.嵌套循环方法(nestedloop)对外层循环(S)的每一个元组(s)，检索内层循环(SC)中的每一个元组(sc)检查这两个元组在连接属性(sno)上是否相等如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止5.1查询处理理论-连接操作2.排序-合并方法(sort-mergejoin或mergejoin)适合连接的诸表已经排好序的情况排序－合并连接方法的步骤：如果连接的表没有排好序，先对S表和SC表按连接属性Sno排序取S表中第一个Sno，依次扫描SC表中具有相同Sno的元组5.1查询处理理论-连接操作5.1查询处理理论-连接操作排序－合并连接方法的步骤（续）：当扫描到Sno不相同的第一个SC元组时，返回S表扫描它的下一个元组，再扫描SC表中具有相同Sno的元组，把它们连接起来重复上述步骤直到S表扫描完5.1查询处理理论-连接操作S表和SC表都只要扫描一遍如果2个表原来无序，执行时间要加上对两个表的排序时间对于2个大表，先排序后使用sort-mergejoin方法执行连接，总的时间一般仍会大大减少5.1查询处理理论-连接操作3.索引连接(indexjoin)方法，步骤：①在SC表上建立属性Sno的索引，如果原来没有该索引②对S中每一个元组，由Sno值通过SC的索引查找相应的SC元组③把这些SC元组和S元组连接起来循环执行②③，直到S表中的元组处理完为止5.1查询处理理论-连接操作4.HashJoin方法把连接属性作为hash码，用同一个hash函数把S和SC中的元组散列到同一个hash文件中步骤：划分阶段(partitioningphase)：对包含较少元组的表(比如S)进行一遍处理把它的元组按hash函数分散到hash表的桶中试探阶段(probingphase)：也称为连接阶段(joinphase)

对另一个表(SC)进行一遍处理把SC的元组散列到适当的hash桶中把元组与桶中所有来自S并与之相匹配的元组连接起来5.1查询处理理论-连接操作上面hashjoin算法前提：假设两个表中较小的表在第一阶段后可以完全放入内存的hash桶中以上的算法思想可以推广到更加一般的多个表的连接算法上5.2查询优化理论查询优化在关系数据库系统中有着非常重要的地位关系查询优化是影响RDBMS性能的关键因素由于关系表达式的语义级别很高，使关系系统可以从关系表达式中分析查询语义，提供了执行查询优化的可能性5.2查询优化理论-概述查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较好的效率，而且在于系统可以比用户程序的“优化”做得更好(1)优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息(2)如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。5.2查询优化理论-概述(3)优化器可以考虑数百种不同的执行计划，程序员一般只能考虑有限的几种可能性。(4)优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术5.2查询优化理论-概述RDBMS通过某种代价模型计算出各种查询执行策略的执行代价，然后选取代价最小的执行方案集中式数据库执行开销主要包括：磁盘存取块数(I/O代价)处理机时间(CPU代价)查询的内存开销I/O代价是最主要的分布式数据库总代价=I/O代价+CPU代价+内存代价＋通信代价5.2查询优化理论-概述查询优化的总目标：选择有效的策略求得给定关系表达式的值使得查询代价最小(实际上是较小)5.2查询优化理论-示例[例3]求选修了2号课程的学生姓名。用SQL表达：SELECTS.snameFROMS，SCWHERES.sno=SC.snoANDSC.cno=‘2’；假定学生-课程数据库中有1000个学生记录，10000个选课记录其中选修2号课程的选课记录为50个5.2查询优化理论-示例系统可以用多种等价的关系代数表达式来完成这一查询Q1=sname(s.sno=sc.sno∧o='2'(S×SC))Q2=

sname(

o='2'(SSC))Q3=

sname(S

o='2'(SC))5.2查询优化理论-示例一、第一种情况Q1=sname(s.sno=sc.sno∧o='2'(S×SC))1.计算笛卡尔积把S和SC的每个元组连接起来的做法：在内存中尽可能多地装入某个表(如S表)的若干块，留出一块存放另一个表(如SC表)的元组。把SC中的每个元组和S中每个元组连接，连接后的元组装满一块后就写到中间文件上从SC中读入一块和内存中的S元组连接，直到SC表处理完。再读入若干块S元组，读入一块SC元组重复上述处理过程，直到把S表处理完5.2查询优化理论-示例设一个块能装10个S元组或100个SC元组，在内存中存放5块S元组和1块SC元组，则读取总块数为1000/10+1000/(10*5)*10000/100=100+20*100=2100块其中，读S表100块。读SC表20遍，每遍100块。若每秒读写20块，则总计要花105s连接后的元组数量为103×104=107，假设每块能装10个元组，则写出这些块（写中间文件）要用106/20=5×104s5.2查询优化理论-示例2.作选择操作依次读入连接后的元组，按照选择条件选取满足要求的记录假定内存处理时间忽略。读取中间文件花费的时间(同写中间文件一样)需5×104s满足条件的元组假设仅50个，均可放在内存5.2查询优化理论-示例3.作投影操作把第2步的结果在Sname上作投影输出，得到最终结果第一种情况下执行查询的总时间≈105+2×5×104≈105s所有内存处理时间均忽略不计5.2查询优化理论-示例二、第二种情况Q2=sname(σo='2'(SSC))1.计算自然连接执行自然连接，读取S和SC表的策略不变，总的读取块数仍为2100块花费105s自然连接的结果比第一种情况大大减少，为104个写出这些元组时间为104/10/20=50s，为第一种情况的千分之一2.读取中间文件块，执行选择运算，花费时间也为50s。3.把第2步结果投影输出。第二种情况总的执行时间≈105+50+50≈205s5.2查询优化理论-示例三、第三种情况Q3=

sname(Sσo='2'(SC))1.先对SC表作选择运算，只需读一遍SC表，存取100块花费时间为5s，因为满足条件的元组仅50个，不必使用中间文件。2.读取S表，把读入的S元组和内存中的SC元组作连接。也只需读一遍S表共100块，花费时间为5s。3.把连接结果投影输出。第三种情况总的执行时间≈5+5≈10s5.2查询优化理论-示例假如SC表的Cno字段上有索引第一步就不必读取所有的SC元组而只需读取Cno=‘2’的那些元组(50个)存取的索引块和SC中满足条件的数据块大约总共3～4块若S表在Sno上也有索引第二步也不必读取所有的S元组因为满足条件的SC记录仅50个，涉及最多50个S记录读取S表的块数也可大大减少总的存取时间将进一步减少到数秒5.2查询优化理论-示例把代数表达式Q1变换为Q2、Q3，即有选择和连接操作时，先做选择操作，这样参加连接的元组就可以大大减少，这是代数优化在Q3中SC表的选择操作算法有全表扫描和索引扫描2种方法，经过初步估算，索引扫描方法较优对于S和SC表的连接，利用S表上的索引，采用indexjoin代价也较小，这就是物理优化5.3代数优化5.3.1关系代数表达式等价变换规则5.3.2查询树的启发式优化5.3.1关系代数表达式等价变换规则代数优化策略：通过对关系代数表达式的等价变换来提高查询效率关系代数表达式的等价：指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的两个关系表达式E1和E2是等价的，可记为E1≡E25.3.1关系代数表达式等价变换规则常用的等价变换规则：1.连接、笛卡尔积交换律。设E1和E2是关系代数表达式，F是连接运算的条件，则有E1×E2≡E2×E1E1E2≡E2E1E1E2≡E2E12.连接、笛卡尔积的结合律。设E1，E2，E3是关系代数表达式，F1和F2是连接运算的条件，则有(E1×E2)×E3≡E1×(E2×E3)(E1E2)E3≡E1(E2E3)(E1E2)E3≡E1(E2E3)5.3.1关系代数表达式等价变换规则3.投影的串接定律A1,A2,…,An(B1,B2,…,Bm(E))≡A1,A2,…,An(E)这里，E是关系代数表达式，Ai(i=1，2，…，n)，Bj(j=1，2，…，m)是属性名且{A1，A2，…，An}构成{B1，B2，…，Bm}的子集。4.选择的串接定律F1(F2(E))≡F1F2(E)这里，E是关系代数表达式，F1、F2是选择条件。选择的串接律说明选择条件可以合并。这样一次就可检查全部条件。5.3.1关系代数表达式等价变换规则5.选择与投影操作的交换律σF(A1,A2,…,An(E))≡A1,A2,…,An(σF(E))选择条件F只涉及属性A1，…，An。若F中有不属于A1，…，An的属性B1，…，Bm则有更一般的规则：A1,A2,…,An(σF(E))≡A1,A2,…,An(σF(A1,A2,…,An,

B1,B2,…,Bm(E)))5.3.1关系代数表达式等价变换规则6.选择与笛卡尔积的交换律。如果F中涉及的属性都是E1中的属性，则σF(E1×E2)≡σF(E1)×E2如果F=F1∧F2，并且F1只涉及E1中的属性，F2只涉及E2中的属性，则由上面的等价变换规则1，4，6可推出：σF(E1×E2)≡σF1(E1)×σF2(E2)若F1只涉及E1中的属性，F2涉及E1和E2两者的属性，则仍有σF(E1×E2)≡σF1(σF2(E1)×E2)它使部分选择在笛卡尔积前先做。5.3.1关系代数表达式等价变换规则7.选择与并的分配律设E=E1∪E2，E1，E2有相同的属性名，则σF(E1∪E2)≡σF(E1)∪σF(E2)8.选择与差运算的分配律若E1与E2有相同的属性名，则σF(E1-E2)≡σF(E1)-σF(E2)9.选择对自然连接的分配律σF(E1E2)≡σF(E1)σF(E2)F只涉及E1与E2的公共属性5.3.1关系代数表达式等价变换规则10.投影与笛卡尔积的分配律设E1和E2是两个关系表达式，A1，…，An是E1的属性，B1，…，Bm是E2的属性，则A1,A2,…,An,

B1,B2,…,Bm(E1×E2)≡A1,A2,…,An(E1)×B1,B2,…,Bm(E2)11.投影与并的分配律设E1和E2有相同的属性名，则A1,A2,…,An(E1∪E2)≡A1,A2,…,An(E1)∪A1,A2,…,An(E2)5.3.2查询树的启发式优化典型的启发式规则：1.选择运算应尽可能先做。在优化策略中这是最重要、最基本的一条2.把投影运算和选择运算同时进行如有若干投影和选择运算，并且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系5.3.2查询树的启发式优化3.把投影同其前或其后的双目运算结合起来4.把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算5.找出公共子表达式如果这种重复出现的子表达式的结果不是很大的关系并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件是合算的5.3.2查询树的启发式优化遵循这些启发式规则，应用上一节的等价变换公式来优化关系表达式的算法。算法：关系表达式的优化输入：一个关系表达式的查询树输出：优化的查询树方法：(1)利用等价变换规则4把形如σF1∧F2∧…∧Fn(E)变换为σF1(σF2(…(σFn(E))…))。(2)对每一个选择，利用等价变换规则4～9尽可能把它移到树的叶端。5.3.2查询树的启发式优化(3)对每一个投影利用等价变换规则3，5，10，11中的一般形式尽可能把它移向树的叶端。注意：等价变换规则3使一些投影消失规则5把一个投影分裂为两个，其中一个有可能被移向树的叶端(4)利用等价变换规则3～5把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。使多个选择或投影能同时执行，或在一次扫描中全部完成5.3.2查询树的启发式优化(5)把上述得到的语法树的内节点分组。每一双目运算(×，，∪，-)和它所有的直接祖先为一组(这些直接祖先是(σ，π运算)。如果其后代直到叶子全是单目运算，则也将它们并入该组但当双目运算是笛卡尔积(×)，而且后面不是与它组成等值连接的选择时，则不能把选择与这个双目运算组成同一组，把这些单目运算单独分为一组5.3.2查询树的启发式优化［例4］下面给出［例3］中SQL语句的代数优化示例。(1)把SQL语句转换成查询树，如下图所示5.3.2查询树的启发式优化为了使用关系代数表达式的优化法，假设内部表示是关系代数语法树，则上面的查询树如下图所示。5.3.2查询树的启发式优化(2)对查询树进行优化利用规则4、6把选择σSC.Cno=‘2’移到叶端，查询树便转换成下图所示的优化的查询树。这就是Q3的查询树表示5.4物理优化代数优化改变查询语句中操作的次序和组合，不涉及底层的存取路径对于一个查询语句有许多存取方案，它们的执行效率不同，仅仅进行代数优化是不够的物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划5.4物理优化选择的方法：基于规则的启发式优化基于代价估算的优化两者结合的优化方法5.4物理优化5.5.1基于启发式规则的存取路径选择优化5.5.2基于代价的优化5.5.1基于启发式规则的存取路径选择优化一、选择操作的启发式规则二、连接操作的启发式规则5.5.1基于启发式规则的存取路径选择优化一、选择操作的启发式规则:1.对于小关系，使用全表顺序扫描，即使选择列上有索引对于大关系，启发式规则有：2.对于选择条件是主码＝值的查询查询结果最多是一个元组，可以选择主码索引一般的RDBMS会自动建立主码索引。5.5.1基于启发式规则的存取路径选择优化3.对于选择条件是非主属性＝值的查询，并且选择列上有索引要估算查询结果的元组数目如果比例较小(<10%)可以使用索引扫描方法否则还是使用全表顺序扫描5.5.1基于启发式规则的存取路径选择优化4.对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引要估算查询结果的元组数目如果比例较小(<10%)可以使用索引扫描方法否则还是使用全表顺序扫描5.5.1基于启发式规则的存取路径选择优化5.对于用AND连接的合取选择条件如果有涉及这些属性的组合索引优先采用组合索引扫描方法如果某些属性上有一般的索引则可以用［例1-C4］中介绍的索引扫描方法否则使用全表顺序扫描。6.对于用OR连接的析取选择条件，一般使用全表顺序扫描5.5.1基于启发式规则的存取路径选择优化二、连接操作的启发式规则：1.如果2个表都已经按照连接属性排序选用排序-合并方法2.如果一个表在连接属性上有索引选用索引连接方法3.如果上面2个规则都不适用，其中一个表较小选用Hashjoin方法5.5.1基于启发式规则的存取路径选择优化4.可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数(b)较少的表，作为外表(外循环的表)。理由：设连接表R与S分别占用的块数为Br与Bs连接操作使用的内存缓冲区块数为K分配K-1块给外表如果R为外表，则嵌套循环法存取的块数为Br+(Br/(K-1))Bs显然应该选块数小的表作为外表5.5.2基于代价的优化启发式规则优化是定性的选择，适合解释执行的系统解释执行的系统，优化开销包含在查询总开销之中编译执行的系统中查询优化和查询执行是分开的可以采用精细复杂一些的基于代价的优化方法5.5.2基于代价的优化一、统计信息二、代价估算示例5.5.2基于代价的优化一、统计信息基于代价的优化方法要计算各种操作算法的执行代价，与数据库的状态密切相关数据字典中存储的优化器需要的统计信息：1.对每个基本表该表的元组总数(N)元组长度(l)占用的块数(B)占用的溢出块数(BO)5.5.2基于代价的优化2.对基表的每个列该列不同值的个数(m)选择率(f)如果不同值的分布是均匀的，f＝1/m如果不同值的分布不均匀，则每个值的选择率＝具有该值的元组数/N该列最大值该列最小值该列上是否已经建立了索引索引类型(B+树索引、Hash索引、聚集索引)5.5.2基于代价的优化3.对索引(如B+树索引)索引的层数(L)不同索引值的个数索引的选择基数S(有S个元组具有某个索引值)索引的叶结点数(Y)5.5.2基于代价的优化二、代价估算示例1.全表扫描算法的代价估算公式如果基本表大小为B块，全表扫描算法的代价cost＝B如果选择条件是码＝值，那么平均搜索代价cost＝B/25.5.2基于代价的优化2.索引扫描算法的代价估算公式如果选择条件是码＝值如［例1-C2］，则采用该表的主索引若为B+树，层数为L，需要存取B+树中从根结点到叶结点L块，再加上基本表中该元组所在的那一块，所以cost=L+1如果选择条件涉及非码属性如［例1-C3］，若为B+树索引，选择条件是相等比较，S是索引的选择基数(有S个元组满足条件)最坏的情况下，满足条件的元组可能会保存在不同的块上，此时，cost=L+S5.5.2基于代价的优化如果比较条件是＞，＞＝，＜，＜＝操作假设有一半的元组满足条件就要存取一半的叶结点通过索引访问一半的表存储块cost=L+Y/2+B/2如果可以获得更准确的选择基数，可以进一步修正Y/2与B/25.5.2基于代价的优化3.嵌套循环连接算法的代价估算公式5.5.1中已经讨论过了嵌套循环连接算法的代价cost＝Br+BrBs/(K-1)如果需要把连接结果写回磁盘，cost＝Br+Bs/(K-1)Br+(Frs*Nr*Ns)/Mrs其中Frs为连接选择性(joinselectivity)，表示连接结果元组数的比例Mrs是存放连接结果的块因子，表示每块中可以存放的结果元组数目。5.5.2基于代价的优化4.排序-合并连接算法的代价估算公式如果连接表已经按照连接属性排好序，则cost＝Br+Bs+(Frs*Nr*Ns)/Mrs。如果必须对文件排序需要在代价函数中加上排序的代价对于包含B个块的文件排序的代价大约是(2*B)+(2*B*log2B)5.5基于Oracle的SQL优化Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的(相对而言)。ORACLE的优化器共有3种:a.COST(基于成本)b.RULE(基于规则)c.CHOOSE(选择性)5.5基于Oracle的SQL优化RBO是根据ORACLE的内定规则实现的，索引就是ORACLE的内定规则；而对于CBO，由于是基于代价的，也就是ORACLE机器的资源了，比如CPU和内存等优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、多少行、每行的长度等信息。这些统计信息起初在库内是没有的,是在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。5.5基于Oracle的SQL优化在安装时，init.ora文件中设定OPTIMIZER_MODE参数，如果我们设为OPTIMIZER_MODE=RULE，它就会按RBO方式进行，反之按CBO了Choose:如果没有设定OPTIMIZER_MODE参数，默认的情况下Oracle用的便是这种方式。指的是当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。并不是所有的查询都需要索引，比如小表5.5基于Oracle的SQL优化访问表的方式a.全表扫描全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描b.通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.5.5基于Oracle的SQL优化共享SQL语句为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享。当执行一个SQL语句时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。5.5基于Oracle的SQL优化共享SQL语句a.字符级的比较:当前被执行的语句和共享池中的语句必须完全相同b.两个语句所指的对象必须完全相同c.两个SQL语句中必须使用相同的名字的绑定变量(bindvariables)ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).5.5基于Oracle的SQL优化共享SQL语句-字符级比较：当前被执行的语句和共享池中的语句必须完全相同.SELECT*FROMEMP;此SQL与下列SQL均不同：SELECT*fromEMP;Select*FromEmp;SELECT*FROMEMP;5.5基于Oracle的SQL优化共享SQL语句-对象：两个SQL语句所指的对象必须完全相同考虑一下下列SQL语句能否在这两个用户之间共享selectmax(sal_cap)fromsal_limit;selectcount(*)fromwork_citywheresdesclike'NEW%';selecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id用户名对象如何访问Jacksal_limitprivate

synonymWork_citypublic

synonymPlant_detailpublic

synonymJillsal_limitprivate

synonymWork_citypublic

synonymPlant_detailtable

owner5.5基于Oracle的SQL优化共享SQL语句-绑定变量：两个SQL语句中必须使用相同的名字的绑定变量下述两组SQL是否可以共享？a.selectpin,namefrompeoplewherepin=:blk1.pin;selectpin,namefrompeoplewherepin=:blk1.pin;b.selectpin,namefrompeoplewherepin=:blk1.ot_ind;

selectpin,namefrompeoplewherepin=:blk1.ov_ind;

5.5基于Oracle的SQL优化选择最有效率的表名顺序(只在基于规则的优化器中有效)ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础。当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.5.5基于Oracle的SQL优化WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.5.5基于Oracle的SQL优化WHERE子句中的连接顺序(低效,执行时间156.3秒)SELECT…FROMEMPEWHERESAL>50000ANDJOB=‘MANAGER’AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.6秒)SELECT…FROMEMPEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL>50000ANDJOB=‘MANAGER’;5.5基于Oracle的SQL优化SELECT子句中避免使用*当你想在SELECT子句中列出所有的列时,使用动态SQL列引用*是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.5.5基于Oracle的SQL优化减少访问数据库的次数当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.5.5基于Oracle的SQL优化减少访问数据库的次数例如：以下有三种方法可以检索出雇员号等于0342或0291的职员.方法1(最低效)SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=291;5.5基于Oracle的SQL优化方法2(次低效)DECLARECURSORC1(E_NONUMBER)ISSELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=E_NO;BEGINOPENC1(342);FETCHC1INTO…,..,..;…..OPENC1(291);FETCHC1INTO…,..,..;CLOSEC1;END;5.5基于Oracle的SQL优化方法3(高效)SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;5.5基于Oracle的SQL优化用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML5.5基于Oracle的SQL优化尽量多使用COMMIT只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redologbuffer中的空间d.ORACLE为管理上述3种资源中的内部花费在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼，在这种情况下，事务的完整性重要。5.5基于Oracle的SQL优化用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.5.5基于Oracle的SQL优化低效:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION<>‘SYDNEY’ANDREGION<>'PERTH'

5.5基于Oracle的SQL优化高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION<>‘SYDNEY’ANDREGION<>‘PERTH’

GROUPBYREGIONHAVING中的条件一般用于对一些集合函数的比较,如COUNT()等等.除此而外,一般的条件应该写在WHERE子句中5.5基于Oracle的SQL优化减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询.低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)5.5基于Oracle的SQL优化高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)

5.5基于Oracle的SQL优化使用表的别名(Alias)当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.5.5基于Oracle的SQL优化用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在子查询结果集中元组数量较多时，使用EXISTS(或NOTEXISTS)通常将提高查询的效率.5.5基于Oracle的SQL优化低效:SELECT*FROMEMP(基础表) WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB')

5.5基于Oracle的SQL优化高效:SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDEXISTS(SELECT'X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC='MELB')5.5基于Oracle的SQL优化用表连接替换EXISTS通常来说,采用表连接的方式比EXISTS更有效率(更高效)SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT=‘A’;5.5基于Oracle的SQL优化用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率.实际上,ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证.除了那些LONGRAW或者BLOB数据类型,你可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.

5.5基于Oracle的SQL优化用索引提高效率虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.5.5基于Oracle的SQL优化基础表的选择基础表(DrivingTable)是指被最先访问的表(通常以全表扫描的方式被访问).根据优化器的不同,SQL语句中基础表的选择是不一样的.如果你使用的是CBO(COSTBASEDOPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.如果你用RBO(RULEBASEDOPTIMIZER),并且所有的连接条件都有索引对应,在这种情况下,基础表就是FROM子句中列在最后的那个表.5.5基于Oracle的SQL优化基础表的选择SELECTA.NAME,B.MANAGERFROMWORKERA,LODGINGBWHEREA.LODGING=B.LODING;由于LODGING表的LODING列上有一个索引,而且WORKER表中没有相比较的索引,WORKER表将被作为查询中的基础表.5.5基于Oracle的SQL优化多个平等的索引当SQL语句的执行路径可以使用分布在多个表上的多个索引时,ORACLE会同时使用多个索引并在运行时对它们的记录进行合并,检索出仅对全部索引有效的记录.在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引.然而这个规则只有当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较.这种子句在优化器中的等级是非常低的.如果不同表中两个想同等级的索引将被引用,FROM子句中表的顺序将决定哪个会被率先使用.FROM子句中最后的表的索引将有最高的优先级.如果相同表中两个想同等级的索引将被引用,WHERE子句中最先被引用的索引将有最高的优先级.5.5基于Oracle的SQL优化多个平等的索引举例:DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.SELECTENAMEFROMEMPWHER