数据库程序员面试分类真题14.docx

《数据库程序员面试分类真题14.docx》由会员分享，可在线阅读，更多相关《数据库程序员面试分类真题14.docx（24页珍藏版）》请在三一办公上搜索。

1、数据库程序员面试分类真题14倚答题1. SQ1.语句的执行过程有哪些？正确答案：Oracle数据库里SQ1.语句的执行过程可以如卜图所示。在上图中，可以看到SQ1.语句的执行过程大致分为以卜几个步骤：1）当用户提交待执行的目标SQ1.后，Oracle首先会对目标SQ1.进行解析。在这个过程中，OraCIe会先执行对目标SQ1.的语法、语义和权限的检查：语法检查（SyntaXCheCk）是检查目标SQ1.的拼写是否正确，例如，错将关键字“FRO字写成“FORM”,将SE1.ECT”写成“SE1.字”等。语义检查（SemantiCCheCk）是检查SQ1.语句中的访问对象是否存在及该用户是否具备相

2、应的权限。2）如果目标SQ1.不能通过上述语法、语义和权限的检查，那么该目标SQ1.将解析失败。如果目标SQ1.能通过上述检杳，那么接下来OraCIe就会去SGA的共享池中的库缓存中查找是否存在匹配的共享游标。如果找到了匹配的共享游标，那么Oracle就会把存储于该共享游标中的解析树和执行计划直接拿过来重用，这相当于跳过了后续的“查询转换”和“查询优化”这两个步骤，直接进入“实际执行”阶段。3）如果找不到匹配的共享游标，就意味着此时没有可以被重用的解析树和执行计划，那么接下来整个执行过程就进入查询转换这一步。在查询转换过程中，OraCIe会根据一些规则来决定是否对目标SQ1.执行查询转换，这些

3、规则在OraCIe不同的版本里不尽相同。在OraCle9i中，查询转换是独立于优化器的，它与优化器的类型无关，但是从OraeleIog开始，Oracle会对某些类型的查询转换（比如子查询展开、复杂视图合并等）分别计算经过查询转换后的等价改写SQ1.的成本和原始SQ1.的成本。只有当等价改写后SQ1.的成本值小于未经过查询转换的原始SQ1.的成本值时，OraCIe才会对目标SQ1.执行查询转换。在执行完查询转换后，原目标SQ1.可能就被OraClC改写了，当然也有可能没有改写O4）接下来，OraCIe就正式进入了查询优化这个步骤。在这个步骤里，Oracle会根据不同的优化器类型（CBo或RBO）

4、采用不同的判断原则，从执行完查询转换后得到的目标SQ1.的诸多可能的执行路径中选择一条效率最高的路径来作为其执行计划，即查询优化的输入就是执行完查询转换后得到的等价改写SQ1.,其输出就是该目标SQ1.的最终执行计划。5）在得到了目标SQ1.的执行计划后，接下来OraeIe就会根据执行计划去实际执行该SQ1.,并将执行结果返回给用户。考点游标2. 绑定变量的适用场合有哪些？正确答案：对于绑定变量应该根据系统的类型来决定是否使用绑定变量，具体如下：1）在高并发的O1.TP系统中，SQ1.语句肃宜执行频度高，但处理的数据量较少，结果集也相对较小，尤其是使用表上的索引来缩小中间结果集，其解析时间通常

5、会接近或高于执行时间，因此，在该场合一定要使用绑定变量，并且最好是使用批量绑定，因为这样可以有效降低系统硬解析的数量，这也是O1.TP类型的系统在数据库端具备良好的性能和可扩展性的前提条件。2）在O1.AP/DSS系统中，SQ1.语句执行次数相对较少，但返回的数据量较大，其SQ1.语句执行时间远高于其解析时间，硬解析对系统性能的影响是微乎其微的，因此，使用绑定变量对于总的执行时间影响不大，对系统性能的提升也非常有限。3）对于O1.AP和O1.TP混合型的应用系统，如果有循环，不管这个循环是在前台代码还是在后台P1./SQ1.代码中，循环内部的SQ1.语句一定要使用绑定变量，并且最好是使用批量绑

