Oracle-如何给Large Delete,Insert,Update操作提 速近千倍

《Oracle-如何给Large Delete,Insert,Update操作提 速近千倍》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、1.  背景描述1.1.任务描述这个任务是需要从一系列大表中清理3个省的大批过时数据，具体的清理过程简单的说就是：首先根据不同的miscid的值创建不同的临时表，类似于： CREATETABLEtemp_midASSELECTmidFROMssrWHERESUBSTR(ssid,1,7)IN(SELECTprefixnumFROMprefixWHEREmcid='0012'); 然后通过这个临时表连接另一个大表，做删除工作：DELETESSFWHEREmidIN(SELECTmidFROMTEMP_MID_HUBEI)

2、;  上述任务根据不同的关键字，需要执行几十次，如果不加任何优化的话，每一次都需要执行几十个小时。由于需求、操作和优化思路大体相同，下面我们就以上面的例子详细说说实际应用中如何一步步优化提速到近千倍的过程。  1.2.数量级统计和描述首先统计这个操作涉及到的几张表： SELECTCOUNT(*)FROMPREFIX; SELECTCOUNT(*)SSRFROMSSR; SELECTCOUNT(*)SSFFROMSSF; SELECTCOUNT(*)ASSSF_0012FROMSSFWHEREMIDIN(SELECTMI

3、DFROMTEMP_MID_HUBEI);  上述脚本的执行过程如下（请注意，由于创建临时表TEMP_MID_HUBEI的过程比较简单，因此这里没有赘述，仅仅是从建立临时表后的删除操作开始分析的）： SQL>@LUNAR.SQL PREFIX----------51854 ELAPSED:00:00:00.14 SSF-----------83446270 ELAPSED:00:04:53.27 SSR----------43466645 ELAPSED:00:03:08.00 SSF_0012------------

4、----131098 ELAPSED:00:00:57.02SQL>  我们注意到，这里面，我们需要做的是从一个8300多万行的大表中，通过和一个130多万行的表进行连接并删除其中的大部分数据。整个操作的过程，要求所有的表都可以实时访问，并且除了我们手工建立的临时表（TEMP_MID_HUBEI）以外，其他的表都可以实时访问和修改。   2.  背景知识——BulkBinding在下面的优化过程中，我们使用了批量绑定（BulkBinding）的思想，因此首先对这一知识作些解释。  2.1.什么是BulkBinding？

5、在sql语句中（动态地）给PL/SQL变量赋值叫做绑定（Binding）。一次绑定一个完整的集合叫做批量绑定（BulkBinding）。从Oracle8i开始，在PL/SQL可以使用两个新的数据操纵语言（DML）语句：BULKCOLLECT和FORALL。这两个语句在PL/SQL内部按数组进行数据处理。  2.2.Bulkbinds的优点是什么呢？批量绑定（Bulkbinds）通过最小化在PL/SQL和SQL引擎之间的上下文切换提高了性能，它以一个完整的集合（如，varray,nestedtables,index-by

6、table,orhostarray）为单位（一批一批的）向前或者向后绑定变量。在Oracle8i以前，每个SQL语句的执行需要在PL/SQL和SQL引擎之前切换上下文，使用绑定变量后，就只需要一次上下文切换。其中，BULKCOLLECT提供对数据的高速检索，FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。  2.3.如何进行批量绑定（BulkBinds）？绑定变量包扩下面两个部分：1)       输入集合（collections），使用FORALL语句，一般用来改善DML（INSERT、UP

7、DATE和DELETE)操作的性能；2)       输出集合（collections），使用BULKCOLLECT子句；一般用来提高查询（SELECT）的性能。 2.3.1.   输入集合(FORALL)输入集合是数据通过PL/SQL引擎到SQL引擎去执行INSERT,UPDATE,DELETE语句。输入集合使用FORALL语句，下面是FORALL的语法： FORALLindexINlower_bound..upper_boundsql_statement; 2.3.2.   FOR..LOOP语句和FORALL的比

8、较例1（example1）：分别使用传统的FOR..LOOP操作和我们这里介绍的FORALL操作向lunartest表中加载1000000条记录，对比一下他们的执行效率。 测试过程如下：首先创建一个用来记录操作时间的存储过程get_time：CREATEORREPLACEPROCEDUREget_time(tOUTNUMBER)I