本文档来自网上，粘出来供大家分享
FORALL用法小结：
作者：sonic本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容，不妥之处，还希望多和大家交流。在发送语句到SQL引擎前，FORALL语句告知PL/SQL引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式，它并不一是个FOR循环。其语法为：FORALLindexINlower_bound..upper_boundsql_statement;
一、如何使用批挷定提高性能（How
DoBulkBindsImprovePerformance）
在PL/SQL和SQL引擎(engines)中，太多的上下文切换(contextswitches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。下图显示PL/SQL引擎和SQL引擎之间的contextswitches：（PL/SQL引擎执行存过语句仅发送SQL语句到SQL引擎，SQL引擎执行语句后返回数据给PL/SQL引擎）PL/SQL引擎发送一次SQL语句给SQL引擎，在SQL引擎中则为范围中每个index数字执行一次SQL语句。PL/SQL挷定操作包含以下三类：in-bind：WhenaPL/SQLvariableorhostvariableisstoredinthedatabasebyanINSERTorUPDATEstatement.out-bind：WhenadatabasevalueisassignedtoaPL/SQLvariableorahostvariablebytheRETURNINGclauseofanINSERT,UPDATE,orDELETEstatement.define：WhenadatabasevalueisassignedtoaPL/SQLvariableorahostvariablebyaSELECTorFETCHstatement.在SQL语句中，为PL/SQL变量指定值称为挷定(binding)，DML语句能传递所有集合元素到一个单个操作中，这过程称为批挷定（bulkbinding）。如果集合有20个元素，批挷定让你用单个操作等效于执行与20个SELECT，INSERT，UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括：1．带INSERT,UPDATE,andDELETE语句的批挷定：在FORALL语句中嵌入SQL语句2．带SELECT语句的批挷定：在SELECT语句中用BULKCOLLECT语句代替INTO下边的例子分别用FOR和FORALL进行数据插入，以显示用批挷定的对性能的提高：
SQL代码1.2.3.4.5.6.7.8.SQL>SETSERVEROUTPUTONSQL>CREATETABLEparts(pnumNUMBER(4),pnameCHAR(15));Tablecreated.SQL>DECLARETYPENumTabISTABLEOFparts.pnum%TYPEINDEXBYBINARY_INTEGER;TYPENameTabISTABLEOFparts.pname%TYPEINDEXBYBINARY_INTEGER;pnumsNumTab;PnamesNameTab;
1
本文档来自网上，粘出来供大家分享
9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.
t1NUMBER;t2NUMBER;t3NUMBER;BEGINFORiIN1..500000LOOPpnums(i):=i;pnames(i):='PartNo.'||to_char(i);ENDLOOP;t1:=dbms_utility.get_time;FORiIN1..500000LOOPINSERTINTOpartsVALUES(pnums(i),pnames(i));ENDLOOP;t2:=dbms_utility.get_time;FORALLiIN1..500000INSERTINTOpartsVALUES(pnums(i),pnames(i));t3:=dbms_utility.get_time;dbms_output.put_line('ExecutionTime(secs)');dbms_output.put_line('---------------------');dbms_output.put_line('FORloop:'||TO_CHAR(t2-t1));dbms_output.put_line('FORALL:'||TO_CHAR(t3-t2));END;
SQL>/ExecutionTime(secs)--------------------FORloop:2592FORALL:358PL/SQLproceduresuccessfullycompleted从而可以看出FORALL语句在性能上有显著提高。注释：SQL语句能涉及多个集合，然而，性能提高只适用于下标集合(subscriptedcollections)
二、FORALL
如何影响回滚(HowFORALLAffectsRollbacks)
在FORALL语句中，如果任何SQL语句执行产生未处理的异常(exception)，先前执行的所有数据库改变都会被回滚。然而，如果产生的异常被捕获并处理，则回滚改变到一个隐式的保存点，该保存点在每个SQL语句执行前被标记。之前的改变不会被回滚。例如
SQL代码1.CREATETABLEemp2(deptnoNUMBER(2),jobVARCHAR2(15));
2
本文档来自网上，粘出来供大家分享
2.INSERTINTOemp2VALUES(10,'Clerk');3.INSERTINTOemp2VALUES(10,'Clerk');4.INSERTINTOemp2VALUES(20,'Bookkeeper');--10-charjobtitle5.INSERTINTOemp2VALUES(30,'Analyst');6.INSERTINTOemp2VALUES(30,'Analyst');7.Comit;8.DECLARE9.TYPENumListISTABLEOFNUMBER;10.deptsNumList:=NumList(10,20,30);11.BEGIN12.FORALLjINdepts.FIRST..depts.LAST13.UPDATEemp2SETjob=job||'(temp)'14.WHEREdeptno=depts(j);15.--raisesa"valuetoolarge"exception16.EXCEPTION17.WHENOTHERSTHEN18.COMMIT;19.END;20./21.PL/SQLproceduresuccessfullycompleted22.SQL>select*fromemp2;
DEPTNOJOB------------------------10Clerktemp10Clerktemp20Bookkeeper30Analyst30Analyst上边的例子SQL引擎执行UPDATE语句3次，指定范围内的每个索引号一次。第一个(depts(10))执行成功，但是第二个(depts(20))执行失败(插入值超过了列长)，因此，仅仅第二个执行被回滚。当执行任何SQL语句引发异常时，FORALL语句中断(halt)。上边的例子中，执行第二个UPDATE语句引发异常，因此第三个语句不会执行。
三、用%BULK_ROWCOUNT
属性计算FORALL迭代影响行数
在进行SQL数据操作语句时，SQL引擎打开一个隐式游标(命名为SQL)，该游标的标量属性(scalarattribute)有%FOUND,%ISOPEN,%NOTFOUND,and%ROWCOUNT。FORALL语句除具有上边的标量属性外，还有个复合属性(compositeattribute)：%BULK_ROWCOUNT，该属性具有索引表(index-bytable)语法。它的第i个元素存贮SQL语句(INSERT,UPDATE或DELETE)第i个执行的处理行数。如果第i个执行未影响行，%bulk_rowcount(i)，返回0。FORALL与%bulk_rowcount属性使用相同下标。例如：DECLARE
3
本文档来自网上，粘出来供大家分享
TYPENumListISTABLEOFNUMBER;deptsNumList:=NumList(10,20,50);BEGINFORALLjINdepts.FIRST..depts.LASTUPDATEempSETsal=sal*1.10WHEREdeptno=depts(j);--Didthe3rdUPDATEstatementaffectanyrows?IFSQL%BULK_ROWCOUNT(3)=0THEN...END;%ROWCOUNT返回SQL语句所有执行处理总的行数%FOUND和%NOTFOUND仅与SQL语句的最后执行有关，但是，可以使用%BULK_ROWCOUNT推断单个执行的值，如%BULK_ROWCOUNT(i)为0时，%FOUND和%NOTFOUND分别是FALSE和TRUE。
四、用%BULK_EXCEPTIONS属性处理FORALL异常
在执行FORALL语句期间，PL/SQL提供一个处理异常的机制。该机制使批挷定(bulk-bind)操作能保存异常信息并继续执行。方法是在FORALL语句中增加SAVEEXCEPTIONS关键字。语法为：FORALLindexINlower_bound..upper_boundSAVEEXCEPTIONS{insert_stmt|update_stmt|delete_stmt}执行期间引发的所有异常都被保存游标属性%BULK_EXCEPTIONS中，它存贮一个集合记录，每记录有两个字段：%BULK_EXCEPTIONS(i).ERROR_INDEX：存贮在引发异常期间FORALL语句迭代(重复：iteration)%BULK_EXCEPTIONS(i).ERROR_CODE：存贮相应的Oracle错误代码%BULK_EXCEPTIONS.COUNT存贮异常的数量。(该属性不是%BULK_EXCEPTIONS集合记录的字段)。如果忽略SAVEEXCEPTIONS，当引发异常时，FORALL语句停止执行。此时，SQL%BULK_EXCEPTIONS.COUNT返回1,且SQL%BULK_EXCEPTIONS只包含一条记录。如果执行期间无异常SQL%BULK_EXCEPTIONS.COUNT返回0.例子：
DECLARETYPENumListISTABLEOFNUMBER;num_tabNumList:=NumList(10,0,11,12,30,0,20,199,2,0,9,1);errorsNUMBER;dml_errorsEXCEPTION;PRAGMAexception_init(dml_errors,-24381);BEGINFORALLiINnum_tab.FIRST..num_tab.LASTSAVEEXCEPTIONSDELETEFROMempWHEREsal>500000/num_tab(i);EXCEPTIONWHENdml_errorsTHENerrors:=SQL%BULK_EXCEPTIONS.COUNT;dbms_output.put_line('Numberoferrorsis'||errors);FORiIN1..errorsLOOPdbms_output.put_line('Error'||i||'occurredduring'||
4
本文档来自网上，粘出来供大家分享
'iteration'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);dbms_output.put_line('Oracleerroris'||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));ENDLOOP;END;该例子中，当i等于2，6,10时，产生异常ZERO_DIVIDE，完成后SQL%BULK_EXCEPTIONS.COUNT为3，其值为(2,1476),(6,1476)和(10,1476)，错误输出如下：Numberoferrorsis3Error1occurredduringiteration2OracleerrorisORA-01476:divisorisequaltozeroError2occurredduringiteration6OracleerrorisORA-01476:divisorisequaltozeroError3occurredduringiteration10OracleerrorisORA-01476:divisorisequaltozero
五、用BULKCOLLECT子句取回查询结果至集合中
在返回到PL/SQL引擎之前，关键字BULKCOLLECT告诉SQL引擎批挷定输出集合。该关键字能用于SELECTINTO,FETCHINTO和RETURNINGINTO语句中。语法如下：...BULKCOLLECTINTOcollection_name[,collection_name]...示例1：DECLARETYPENumTabISTABLEOFemp.empno%TYPE;TYPENameTabISTABLEOFemp.ename%TYPE;enumsNumTab;--noneedtoinitializenamesNameTab;BEGINSELECTempno,enameBULKCOLLECTINTOenums,namesFROMemp;...END;示例2：CREATETYPECoordsASOBJECT(xNUMBER,yNUMBER);CREATETABLEgrid(numNUMBER,locCoords);INSERTINTOgridVALUES(10,Coords(1,2));INSERTINTOgridVALUES(20,Coords(3,4));DECLARETYPECoordsTabISTABLEOFCoords;pairsCoordsTab;BEGINSELECTlocBULKCOLLECTINTOpairsFROMgrid;--nowpairscontains(1,2)and(3,4)END;示例3：
5
本文档来自网上，粘出来供大家分享
DECLARETYPESalListISTABLEOFemp.sal%TYPE;salsSalList;BEGINSELECTsalBULKCOLLECTINTOsalsFROMempWHEREROWNUM<=100;...END;示例4：ExamplesofBulkFetchingfromaCursor：DECLARETYPENameListISTABLEOFemp.ename%TYPE;TYPESalListISTABLEOFemp.sal%TYPE;CURSORc1ISSELECTename,salFROMempWHEREsal>1000;namesNameList;salsSalList;BEGINOPENc1;FETCHc1BULKCOLLECTINTOnames,sals;--可返回到一个或多个集合END;示例5：ExamplesofBulkFetchingfromaCursor：DECLARETYPEDeptRecTabISTABLEOFdept%ROWTYPE;dept_recsDeptRecTab;CURSORc1ISSELECTdeptno,dname,locFROMdeptWHEREdeptno>10;BEGINOPENc1;FETCHc1BULKCOLLECTINTOdept_recs;--返回到一个记录(records)集合END;
六、用LIMIT
子句限制批取出操作行
LIMIT子句可限制从数据库中取出的行数。该子句仅能用于批(非标量notscalar)FETCH语句.语法为：FETCH...BULKCOLLECTINTO...[LIMITrows];rows可以是文字的(literal)，变量(variable)或表达式(expression)，但必须返回一个数字。否则，PL/SQL引发预定义异常VALUE_ERROR，如果为负数，PL/SQ引发INVALID_NUMBER。如果需要，PL/SQL四舍五入(round)为最近的整数.下例中，在每个循环迭代中，FETCH语句返回到索引表empnos中，先前的值会被覆盖。
DECLARETYPENumTabISTABLEOFNUMBERINDEXBYBINARY_INTEGER;
6
本文档来自网上，粘出来供大家分享
CURSORc1ISSELECTempnoFROMemp;empnosNumTab;rowsNATURAL:=10;BEGINOPENc1;LOOP/**//*Thefollowingstatementfetches10rows(orless).*/FETCHc1BULKCOLLECTINTOempnosLIMITrows;EXITWHENc1%NOTFOUND;...ENDLOOP;CLOSEc1;END;
七、用RETURNINGINTO子句取回DML结果至集合
你能在INSERT、UPDATE或DELETE语句的RETURNINGINTO子句中使用BULKCOLLECT子句。(注意，此处是没有SELECT语句的)
DECLARETYPENumListISTABLEOFemp.empno%TYPE;enumsNumList;BEGINDELETEFROMempWHEREempno=7839RETURNINGempnoBULKCOLLECTINTOenums;--iftherewerefiveemployeesindepartment20,--thenenumscontainsfiveemployeenumbersForIInEnums.First..Enums.LastLoopdbms_output.put_line(Enums(I));endloop;End;
八、BULKCOLLECT上的限制
1．Youcannotbulkcollectintoanassociativearraythathasastringtypeforthekey..2．BULKCOLLECT语句只能用于服务器端(server-side)，而非客户端3．在BULKCOLLECTINTO语句中的所有目标必须是集合，如下例：
DECLARETYPENameListISTABLEOFemp.ename%TYPE;namesNameList;salaryemp.sal%TYPE;
7
本文档来自网上，粘出来供大家分享
BEGINSELECTename,salBULKCOLLECTINTOnames,salary--illegaltargetFROMempWHEREROWNUM<50;...END;.4．复合目标(如对象)不能用于RETURNINGINTO子句中.5．当需要隐式数据类型转换时，多复合目标(如对象集合)不能用于bulkcollectinto子句。
九、同时使用FORALL和BULKCOLLECT
UsingFORALLandBULKCOLLECTTogether你能使FORALL语句与BULKCOLLECT结合，如下例：如果集合depts有3个元素，每个元素导致5行被删除，则语句完成时，集合enums有15个元素：FORALLjINdepts.FIRST..depts.LASTDELETEFROMempWHEREempno=depts(j)RETURNINGempnoBULKCOLLECTINTOenums;注意：不能在FORALL语句中使用SELECT...BULKCOLLECT语句。
十、UsingHostArrayswithBulkBinds
客户端程序能用匿名PL/SQL块批挷定输入和输出数组(arrays)。实际上，这是与服务器端传递集合最有效的方式。HostarraysaredeclaredinahostenvironmentsuchasanOCIorPro*CprogramandmustbeprefixedwithacolontodistinguishthemfromPL/SQLcollections.Intheexamplebelow,aninputhostarrayisusedinaDELETEstatement.Atruntime,theanonymousPL/SQLblockissenttothedatabaseserverforexecution.DECLARE...BEGIN--assumethatvalueswereassignedtothehostarray--andhostvariablesinthehostenvironmentFORALLiIN:lower..:upperDELETEFROMempWHEREdeptno=:depts(i);...END;限制：以下限制将应用于FORALL语句：1．在FORALL循环中，UPDATE语句中的SET子句和WHERE子句中不能指向同一个集合，你可能需要获得另一个集合副本，以在WHERE子句指向新的名称。2．INSERT，UPDATE或DELETE语句必须至少涉及一个集合。如在LOOP插入一组常量的FORALL语句将引发异常。FORALL的INSERT之类的语句一定要用in-bind方式，（如：
8
本文档来自网上，粘出来供大家分享
SQL>BEGIN2FORALLiIN1..1003INSERTINTOpartsVALUES(i,i);4END;5/ORA-06550:第3行,第5列:PLS-00435:没有BULKIn-BIND的DML语句在FORALL内不能使用ORA-06550:第2行,第12列:PL/SQL:Statementignored3．指定范围内的所有集合元素必须存在，如果一元素丢失或删除，你将收到一个错误，如：
DECLARETYPENumListISTABLEOFNUMBER;deptsNumList:=NumList(10,20,30,40);BEGINdepts.DELETE(3);--deletethirdelementFORALLiINdepts.FIRST..depts.LASTDELETEFROMempWHEREdeptno=depts(i);--causesanerrorEND;4．下例显示，复合值的输入集合不能被分解和跳跃数据库列
CREATETABLEcoords(xNUMBER,yNUMBER);CREATETYPEPairASOBJECT(mNUMBER,nNUMBER);DECLARETYPEPairTabISTABLEOFPair;pairsPairTab:=PairTab(Pair(1,2),Pair(3,4),Pair(5,6));TYPENumTabISTABLEOFNUMBER;numsNumTab:=NumTab(1,2,3);BEGIN/**//*Thefollowingstatementfails.*/FORALLiIN1..3UPDATEcoordsSET(x,y)=pairs(i)WHEREx=nums(i);END;Theworkaroundistodecomposethecompositevaluesmanually:DECLARETYPEPairTabISTABLEOFPair;pairsPairTab:=PairTab(Pair(1,2),Pair(3,4),Pair(5,6));TYPENumTabISTABLEOFNUMBER;numsNumTab:=NumTab(1,2,3);BEGIN
9
本文档来自网上，粘出来供大家分享
/**//*Thefollowingstatementsucceeds.*/FORALLiin1..3UPDATEcoordsSET(x,y)=(pairs(i).m,pairs(i).n)WHEREx=nums(i);END;5．集合下标不能是表达式，示例：FORALLjINmgrs.FIRST..mgrs.LASTDELETEFROMempWHEREmgr=mgrs(j+1);--invalidsubscript6．游标属性%BULK_ROWCOUNT不能分配给其它集合，同样，它也不能作为参数传递到子程序。
FORALL和FORLOOP的区别
同事问了我一个问题，使用FORALL和FORLOOP的区别到底是什么。以前也一直没有深究这个问题，唯一清楚的是FORALL是一个批量操作，效率明显比FORLOOP的方式要高，至于FORALL到底是一个SQL语句，还是将多个SQL语句同时提交给ORACLE还真不太清楚。
于是，首先做了个简单的例子，但是通过触发器来观察二者的区别：SQL>CREATETABLETTT(IDNUMBER);表已创建。SQL>CREATEORREPLACETRIGGERTRI_TTTBEFOREINSERTONTTT2BEGIN3DBMS_OUTPUT.PUT_LINE('A');4END;5/触发器已创建SQL>SETSERVEROUTONSQL>DECLARE2TYPET_NUM_TABLEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;3V_NUMT_NUM_TABLE;4BEGIN5SELECTROWNUMBULKCOLLECTINTOV_NUMFROMTAB;6FORIIN1..V_NUM.COUNTLOOP7INSERTINTOTTTVALUES(V_NUM(I));
10
本文档来自网上，粘出来供大家分享
8ENDLOOP;9END;10/AAAAAAAAAAAAAAAAPL/SQL过程已成功完成。SQL>DECLARE2TYPET_NUM_TABLEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;3V_NUMT_NUM_TABLE;4BEGIN5SELECTROWNUMBULKCOLLECTINTOV_NUMFROMTAB;6FORALLIIN1..V_NUM.COUNT7INSERTINTOTTTVALUES(V_NUM(I));8END;9/APL/SQL过程已成功完成。从触发器的除非动作上可以看出，FORALL语法和FORLOOP的区别，FORALL对INSERT语句只调用了一次。通过SQL_TRACE的方式也可以清楚的看到这一点：SQL>DROPTRIGGERTRI_TTT;触发器已删除。SQL>ALTERSESSIONSETSQL_TRACE=TRUE;
11
本文档来自网上，粘出来供大家分享
会话已更改。SQL>DECLARE2TYPET_NUM_TABLEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;3V_NUMT_NUM_TABLE;4BEGIN5SELECTROWNUMBULKCOLLECTINTOV_NUMFROMTAB;6FORALLIIN1..V_NUM.COUNT7INSERTINTOTTTVALUES(V_NUM(I));8END;9/PL/SQL过程已成功完成。SQL>ALTERSESSIONSETSQL_TRACE=FALSE;会话已更改。SQL>ALTERSESSIONSETSQL_TRACE=TRUE;会话已更改。SQL>DECLARE2TYPET_NUM_TABLEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;3V_NUMT_NUM_TABLE;4BEGIN5SELECTROWNUMBULKCOLLECTINTOV_NUMFROMTAB;6FORIIN1..V_NUM.COUNTLOOP7INSERTINTOTTTVALUES(V_NUM(I));8ENDLOOP;9END;10/PL/SQL过程已成功完成。SQL>ALTERSESSIONSETSQL_TRACE=FALSE;会话已更改。SQL>HOSTTKPROFE:ORACLEADMINYTKUDUMPYTK_ORA_3964.TRCE:REPORT.TXTSYS=NOAGGREGATE=NO其中FORALL语句对应的INSERT语句为：
12
本文档来自网上，粘出来供大家分享
INSERTINTOTTTVALUES(:B1)
callcountcpuelapseddiskquerycurrentrows---------------------------------------------------------------------Parse10.000.000000Execute10.000.0001516Fetch00.000.000000---------------------------------------------------------------------total20.000.0001516而FORLOOP对应的INSERT语句为：INSERTINTOTTTVALUES(:B1)
callcountcpuelapseddiskquerycurrentrows---------------------------------------------------------------------Parse10.000.000000Execute160.000.06412016Fetch00.000.000000---------------------------------------------------------------------total170.000.06412016从上面的TKPROF可以更清楚的看出，对于FORALL语法，INSERT语句只是执行了一次，但是产生了16条记录。从这一点上，FORALL语法于INSERTINTOSELECT语法更为相似。但FORALL又和INSERTINTOSELECT有着本质的区别：SQL>TRUNCATETABLETTT;表被截断。SQL>ALTERTABLETTTMODIFYIDNUMBER(3);表已更改。SQL>INSERTINTOTTTSELECT994+ROWNUMFROMTAB;INSERTINTOTTTSELECT994+ROWNUMFROMTAB*第1行出现错误:ORA-01438:值大于为此列指定的允许精度
13
本文档来自网上，粘出来供大家分享
SQL>SELECT*FROMTTT;未选定行INSERTINTOSELECT语法属于一条语句，根据Oracle的语句级回滚，当插入由于个别数据发生错误的时候，整个插入语句被回滚。但对于FORALL语句，虽然Oracle只执行了INSERT语句一次，但是，如果发生了错误，是可以捕获的，且错误发生之间的操作是可以保留下来的。SQL>DECLARE2TYPET_NUM_TABLEISTABLEOFNUMBERINDEXBYBINARY_INTEGER;3V_NUMT_NUM_TABLE;4BEGIN5SELECTROWNUMBULKCOLLECTINTOV_NUMFROMTAB;6V_NUM(5):=1000;7BEGIN8FORALLIIN1..V_NUM.COUNT9INSERTINTOTTTVALUES(V_NUM(I));10EXCEPTION11WHENOTHERSTHEN12COMMIT;13END;14END;15/PL/SQL过程已成功完成。SQL>SELECT*FROMTTT;ID---------1234从这一点看，FORALL语法和INSERTINTOSELECT又有着本质的区别。个人感觉FORALL语法和Oracle的OCI中数组绑定语法十分类似。二者都采用数据绑定变量的方式，通过调用一次SQL，将整个数组的内容提交给Oracle，并且出现错误后，可以通过捕获错误的方式保留出错前已经进行的修改。个人认为，ALL语法和OCI的数组绑定具有相同的内部机制。FOR二者分别为PL/SQL和OCI提供了相同的批量处理功能。
14
本文档来自网上，粘出来供大家分享
SQL优化学习笔记
1.基于规则的优化器基于规则的优化器—RuleBased(Heuristic)Optimization(简称RBO)：简称：2.基于代价的优化器—CostBasedOptimization(简称CBO)基于代价的优化器—简称a)查询耗费资源可以被分成三个组成部分：I/O代价、CPU代价、NetWark代价查询耗费资源可以被分成三个组成部分：代价、代价、3.判断当前数据库使用何种优化器a)由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows_[1|10|100|1000]|first_rows|all_rows|choose|rulei.Rule为RBO优化器ii.Choose根据实际情况，如果数据字字典包含被引用的表的统计数据，即引用的对根据实际情况，如果数据字字典包含被引用的表的统计数据，象已经被分析，优化器，优化器。象已经被分析，则就使用CBO优化器，否则为RBO优化器。iii.All_rows为CBO优化器使用的第一种具体的优化方法，是以数据吞量为主要目优化器使用的第一种具体的优化方法，以便可以使用最少的资源完成语句。标，以便可以使用最少的资源完成语句。iv.First_rows为优化器使用的第二种具体的优化方法，是以数据响应时间为主要目为优化器使用的第二种具体的优化方法，是以数据响应据响应时间为主要目以便快速查询出开始的几行数据。标，以便快速查询出开始的几行数据。v.First_rows_[1|10|100|1000]为优化器使用的第三种具体的优化方法，为优化器使用的第三种具体的优化方法，为优化器使用的第三种具体的优化方法让优化器选择一个能够把响应时间减到最小的查询执行计划，一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果的前n行。该参数为Oracle9I新引入vi.以来，参数的缺省设置应是“从OracleV7以来，optimizer_mode参数的缺省设置应是“choose”即如果对已”，在此种设置中，分析的表查询的话选择CBO，，否则选择RBO。。在此种设置中，如果采用了CBO，，模式。则缺省CBO中的All_rows模式。4.共享SQL语句5.rowid的概念：rowid是一个伪列，是系统自己加上的，对于每一个表都有一个rowid。的概念：是一个伪列，是系统自己加上的，。a)为什么使用rowid?i.可以直接定位到相应的数据块上，将其读到内存，我们创建索引时，通过rowid可以直接定位到相应的数据块上，将其读到内存，我们创建索引时，也是存储对应的行的rowid6.DrivingTable驱动表a)该又称外层表（outertable）.这个概念用于嵌套HASH连接中。如果该rowsource返该又称外层表（连接中。）这个概念用于嵌套返回较少的行源的表作为驱动回较多的数据，则对有的后续操作有负面影响。一般用返回较少的行源的表回较多的数据，则对有的后续操作有负面影响。一般用返回较少的行源的表作为驱动表。7.ProbedTable(被探查表被探查表)被探查表a)该表被称为内层表（INNERTABLE）该表被称为内层表（）。b)在我们从驱动表中得到的具体一行的数据后，在该表中寻找符合连接条件的行。所以在我们从驱动表中得到的具体一行的数据后在该表中寻找符合连接条件的行。具体一行的数据后，的表）且相应的列上应该有索引。该表应该为大表实际上应该为返回较大Rowsource的表）（且相应的列上应该有索引。8.组合索引（concatenatedindex）组合索引（）a)由多个列构成的索引，如createindexidx_emponemp(col1,col2,col3….).由多个列构成的索引，b)引导列：在上面的例子中col1为引导列，录我们进行查询时可以使用wherecol1=?,引导列：为引导列，但用：查询时就不会用该索引。或wherecol1=?Andcol2=?.但用：wherecol2=?查询时就不会用该索引。但用查询时就不会用该索引9.可选择性可选择性(selectivity):a)比较列中唯一键的数量和表中的行数，就可以判断该列的可选择性。比较列中唯一键的数量和表中的行数，就可以判断该列的可选择性。10.访问路径（方法）——访问路径（方法）——accesspatha)在物理层，oracle读取数据，一次读取最小单位为数据库块（由多个连续操作系统块在物理层，读取数据，一次读取最小单位为数据库块（
15
本文档来自网上，粘出来供大家分享
组成）所以即使只需要一行数据，，也是将该行所在的数据块读入内在。逻辑上，oracle组成）所以即使只需要一行数据，也是将该行所在的数据块读入内在。逻辑上，用如下存取方法访问数据：用如下存取方法访问数据：i.全表扫描（全表扫描（FullTableScans,FTS））1.为实现全表扫描，oracle读取表中所有的行，并检查每一行是否满足语句的为实现全表扫描，读取表中所有的行，并检查每一行是否满足语句的Where限制条件。Oracl顺序地读取分配给表的第个数据块，真到读表的最限制条件。顺序地读取分配给表的第个数据块，高水平线（标识表的最后一个数据块）。由于高水平线（highwatermark,HWM标识表的最后一个数据块）由于HWM。标识最后一块被读入的数据，标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的其全表扫描的时间不会改善，所有数据被delete后，其全表扫描的时间不会改善，一般用truncate命令来使HWM值归为0.ii.的表存取（通过rowid的表存取（TableAccessbyROWID））a)为了通过ROWID存取表，Oracle首先要获取被选择行的ROWID,或存取表，或从语句的Where子句中得到iii.索引扫描（索引扫描（IndexScan））1.在索引中，在索引中，除了存储每个索引值外，除了存储每个索引值外，还存储此行对应的ROWID。。先通过Index直接从表中得到具体的数据，查找到数据应的Rowid值，然后根据Rowid直接从表中得到具体的数据，此称索引扫描或索引查找。此称索引扫描或索引查找。a)索引唯一扫描（Indexuniquescan）索引唯一扫描（）i.通过唯一索引查找一个数值经常返回单个RowID。。如果该唯一索引有我个列组成即组合索引）则至少要有组合索引的引导列参与到（即组合索引），该查询中。该查询中。b)索引范围扫描（Indexrangescan）索引范围扫描（）i.使用一个索引存取多行数据（三种情况）使用一个索引存取多行数据（三种情况）1.在唯一索引列上使用了rang操作符（><<>>=<=between操作符（like））2.在组合索引上，只使用部分列进行查询，导致查询出多行在组合索引上，只使用部分列进行查询，3.对非唯一索引列上进行任何查询。对非唯一索引列上进行任何查询。c)索引全扫描（Indexfullscan）索引全扫描（）i.与全表扫描对应，也有相应的全索引扫描。与全表扫描对应，也有相应的全索引扫描。ii.在某些情况下，可能进行全索引扫描而不是范围扫描，在某些情况下，可能进行全索引扫描而不是范围扫描，全索引扫描模式才有效。只在CBO模式才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全过引扫描。进行全表扫描更有效时，才进行全过引扫描。d)索引快速扫描索引快速扫描(Indexfastfullscan)i.扫描索引中所有的数据块，很类似，扫描索引中所有的数据块，Indexfulscan很类似，与但是一个显著区别就是它不对查询出的数据进行排序。区别就是它不对查询出的数据进行排序。11.表之间的连接表之间的连接a)典型的连类型共有3种：例：SELECTA.COL1,B.COL2FROMA,BWHEREA.COL3=B.COL4;i.排序——合并连接（——合并连接排序——合并连接（SortMergeJoin））1.过程：过程：a)首先生A需要的数据然后对这些数据按照连接操作关联列（如A.col3）需要的数据然后对这些数据按照连接操作关联列（）进行排序。进行排序。b)随后生成B需要的数据，需要的数据，然后对这些数据按照与sortA对应的连接操作
16
本文档来自网上，粘出来供大家分享
c)
关联列（关联列（如B.COL4）进行排序。）进行排序。最后两边已排序的行被放在一起执行合并操作，最后两边已排序的行被放在一起执行合并操作，即将2个RowSource按照连接起来MERGE
Sort
Sort
A
B
排序是一个费时、费资源的操作，特别对于大表。基于这个原因，排序——排序是一个费时、费资源的操作，特别对于大表。基于这个原因，排序——合并经常不是一个特别有效的连接方法ii.嵌套环（嵌套环（NestedLoops（NL）））1.这个连接方法有驱动表（外部表）的概念。这个连接方法有驱动表（外部表）的概念。2.原理：该连接过程就是一个2层嵌套循环，所以外层循环次数越少越好，也原理：层嵌套循环，所以外层循环次数越少越好，就是为什么将小表作为驱动表的理论依据iii.哈希连接（哈希连接（HashJoin））1.此连接从理论上来说比NL与SMJ更高，而且只用在CBO优化器中更高，2.此连接也有NL连接中所谓的驱动表的概念，被构建为hashtable与bitmap连接中所谓的驱动表的概念，表为驱动表，能被容纳在内存中时，表为驱动表，当被构建的hashtable与bitmap能被容纳在内存中时，这种种连接方式的效率极高。连接方式的效率极高。12.在哪种情况下用哪种连接方法比较好a)排序——合并连接（SortMergeJoin,SMJ）:排序——合并连接（——合并连接）i.非等值连接ii.关联上的列都有索引iii.2个较大的RowSource做连接，访方法比NL要好一些做连接，iv.过大，如果sortmerge返回的rowsource过大，则又会导致过多的rowid在表中查询数数据库性能下降。据，数据库性能下降。b)嵌套循环（NestedLoops,NL）:嵌套循环（）i.外部表)比较小如果drivingrowsource(外部表比较小，并且在innerrowsource（内部表）上有外部表比较小，（内部表）唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。ii.NESTEDLOOPS有其它连接方法没有的一个优点：可以先返回已经连接的行，有其它连接方法没有的一个优点：可以先返回已经连接的行，而不必等待所有的连接作处理完才返回数据，这可以实现快速的响应时间。而不必等待所有的连接作处理完才返回数据，这可以实现快速的响应时间。c)哈希连接（HashJoin,HJ）:哈希连接（）i.种连接，优化器才能取得好的性能。其效率应该好于其它2种连接，但只能用在CBO优化器才能取得好的性能。ii.之间连接时会取得相对较好的效率，在2个较大的RowSource之间连接时会取得相对较好的效率，在一个RowSource较小时则能取得更好效率。较小时则能取得更好效率。iii.只能用于等值连接中。只能用于等值连接中。13.如何干预执行计划——使用hints提示如何干预执行计划—a)使用hints来实现i.使用优化器的类型ii.基于代价的优化器的优化目标：基于代价的优化器的优化目标：是all_rows还是first_rows.iii.表的访问路径，是全表扫描，还是索引，表的访问路径，是全表扫描，还是索引，还是直接利用rowid.
17
本文档来自网上，粘出来供大家分享
表之间的连接类型表之间的连接顺序语句的并行程度b)调整目标i.去掉不必要的大型表的全表扫描ii.缓存小型表的全表扫描iii.检验优化索引的使用iv.检验优化的连接技术v.尽可能减少执行计划的COSTc)范例：范例：i.WHERE子句中有将不使用的索引子句中有!=将不使用的索引ii.WHERE条件对字段增加处理函数将不使用该列的索引iii.避免在索引列上使用ISNul和isnotnulliv.通配符%的使用通配符的使用1.wherecolumn_namelike‘%M’×2.wherecolumn_namelike“M%’√v.减少对表的查询1.如：×selecttable_nameFromtablesWheretable=(selecttab_nameFromtab_columnsWhereversion=604)Anddb_ver=(selectdb_verFromtab_columnsWhereversion=604)2.√selecttable_nameFromtablesWhere(tab_name,db_ver)=(selecttab_name,db_verFromtab_columnsWhereversion=604)d)实例（使用hints）实例（）i./*+ALL_ROWS*/1.表明对语句块选择基于开销优化方法，并获得最佳吞吐量，使资源消耗最小表明对语句块选择基于开销优化方法，并获得最佳吞吐量，化。如：a)SELECT/*+ALL_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;ii./*+FIRST_ROWS*/1.表明对语句块选择基于开销优化方法，表明对语句块选择基于开销优化方法，并获得最佳呼应时间，使资源最小化。并获得最佳呼应时间，使资源最小化。如：a)SELECT/*+FIRST_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;iii./*+CHOOSE*/1.表明如果数据字典中有访问表的统计信息，将基于开销的优化方法，并获得表明如果数据字典中有访问表的统计信息将基于开销的优化方法，如果数据字典中有访问表的统计信息，最佳吞吐量；表明数据字典中没有访问表的统计信息，最佳吞吐量；表明数据字典中没有访问表的统计信息，将基于规则的优化方iv.v.vi.
18
本文档来自网上，粘出来供大家分享
iv.
v.
vi.
vii.
viii.
ix.
x.
xi.
xii.
xiii.
法：如a)SELECT/*+CHOOSE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;/*+RULE*/1.表明对语句块选择基于规则的优化方法，如：表明对语句块选择基于规则的优化方法，a)SELECT/*+RULE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO=’SCOTT’;/*+FULL(TABLE)*/1.表明对表选择全局扫描的方法，如：表明对表选择全局扫描的方法，a)SELECT/*+FULL(A)*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSAWHEREEMP_NO=’SCOTT’;/*+ROWID(TABLE)*/1.表明对指定根据ROWID进行访问，如：进行访问，a)SELECT/*+ROWID(BSEMPMS)*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREROWID>=’AAAAAAAAAAAAAAAA’ANDEMP_NO=’SCOTT’;/*+CLUSTER(TABLE)*/1.提示明确表明对指定表选择簇扫描的访问方法，它只能簇对角有效，如：提示明确表明对指定表选择簇扫描的访问方法，它只能簇对角有效，a)SELECT/*+CLUSTER*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSAWHEREEMP_NO=’SCOTT’;/*+INDEX(TABLEINDEX_NAME)*/1.表明对表选择索引的扫描方法，如：表明对表选择索引的扫描方法，a)SELECT/*+INDEX(BSEMPMSSEX_INDEX)USESEX_INDEXBECAUSETHEREAREFEWMALEBSEMPS*/FROMBSEMPMSWHEESEX=’M’;/*+INDEX_ASC(TABLEINDEX_NAME)*/1.表明对表选择索引升序的扫描方法，如：表明对表选择索引升序的扫描方法，a)SELECT/*+INDEX_ASC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO=’SCOTT’;/*+INDEX_COMBINE*/1.为指定选择位图访问路径，如果INDEX_COMBINE中没有提供作为参数索为指定选择位图访问路径，将选择出位图索引的布尔组合方式，引，将选择出位图索引的布尔组合方式，如:a)SELECT/*+INDEX_COMBINE(BSEMPMSSAL_BMIHIREDATE_BMI)*/FROMBSEMPMSWHERESAL<5000000/*+INDEX_JOIN(TABLEINDEX_NAME)*/1.提示明确命令优化器索引作为访问路径，如：提示明确命令优化器索引作为访问路径，a)SELECT/*+INDEX_JOIN(BSEMPMSSAL_HIMHIREDATE_BMI)*/SAL,HIREDATEFROMBSEMPMSWHERESAL<60000;/*+INDEX_DESC(TABLEINDEX_NAME*/1.表明选择索引降序的扫描方法。如：表明选择索引降序的扫描方法。a)SELECT/*+INDEX_DESC(BSEMPMSPK_BSEMPMS)*FROMBSEMPMSWHEREDPT_NO=’SCOTT’;/*+INDEX_FFS(TABLEINDEX_NAME)*/1.对指定的表执行快速索引扫描，而不是全表扫描的办法。如：对指定的表执行快速索引扫描，而不是全表扫描的办法。
19
本文档来自网上，粘出来供大家分享
xiv.
xv.
xvi.
xvii.xviii.xix.
xx.
xxi.
xxii.
SELECT/*+INDEX_FFS(BSEMPMSIN_DPTNO,IN_EMPNO,IN_SEX)*/FROMBSEMPMSWHEREEMP_NO=’SCOTT’ANDDPT_NO=’TDC306’;/*+ADD_EQUALTABLEINDEX_NAM1,INDEX_NAM2*/1.提明确进行执行规划的选择，将几个单列索引的找描合起来，如：提明确进行执行规划的选择，将几个单列索引的找描合起来，a)SELECT/*+INDEX_FFS(BSEMPMSIN_DEPTNO,IN_EMPNO,IN_SEX)*/FROMBSEMPMSWHEREEMP_NO=’SCOTT’=ANDDPT_NO=’TDC306’;/*+USE_CONCAT*/1.对查询中的WHERE后面的OR条件进行转换为unionall的组合查询，如：的组合查询，a)SELECT/*USE_CONCAT*/FROMBSEMPMSWHEREDPT_NO=’TDC506’ANDSEX=’M’;/*+NO_EXPAND*/1.对于WHERE后面的OR或者IN-LIST的查询语句，NO_EXPAND将阻止的查询语句，其基于优化器对其进行扩展，其基于优化器对其进行扩展，如：a)SELECT/*+NO_EXPAND*/FROMBSEMPMSWHEREDPT_NO=’TDC506’ANDSEX=’M’;/*+NOWRITE*/1.禁止对查询块的查询重写操作。禁止对查询块的查询重写操作。/*+REWRITE*/1.可以将视图作为参数/*+MERGE(TABLE)*/1.能够对视图的各个查询进行相应的合并。如：能够对视图的各个查询进行相应的合并。a)SELECT/*+MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA,(SELECTDPT_NO,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;/*+NO_MERGE(TABLE)*/1.对有可合并的视图不再合并。如对有可合并的视图不再合并。a)SELECT/*+NO_MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA,(SELECTDPT_NO,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;/*+ORDERED*/1.中的顺序，依次此顺序对其连接，根据表出现在From中的顺序，Order使Oracle依次此顺序对其连接，如：a)SELECT/*+ORDERED*/A.COL1,B.COL2,C.COL3FROMTABLE1A,TABLE2B,TABLE3CWHEREA.COL1=B.COL1ANDB.COL1=C.COL1;/*+USE_NL(TABLE)*/1.将指定表与嵌套表的行源进行连接并把指定的表作为内部表。如：将指定表与嵌套表的行源进行连接并把指定的表作为内部表。a)SELECT/*+ORDEREDUSE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_AMFROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
a)
20
本文档来自网上，粘出来供大家分享
xxiii.
xxiv.
xxv.
xxvi.xxvii.
xxviii.
xxix.
xxx.
/*+USE_MERGE(TABLE)*/1.将指定的表与其它行源通过合并排序连接方式连接起来。例如：将指定的表与其它行源通过合并排序连接方式连接起来。例如：a)SELECT/*+USE_MERGE(BSEMPMS,BSDPTMS)*/FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;/*+USE_HASH(TABLE)*/1.将指定的表与其它行源通过哈希连接方式连接起来。如将指定的表与其它行源通过哈希连接方式连接起来。a)SELECT/*+USE_HASH(BSEMPMS,BSDPTMS)*/FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT=NO;/*+DRIVING_SITE(TABLE)*/1.强制与Oracle所选择的位置不同的表进行查询执行。如：所选择的位置不同的表进行查询执行。a)SELECT/*+DRIVING_SITE(DEPT)*/FROMBSEMPMS,DEPT@BSDPTMSWHERFEBSEMPMS.DPT_NO=DEPT.DPT_NO;/*+LEADING(TABLE)*/1.将指定的表作为连接次序的中首表。将指定的表作为连接次序的中首表。/*+CHCHE(TABLE)*/1.当进行全表扫描时，CACHE提示能够将表的检索块放置在缓冲区缓存中最当进行全表扫描时，提示能够将表的检索块放置在缓冲区缓存中最的最近使用端。近最少列表LRU的最近使用端。如：a)SELECT/*+FULL(BSEMPMS)CAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;/*+NOCACHE(TABLE)*/1.进行全表扫描时，CACHE提示能够将表的检索块放置在缓冲区缓存中最近进行全表扫描时，的最近使用端。最少列表LRU的最近使用端。如：a)SELECT/*+FULL(BSEMPMS)NOCACHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;/*+APPEND*/1.直接插入到表的最后，可以提高速度。直接插入到表的最后，可以提高速度。a)INSERT/*+APPEND*/INTOTESTSELECT*FROMTEST4;/*+NOAPPEND*/1.通过在插入语句生存期内停止并行模式来启动常规插入。通过在插入语句生存期内停止并行模式来启动常规插入。a)INSERT/*+APPEND*/INTOTEST1SELECT*FROMTEST4;
高难度SQL
例1：：
有两个不同的用户（会话）在不同的时间点（按照特定的时间顺序）执行了一系列的操作，那么在其中或最后的结果为：time----------T1T2session1session2----------------------------------------------------------------insertintoempvalues(102,2,60)updateempsetdeptno=2whereempno=100
两条update语句都没有提交，不会影响到session1，数据还在session2客户21端1.session2还没有提交2.session1执行update,
本文档来自网上，粘出来供大家分享
T3
updatedeptsetsum_of_salary=(selectsum(salary)fromempwhereemp.deptno=dept.deptno)wheredept.deptnoin(1,2);updatedeptsetsum_of_salary=(selectsum(salary)fromempwhereemp.deptno=dept.deptno)wheredept.deptnoin(1,2);commit;selectsum(salary)fromempgroupbydeptno;问题一：这里会话2的查询结果为：
T4
T5T6
T7commit;=======到这里为此，所有事务都已完成，所以以下查询与会话已没有关系========T8T9selectsum(salary)fromempgroupbydeptno;问题二：这里查询结果为select*fromdept;问题三：这里查询的结果为
问题一的结果(B)问题二的结果是(C)问题三的结果是(E)A:---------------150260C:---------------1502115E:---------------1105260B:---------------150255D:---------------1115250F:---------------1110255
例2：：
以下的null代表真的null，写在这里只是为了让大家看清楚
根据如下表的查询结果，那么以下语句的结果是（知识点：notin/notexists+null）SQL>select*fromusertable;USERID----------123456USERNAME---------------user1nulluser3nulluser5user6
SQL>select*fromusergrade;
22
本文档来自网上，粘出来供大家分享
USERID---------1278
USERNAME---------------user1nulluser7user890808090----------
GRADE
执行语句：
selectcount(*)fromusergradewhereusernamenotin(selectusernamefromusertable);
selectcount(*)fromusergradegwherenotexists(selectnullfromusertabletwheret.userid=g.useridandt.username=g.username);
结果为：语句1(0)
语句2(3)
A:0B:1C:2D:3E:NULL总结：a)selectcount(*)fromusergradewhereusernamenotin(selectusernamefromusertable);.null意思为什么都没有，所以不参加运算（用以下例子可以证明）i.如果将emp表的null都改成：“1a”，那么以上SQL结果为：IDUSERNAME-------------------7user78user8ii.以上说明了Usergrade中第2行username为null,不参加运算iii.因为子句返回值中有null，外层语句与返回的值在建立条件时，遇到null，将停止运算，最终结果返回为0.b)selectcount(*)fromusergradegwherenotexists(selectnullfromusertabletwheret.userid=g.useridandt.username=g.username);IDUSERNAME-------------------8user87user72以下分析：a)子查询selectnull表示没有选择任何列，如果符合子查询的条件，将返回true,所以返回以上结果
例3：：
在以下的表的显示结果中，以下语句的执行结果是（知识点：in/exists+rownum）SQL>select*fromusertable;USERIDUSERNAME-------------------------1user12user23user34user45user5SQL>select*fromusergrade;USERNAMEGRADE------------------------user990user880user780user290user110023
本文档来自网上，粘出来供大家分享user180
执行语句Selectcount(*)fromusertablet1whereusernamein(selectusernamefromusergradet2whererownum<=1);Selectcount(*)fromusertablet1whereexists(select'x'fromusergradet2wheret1.username=t2.usernameandrownum<=1);以上语句的执行结果是：(A)(C)A:0B:1C:2D:3分析：1.Selectcount(*)fromusertablet1whereusernamein(selectusernamefromusergradet2whererownum<=1);这种嵌套查询求解过程是由内向外，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件。2.Selectcount(*)fromusertablet1whereexists(select'x'fromusergradet2wheret1.username=t2.usernameandrownum<=1);带有exists的子查询的求解过程是：首先取外层查询中中表的第1个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回值为真，则取引元组入结果表然后再取下一个元组；重复这一过程，真至外层表全部检查完为止。以上这条SQL，子查询条件为真时，rownum<=1当然为真，会返回true
例4
有表一的查询结果如下，该表为学生成绩表（知识点：关联更新）selectid,gradefromstudent_gradeID-------123456GRADE----------504070803090
表二为补考成绩表selectid,gradefromstudent_makeupID-------125GRADE----------608060现在有一个dba通过如下语句把补考成绩更新到成绩表中，并提交：updatestudent_gradessets.grade=(selectt.gradefromstudent_makeuptwheres.id=t.id);commit;请问之后查询：selectGRADEfromstudent_gradewhereid=3;结果为：cA:0B:70C:nullD:以上都不对分析：1.这条update语句没有where子句，所以也只是在条件返回为真的才给出数据，为假时为null2.updatestudent_gradessets.grade=(selectt.gradefromstudent_makeuptwheres.id=t.id)whereexists(selectt.gradefromstudent_makeuptwheres.id=t.id);这种写法可以得到返回null时不更新（还有没有人可以给一个好的意见）
例4
24
本文档来自网上，粘出来供大家分享根据以下的在不同会话与时间点的操作，判断结果是多少，其中时间T1session1-------------------------------------T1selectcount(*)fromt;--显示结果(1000)条session2----------------------------------------
Session2:此时会等待session1的提交
T2
deletefromtwhererownum<=100;
T3
begindeletefromtwhererownum<=100;commit;end;/
T4
truncatetablet;
1：Truncatetable语句会自动提交2.因session2也在等待session的提交3.此时session1又在待session2的提交，所以会造成死锁，oracle会发现错误，truncate执行失败，最后commit；4.首先session1提交delete其次是session2提交delete
T5
selectcount(*)fromt;--这里显示的结果是多少(C)
A:1000
B:
900
C:
800
D:
0
给Oracle存储过程传入数组(这是自己的)
这段时间以来，我一直都在寻找关于Hibernate调用存储过程传数组方法，都没有找到；后来在读了一偏关于如何操作Blob的文章就有感而发了，这次不用Hibernate，而是用JdbcTemplate：代码如下：1.DATABASETABLE:CREATETABLE"test"."LARGETA"("X"NUMBER(10,0)NOTNULLENABLE,"Y"NUMBER(10,0)NOTNULLENABLE)2.Type:createorreplaceTypeRecords_TypeesAsObject(XNumber(10,0),YNumber(10,0))createorreplaceTypeLarge_Records_ObjectAsTableOfRecords_typees;
3.OraclePROCEDURE:createorreplace25
本文档来自网上，粘出来供大家分享
PROCEDUREINSERTROWS_LARGETA(rtnoutnumber,RsinLarge_Records_Object)ASBeginInsertIntoLargeta(X,Y)SelectX,YFromThe(SelectCast(RsAsLarge_Records_Object)FromDual);Commit;rtn:=sql%ROWCOUNT;ENDINSERTROWS_LARGETA;4.Javacode:
@RepositorypublicclassJdbcCorporateEventDao{privateJdbcTemplatejdbcTemplate;@ResourcepublicvoidsetDataSource(DataSourcedataSource){this.jdbcTemplate=newJdbcTemplate(dataSource);}publicObjectinsertRowsOflargeta(){returnjdbcTemplate.execute(newCallableStatementCreator(){publicCallableStatementcreateCallableStatement(Connectionconn)throwsSQLException{Connectionconn2=newC3P0NativeJdbcExtractor().getNativeConnection(conn);oracle.jdbc.OracleConnectioncon=(oracle.jdbc.OracleConnection)conn2;CallableStatementpstmt=null;Stringsql="{callINSERTROWS_LARGETA(?,?)}";pstmt=con.prepareCall(sql);Object[][]object1=newObject[100][2];for(inti=0;i<100;i++){object1[i][0]=i;object1[i][1]=i;}oracle.sql.ArrayDescriptordesc=oracle.sql.ArrayDescriptor.createDescriptor("LARGE_RECORDS_OBJECT",con);oracle.sql.ARRAYarray=neworacle.sql.ARRAY(desc,con,object1);inti=0;pstmt.setInt(1,i);pstmt.setArray(2,array);returnpstmt;}},newCallableStatementCallback(){publicObjectdoInCallableStatement(CallableStatementpstmt)throwsSQLException,DataAccessException{returnpstmt.executeUpdate();}});}}注意：以上oracle.sql.ArrayDescriptor.createDescriptor("LARGE_RECORDS_OBJECT",con);一定要大写自定义数据类型
26