6、定；至于循环外部的SQ1.语句，可以不使用绑定变量O需要注意的是，对于实际的数据库对象，例如表、视图等，不能使用绑定变量替换，只能替换字面量。如果对象名是在运行时生成的，那么需要对其用字符串拼接，同时，SQ1.只会匹配已经在共享池中存在且相同的对象名。考点绑定变量3. 什么是绑定变量娅探？正确答窠：目标SQ1.若不使用绑定变量，则当具体输入值一旦发生了变化，目标SQ1.的SQ1.文本就会随之发生变化，这样OraCIe就能很容易地计算出对应Selectivity和CardinaIily的值，进而据此来选择执行计划。但对于使用了绑定变量的目标SQ1.而言，情况就完全不一样了，因为现在无论对应绑定变

7、量的具体输入值是什么，目标SQ1.的SQ1.文本都是一模一样的。对于使用了绑定变量的目标SQ1.而言，Oracle可以选择如下两种方法来决定其执行计划：D使用绑定变量窥探(BindPeeking),2)如果不使用绑定变量窥探，那么对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率(例如5蛤。绑定变量窥探是在OraCIe9i中引入的，是否启用绑定变量窥探受隐含参数“OPTIMPEEKUSERBINDS的控制，该参数的默认值是TRUE,表示在Oracle9i及其后续的版本中，默认启用绑定变量婉探。考点绑定变量4. 如何得到已执行的目标SQ1.中的绑定变量的值？正确答窠：当

8、Oracle解析和执行含有绑定变量的目标SQ1.时，如果满足如下两个条件之一，那么该SQ1.中的绑定变量的具体输入值就会被Oracle捕获：1)当含有绑定变量的目标SQ1.以硬解析的方式被执行时。2)当含有绑定变量的目标SQ1.以软解析或软软解析的方式重熨执行时，OraCle在默认情况卜至少得间隔15nin才会捕获一次。这个15nin受隐含参数“CURSORBINDCAPTUREINTERVA1.控制，默认值为900s,即15min0需要注意的是，Oracle只会捕获那些位于目标SQ1.的WHERE条件中的绑定变量的具体输入值，而对于那些使用了绑定变量的INSERT语句，不管该INSERT语句

9、是否是以硬解析的方式执行，Oracle始终不会捕获INSEKT语句的H1.UES了句中对应绑定变量的具体输入值。查询视图V$SQ1._B1ND_CAPTIRE可以得到已执行目标SQ1.中绑定变量的具体输入值。如果V$SQjBIND/APTURE中查不到，那么有可能对应的SharedCurrsor己经从SharedPool中被清除了，这时候可以尝试从AWR相关的数据字典表DBA_HIST_SQ1.STAT或DBA_HIST_SQ1.BIND中查询。考点而定变量5. 什么是游标共享？正确答案：游标共享(CUrSOrSharing)是指共享游标(SharedCUrSOr)之间的共享，游标共享可以实现

10、重用存储在子游标(ChildCUrSor)中的解析树和执行计划而不用从头开始做硬解析，从而提高系统性能。特别是对于同一类型的目标SQ1.更应该实现游标共享，而使用绑定变量就可以实现游标共享。游标共享分为常规游标共享和自适应游标共享。考点绑定变量6. 获取执行计划有哪几种方法？正确答案：对于这几种获取执行冲划的方法有如下结论：1)若目标SQ1.需要执行很长时间才能返回结果，则推荐使用EXP1.AlNP1.ANFOR来获取执行计划。2)若要查询目标SQ1.的所有子游标的执行计划，则推荐使用DBMSXP1.N.DISPUYCURSOR(,&SQIJD*,NU1.1.,ADVANCEDA1.1.STA

11、TS)或awrsqrpt.sql来获取执行计划。3)若要分析SQ1.语句的内部调用详情，则推荐使用10046事件。4)若想确保看到真实的执行计划，则不能使用EXP1.ANP1.ANFOR和SETutotrcetrceonlyexplain。5)若想获取到表的访问次数，则推荐/*+GATHER_P1.AN_STATISTICS*/06)若数据库版本大于10g,则对执行时间较长的SQ1.语句推荐使用SQ1.实时监控特性查看html报告。考点执行计划7. 如何得到真实的执行计划？正确答案：在Oracle数据库中判断得到的执行计划是否准确，就是看目标SQ1.是否被真正执行过，真正执行过的SQ1.所对应

12、的执行计划就是准确的，反之则有可能不准，因此，通过10046事件及如下的几种方式得到的执行计划必最准确的，而从其他方式获取到的执行计划都有可能不准确。SE1.ECT*1ROMTAB1.E(DBMS_XP1.AN.DlSP1.ACURSOR(NU1.1.,NU1.1.,ADVANCEDA1.1.STATS,):SE1.ECT*1ROMTAB1.E(DBMS_XP1.AN.DISP1.AY_CURSOR(,sql.idhash-value,CHI1.D_CURSOR_NUMBER,ADVANC印A1.1.STTS,);SE1.ECT*FROMTAB1.E(DBMS-XP1.AN.DISP1.AY.

13、AWR(,sql.id,);这里需要注意的是，虽然SQI.*Plus的AUTOTRACE功能有部分是真实执行了SQ1.语句的(例如所有DM1.语句),但是由于该命令所显示的执行计划来源于调用EKP1.AlNP1.AN命令，所以，其得到的执行计划依然可能不准确(特别是在使用了绑定变量的情况卜考点执行计划8. 如何固定SQ1.执行计划？正确答案：在实际项目中，通常在开发环境下，一些SQ1.执行没有任何功能问题，而当生产环境或生产环境的数据量发生较大的变化时，其SQ1.的执行效率非常低。此时如果更改SQ1.,那么可能需要重新修改源程序以及重新编译程序。如果修改源程序的成本比较大，那么可以使用一些方法

14、在不改变源应用程序的情况下更改特定SQ1.的执行计划并固定下来。因为在SQ1.语句的执行计划发生更改时，可能存在性能风险。SQ1.计划发生更改的原因有很多，如优化程序版本、优化程序统计信息、优化程序参数、方案定义、系统设计和SQ1.概要文件创建等。有4种方式可以固定和控制执行计划，分别是使用HinI(提示)、OUT1.INE(存储概要)、SQ1.PROFl1.E(SQ1.概要文件)和SPM(SQ1.PlanManagemem,SQ1.计划管理)O考点执行计划9. 什么是半连接(SemiJoin)?正确答案：半连接(SemiJOin)是一种特殊的连接类型，当执行了查询展开时，OraCIe经常会把

15、那些外部WHERE条件为EXISTS.IN或ANY的;查询转换为对应的半连接。半连接分为嵌套循环半连接(Hinl为N1._SJ)、排序合并半连接(Hint为MERGE一SJ)和哈希半连接(Hint为HASl1.SJ),不过在新版本数据库里，都倾向于使用喑希半连接。哈希半连接也有一些限制条件，例如，只能使用等值连接，不能使用GROUPBY、CONNECTBWROWNUM等限制条件。在执行计划中若有关键字“HASHJOINSEM,则说明OraCle使用了哈希半连接。考点SQ1.优化10. 什么是反连接(AntiJoin)?正确答案：反连接(AntiJOin)也是种特殊的连接类型，通常用于从一个点中

16、返回不在另一个数据源中的数据行。当做了查询展开时，OraCle经常会把那些外部WHERE条件为NoTEXISTSNOTlN或A1.1.的了查询转换成对应的反连接。反连接分为嵌套循环反连接(NESTED1.ooPSNT1.Hint为N1.AJ)、排序合并反连接(MERGEJOINANrn,Hint为MERGE_AJ)和哈希反连接(HASHJOINANrn,Hint为HASF1.AJ)。需要注意的是，NOTIN和VA1.1.对U1.1.值敏感，这意味着V)TIN后面的子查询或者常量集合一旦有NU1.1.值出现，则整个SQ1.的执行结果就会为NU1.1.,即此时的执行结果将不包含任何记录。但是，NO

17、TEXlSTS对NU1.1.值不敏感，这意味着NU1.1.值对NOTEXlSTS的执行结果不会有什么影响。正是因为NOTlN和VA1.1.对NU1.1.值敏感，所以一旦相关的连接列上出现了Ml1.1.值，此时OraCIe如果还按照通常的反连接的处理逻辑来处理.，得到的结果就不对/为了解决NOTIN和VA1.1.对NU1.1.值敏感的问题，Oracle推出了改良的反连接,这种反连接能够处理NU1.1.值，OraCIe称其为NUII-AWareAntiJoin,如下例：SE1.ECT,ROMDEPIAWHEREDEPTNONOTIN(SE1.ECTDEPTNOFROMEMP):IIdOperati

18、onNa8osBytesCost(VH)ITiwI0SE1.HTSTATflENTI235(20)i00:00:01*1HAS)JOINAXIIXA1235(20)i00:00:0)12T.WACCESSFlUCePT4l802(O)100:00:0!I3T三ACCESSFillBFIi12362(0)00:00:01I执行步骤的列OPeration的值为“HASHJOINANTINAw,关键字“NA”就是NUlIAWare的缩写，表示这里采用的不是普通的哈希反连接，而是改良后的、能够处理NU1.1.值的哈希反连接。在OraCIellgR2中,OraCle是否启用NUlI-AWareAntiJ

19、Oin受隐含参数“_OPTIMIZER_NU1.1._AWARE_ANTIJOIN”控制，其默认值为TRiJE,表示启用Nu11-AwarentiJOin。如果把该参数的值修改为FA1.SE那么表示Orac1e就不能再用NUlI-AWareAntiJOin了,而又因为NOTIN对NU1.1.值敏感，所以Oracle此时也不能用普通的反连接。考点SQ1.优化11. 什么是星型连接(SlarJoin)?正确答案：星型连接(StarJOin)通常用于数据仓库类型的应用，它是一种单个事实表(FactTabIe)和多个维度表(DimenSiOnTable)之间的连接。星型连接的各维度表之间没有直接的关联

20、条件，其事实表和各维度表之间是基于事实表的外健列和对应维度表的主键列之间的连接，并且通常在事实表的外键列上还会存在对应的位图索引。考点SQ1.优化12. 友和表之间的关联方式有哪几种？正确答案：目前为止，无论连接操作符如何，典型的连接类型共有3种：1)排序合并连接(SortMergeJoin,SMJ),Oracle6提供。2)嵌套循环(NeSted1.oopsN1.),Oracle6提供。3)哈希连接(HaShJoin,HJ),也叫散列连接,Oracle7.3新增。另外,还有一种笛卡儿积(MergeJoinCartesian,MJe)连接，在OraCIe6版本的时候就已经提供，一般情况下，尽量

21、避免使用。对于OraCIe6提供的群集连接(ClUSterJoin)和OraCIe8提供的索引连接(IndeXJoin),本书不做介绍。对于DBA来说，掌握这3种表的连接方式可以对SQ1.优化起到至关重要的作用。对于这3种关联方式的详细对比，参考下表。表连排序合并连接哈希连接接方嵌套循环式/*+USE_N1.(TIT2)*/或若/*+Hint1.EADING(Tl)USE_N1.(T2)*/*+USE_MERGE(TlT2)*/则Tl作为驱动表，T2作为被驱动表有,驱动表的顺序直接影响SQ1.语句的性能，应该是小的结果集先访问，大的结果集后访问才能保证被驱.动表的访问次数2机降到最低，从而提升

22、性能手雷鲁(逻辑读BUFEER无姒于差异大)。需要注意的是，应该选择由过浦条件限制返回记录最少的那张表作为驱动表，而不是根据表的大小来选择驱动表*+USEHASH(TlT2)*/有，驱动表的顺序非常重要，直接影响内存消耗(OMere、Used-Mem差异大)。所以，应该小表作驱动，内存消耗要小得多，性能更好驱动表最多被访问1次,被驱动表被访表访问N次，N由驱动表返回相关联的2张表都最多被访驱动表和被驱动表问次的结果集的行数问都是最多被访问1数来决定，即驱动表返回多1次，和HJ一样次少条记录，被驱动表就访问多少次，:也若驱动表根据过滤条件返回。条记录，则此时，驱动表被访问1次，被驱鲁认动表被访问

23、0次；若目标SQ1.语句的WHERE条件含不成立(例如，WHERE1=2),则驱动表和被驱动表都不被访问，即访问次数为0次。虽然SMJ没有驱动和被驱动的说法，但是，I的相关联的表访问次数和HJ是一样的是否无须排序，不消耗内存需要排序(SoRT_AREA_HJ在大多数隋况下排序SIZE)都不需要排序，但会消耗内存（HASH_AREA_SIZE）用于娘立HASH表1）只取业务需要的D只取业务需要的字段，避字段，避免用*,从1）选择结果集最小的表作为驱动表2）对驱动表的限制条件建布伊立索引，对被驱动表的连接知牛建立索方向弓I3）尽量减少外层循环的次数，提高内层循环的查询效率免用*,从而减少排序而减少

24、排序2）索引列在表连接2）在两表的眼制条件上创建中无特殊要求索引3）两表的限制条件3）在两表的连接条件上创建有索引（根据索引可以过滤掉引，利用索引来消除排序的大部分数据）次数4）小结果集的表作4）尽量确保PAG足够大，避驱动表免磁盘排序点连接的限制（使支持所有表连接的写法用条件）支持大于、小于、大于等于和小于等于，但不支持、1.IKE5）尽量确保PGA可容纳HASH运算哈希连接只适用于CBO,且仅用于等值连接，不支持不等值连接（包括V、V、=、=、OR、1.IKE操作），即使是哈希反连接，OraCIe实际上也是将其转换成了等价的等值连接:设CPU、磁盘I/O资源磁盘I/O、内存、临时空间CPU

25、是否有隐含无参数控制隐含参数OPTIMIZER一SoRTMERGEJOIN一EMAB1.ED”控制着SMJ的启用和关闭，该参数默认值是TRuE,表示启用SMJ从OraCleIOg开始隐含参数M_HASH_JoIN.ENAB1.ED”控制着HJ的启用和关闭，该参数默认值是TRUE,表示启用HJo需要1）如果驱动表（外部表）返回的结果集D对于非等值连接，SMJ的比较小，并且在被驱动表效（内部表）上有率是比较高的唯一索弓I（或有高选择性非2）在通常情况下，SMJ并不唯一索引）时,那么使用X1.可以得到较好合O1.TR类型的系统，而更的效率适合2）N1.连接有其他连接方法O1.AP场景，倾向于吞吐量没

26、有的一个优点：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应适用3）两表关联返回的记录不场合多，最佳情况是驱动表结果集仅返回一条或少量几条记录，而被驱动表仅匹配到1条或少量几条记录，这种情况下即便关联的两张表奇大无比，N1.连接也是非常迅速的4）遇到些不等值查询导致哈希和排序合并连接被限制使用，不得不使用N1.连接5）N1.通常用于O1.TP中，应用有大量访问，但是每个访问最终比较注意的是，在OraCIeIOg之前是参数USHJ01N_ENAB1.ED控制者UJ而启用和关闭1）一般来说,HJ效率好于N1.和SMJ连接，但是HJ只能用在CBO优化器中,且只

27、能用于等值连接中，另外需要设置合适的HASH_AREA_SIZE参数值才能取得较好大的操作，即最终SQ1.返回的性能的记2）在两个较大的表业数比较多的场景。因为对源之间连接时会取得相对较好O1.TP类型的系统而言，排的效率，在一个表序是非源较小时则能取得常昂贵的操作，当然，如果更好的效率。能避HJ很适合于小表和免排序操作，那么即使是大表之f叫做表连O1.TP接且连接结果集类型的系统，也还是可以使的记录数较多的情形，特别是在小表SMJ的。比如两个表虽然是的连接列的可选择性非常好的情SMJ,但实际上它们并不需况下，这时候HJ的要排执行时间就可序，因为这两个表在各自的以近似看作是和全连接列上都存在索

28、引表扫描那个大表所耗费的时间相当3）如果在关联的列上都有索3）当两个表做IIJ引，那么效果更好时，如果在施加了目标SQ1.中返回的记录很少的场景指定的谓词条件（如果有的话）后得到的数据量较小的那个结果集所对应的HashTable能够完全被容纳在内存（PGA的工作区）中，那么此时的HJ的执行效率会非常高4）如果相关联的表在同一数量级，且数据量比较大，那么此时应选择HJ5）HJ一般用于O1.AP场景中,SQ1.最终返回的记录数较多6）若表中无索引，则倾向于HJ先将关联表的关联列各自做将一个表（通常是小表）做HASH运算,将列数据存储到HASH列表中，从另一个表中抽取记录，做HASH运算,然后到HA

29、SH列表中找到相应的值做匹配排序，然后从各自的排序表中在做表连接时依靠两层嵌抽取套循环（分别数据，到另一个排序表中做工作方式为外层循环和内层循环）来匹配，得到连接结果因为SMJ需要做更多的排集。N1.适用的场合是当一序，所个关联表比较小以，消耗的资源更多。通常的时候，效率会更高来讲，能够使用SMJ的地方，HJ都可以发挥更好的性能考点SQ1.优化13.表的访问方式有哪几种?正确答案：访问表的方式也叫优化器访问路径，主要有3种访问路径：全表扫描(FU1.1.TAB1.ESCAN,FTS)、索引扫描(INDEXSCAN)和ROWlD访问。考点SQ1.优化14.COUNT(*)计算行数有哪些优化F段？

30、正确答案：可以有如下表所示几种优化手段。命令执行计划主要原理详细说明手段全表扫描增加普通索引常数索引常数压缩索引性能情况TAB1.EACCESSFU1.1.全表扫描O1.TP中,通常逻辑读是最慢的方式为1139CREATEINDEXIDXOBJECTNAMEONT(OBJECFNAME);因为索引一般比表小得多，INDEXFASTFU1.1.SCAN从全表扫描转成全索引扫描所以全表扫描转成全索引扫描，性能能大幅度提升逻辑读为100CREATEINDEXIDX_OBJECT_NMEONT(O);INDEXFASTFU1.1.SCAN从全表扫描转成全索引扫描常数索引比普通索引更小逻辑读为151CR

31、EATEINDEX从全表扫描常数压缩索引逻辑读为129DXOBJECTNAMEONT(O)COMPRESS;INDEXFASTFU1.1.SCAN转成全索引扫描比常数索引更小位图索引BITMAPINDEX引FASTFU1.1.扫描转成位SCAN图索引扫描MATVIEW空间换时间CREATEBITMAPINDEX1DX_OBJECT_NAMEONT(OBJECT_NAME);物CREATEMATERIA1.IZED从BTREE索位图索引的大小比BTREE索引要小得逻辑读多，所以位图为5索引扫描快要注意，如果逻辑读分析需求VIEWREWRITE数据要求比较为3MV_COUNT_TACCESSFU1

32、.1.实时，就不适BuildhwediateREFRESHONCOMMITENAB1.EQUERYREWRITEASSE1.ECTCOUNT()EROMT;用SE1.ECT*+RESU1.TKACHE*/直接把查询RESU1.TCACHE结果要注意，如果数据频繁更新，就不适用逻辑读为OCOUNT(*)FROMT;拿来用业务需求转如果换，获取条数有的时候，只是为了看看表是否为空，这时候是否是1条和是否大于0其实足一SE1.ECTCOUNT()FROMTWHEREROWNUM=I;COUNT(*)只是为了判断条数，就自吐ROWNUM=1来判断是否为1不言而喻样的化视图缓存结果业务理解据说，这个CO

33、UNT(*)统计条数语句，是多余的!直接去掉这条话时信句，这里没有SQ1.!取优位图索引可以按很高密度存储数据，因此往往比B树索引小很多，前提是在基数比较小(列市.复度比较高)的情况下。位图索引是保存空值的，因此可以在CoUNT中利用。位图索引不太适合O1.TP类型数据库。物化视图是应用在数据要求不怎么及时的场景下。若表频繁更新，则不适合缓存结果集。优化没有止境，对数据库了解越多，能想到的方法就越多。考点SQ1.优化15. 给出下面语句的可能的优化思路。CREATETAB1.ET_YH_20170705_1.HR(XINT)：BEGINEOKIIN1.1000001.OOPEXECUTEIMM

34、EDIATE,INSERTINTOT_YH_20170705JJlRVA1.UESCI11I）;COMMIT：END1.OOP;END;正确答案：优化思路有：采用绑定变量：使用静态SQ1.：采用批量提交或循环外提交：根据功能，可以去掉P1./SQ1.块，采用直接一次性插入的方式来完成，SQ1.为“INSERTINTOT_YH_20170705_1.HRSE1.ECTROWNUMFROMDUA1.CONNECTBY1.EVE1.=100000;；采用直接路径方式，例如，“CREATETAB1.ET_YH_20170705_1.HRASSE1.ECTKOWNUMXI-ROMDUA1.CONNECT

35、BY1.EVE1.=100000;,；&采用No1.oGGING和PARA1.1.E1.的方式，例如，“CREATETAB1.ET_YH_20170705_1.HRNO1.OGGINGPARA1.1.E1.8ASSE1.ECTROWNUMXFROMDUA1.CONNECTBY1.EVE1.=100000;w考点SQ1.优化16. SQ1.如何优化?SQ1.优化关注点有哪些？正确答案：在多数情况下，OraCle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQ1.语句的WHERE子句中写的SQ1.条件不合理,那么就会造成优化器舍去索引而使用全表扫描，一般这种SQ1.语句的性能

36、都是非常差的。在编写SQ1.语句时，应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQ1.语句。SQ1.的优化主要涉及如下几个方面的内容：1）索引问题。是否可以使用组合索引：限制条件、连接条件的列是否有索引；能否使用到索引，避免全表扫描。一般情况下，尽量使用索引，因为索引在很多情况下可以提高查询效率。排序字段有正确的索引，驱动表的限制条件有索引，被驱动表的连接条件有索引。2）相关的统计信息缺失或者不准确。查看SQ1.的执行计划是不是最优，然后结合统计信息查看执行计划是否正确.3）直方图使用错误。4）SQ1.本身的效率问题,例如使用绑定变量,批量DM1.采用BU1.K等,这个就考验写S

37、Q1.的基本功了。5）数据量大小。如果就是几百条数据，那么就没有所谓效率之分，一般情况下怎么写效率都不低。如果数据量很大，那么就得考虑是否要分页或排序。6）绑定变量。大多数情况绑定变量能提高查询效率，但也有降低效率的情况。7）批量和并行的考虑。8）业务需求需要正确理解，实现业务的逻辑需要正确，减少一些重复计算。有可能是设计的不合理、业务需求的不合理，而问题SQ1.并IF.根本原因。9）查询特别频繁的结果是否可以缓存，比如Oracle的*+result_cache*/o10）分析表的连接方式。若是N1.连接，则根据业务或表的数据质量情况，分析能否减少驱动表的结果集。11）是否可以咧定执行计划。1

38、2）大表是否存在高水位。13）在创建表的时候，应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数。考点SQ1.优化17. SQ1.优化在写法上有哪些常用的方法？正确答案：一般在3写SQ1.时需要注意哪些问题，如何书写可以提高查询的效率?可以从以卜几个方面去考虑：D减少对数据库的访问次数。2）减少对大表的扫描次数。可以利用WITH对SQ1.中多次扫描的表来进行修改。采用各种手段来避免全表扫描。3）SE1.ECT子句中避免使用，应该写出需要查询的字段。4）尽量使用表的别名（A1.lAS）.当在SQ1.语句中连接多个衣时，清使用表的别名，并把别名前缀于每个列上。此时就可以

39、减少解析的时间并减少那些由列歧义引起的语法错误。5）对于数据量较少，又有主键索引的情况，可以考虑将关联了查询或外连接的SQ1.修改为标量子查询。6）避免隐式类型转换（ImPIiCitTypeConversion）如果进行比较的两个值的数据类型不同，那么Oracle必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换.通常当开发人员将数字存储在字符列时会导致这种问题的产生。Oracle在运行时会在索引字符列使用TONUMBER函数强制转化字符类型为数值类型。由于添加函数到索引列，导致索引不被使用。实际上，OraCIe也只能这么做，类型转换是一个应用程序设计因素。由于转换是在每行都进

40、行的，这会导致性能问题。一般情况下，当比较不同数据类型的数据时，Oracle自动地从复杂向简单的数据类型转换。所以，字符类型的字段值应该加上引号。例如，假设USERIo是一个字符类型的索引列，则SE1.ECTUSER_N0,USER_NAME,ADDRESSFROMUSERE11.ESWHEREUSER_NO=109204421;这个语句在执行的时候被OraCle在内部自动地转换为SE1.ECTUSER_N0,USER_NAME,ADDRESSFROMUSER_FI1.ESWHERETO_NUMBER(USER_NO)=IO9204421;因为是内部发生的类型转换，这个索引将不会被使用，所以正

41、确的写法应该是SEI.ECTUSER.NO,USER_NAME,ADDRESSFROMuserFileswhereuserno=109204421；7)避免使用耗费资源的操作，包括DlSTINCT、UNION.MINUS.INTERSECT.ORDERBY、GROUPBY等、能用DISTINeT的就不用GROUPBY,能用UNIONA1.1.就不要用UNIONo8)用TRUNCATE替代DE1.ETE。若要删除表中所有的数据，则可以用TRUNCATE替代DE1.ETE09)根据查询条件建立合适的索引，利用索引可以避免大表全表扫描(FU1.1.TAB1.ESCAN)。10)合理使用临时表。ID避

42、免写过于熨杂的SQ1.,不一定非要一个SQ1.解决问题。将一个大的SQ1.改写为多个小的SQ1.来实现功能。条件允许的情况下可以使用批处理来完成。12)在不影响业务的前提卜.尽量减小事务的粒度。13)当使用基于规则的优化器(RBO)时，在多表连接查询的时候，记录数少的表应该放在右边。14)避免使用复杂的集合函数，像NOTIN等。通常，要避免在索引列上使用NOT,NoT会产生和在索引列上使用函数相同的影响。当OraCle遇到NoT操作符时，它就会停止使用索引转而执行全表扫描。很多时候用EXISTS和NOTEXISTS代替IN和NOTIN语句是一个好的选择。需要注意的是，在OraeleIlg之前，

43、若NOTIN的列没有指定非空(注意：是主表和子点的列未同时有M)TNlJ1.1.约束,或都未加ISNOTNU1.1.限制),则M)TIN选择的是filter操作(如果指定了非空，那么会选择ANTl的反连接),但是从OraCIeIIg开始有新的ANTINA(NU1.1.AWARE)优化,可以对予查询进行UNNES化NOTIN和NOTEXISTS都选择的是ANTl的反连接，所以效率是一样的。在一般情况卜，ANTI的反连接算法比filter更高效。对于未UNNEST的子查询，若选择了filter操作，则至少有两个子节点，执行计划还有个特点就是PrediCate谓词部分有“：B1”这种类似绑定变量的内

44、容,内部操作类似NeSt容1.OoPS操作。如果在OraCIeIlg之前，遇到0TIN无法UNNEST,那么可以将NOTIN部分的匹配条件均设为NoTNU1.1.约束。若不添加M)TNUiX约束，则需要两个条件均增加ISNOTNU1.1.条件。当然也可以将NoTlN修改为NOTEXISTSo15)尽量避免使用UNIO、关键词，可以根据情况修改为UNIONA1.1.(I16)在OraCle数据库里，IN和OR是等价的，优化器在处理带IN的目标SQ1.时会将其转换为带OR的等价SQ1.例如,kDEPTNOIN(IO,20)”和rtDEPTNO=100RDEPTNo=20”是等价的。17)选择合适的

45、谓词进行过滤。18)避免使用前置通配符(%)。在WHERE子句中，如果索引列所对应的值的第一个字符由通配符(WI1.DCARD)开始，索引将不被采用。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低雀询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。若前国.通配符实在无法取消，则可以从3个方面去考虑：去重和去空。应该把表中的重复记录或者为空的记录全部去掉，这样可以大大减少结果集，因而提升性能，这里也体现了大表变小表的思想。考虑建立文本索引。做相关的转换。19)应尽量避免在WHERE子句中对字段进行函数、算术运算或其他表达式等操作，因为这样可能会使索引失效，查询时要尽可能将操作移至等号右边。见如下例子:SE1.ECT*1ROMTlWHERESlBSTR(NAME,2,D=1.,;在以上SQ1.中，即使NAME字段建有唯一索引，该SQ1.语句也无法利用索引进行检索数据，而是走全表扫描的方式。一些常见的改写见下表。原SQ1.语句SE1.ECT*EKOMTlWHE