首页
登录 | 注册

Forall in Oracle

 

FORALL句的一个关键性改,它可大大化代,并且于那些要在PL/SQL程序中更新很多行数据的程序来,它可著提高其性能

  1:用FORALL来增DML理能力OracleOracle8i中的PL/SQL引入了两个新的数据操纵语言(DML句:BULK COLLECTFORALL.两个句在PL/SQL内部行一组处

  ;BULK COLLECT提供数据的高速索,FORALL可大大改INSERTUPDATEDELETE操作的性能。Oracle数据使用句大大减少了

  PL/SQLSQL行引擎的境切次数,从而使其性能有了著提高。

  使用BULK COLLECT,你可以将多个行引入一个或多个集合中,而不是量或记录中。下面BULK COLLECT例是将标题中包含

  有"PL/SQL"的所有索出来并置于记录的一个关联中,它都位于通向数据一通道中。

  DECLARE TYPE books_aat

  IS TABLE OF book%ROWTYPE INDEX BY PLS_INTEGERbooks books_aatBEGIN SELECT * BULK COLLECT INTO book FROM books WHERE title LIKE '%PL/SQL%'……

  END

  似地,FORALL将数据从一个PL/SQL集合指定的使用集合的表。下面的代码实出一个程,即接收籍信息的一个嵌套表,并将

  集合(定数)的全部内容插入该书籍表中。注意,个例子利用了Oracle9iFORALL的增功能,可以将一条记录直接插入到表中。

  BULK COLLECTFORALL都非常有用,它提高了性能,而且还简化了PL/SQL中的SQL操作所写的代。下面的多行FORALL INSERT相当

  清楚地明了PL/SQL认为Oracle数据的最佳言。

  CREATE TYPE books_nt IS TABLE OF book%ROWTYPE/ CREATE OR REPLACE PROCEDURE add_books

  books_in IN books_nt

  IS BEGIN FORALL book_index IN books_in.FIRST …… books_in.LAST INSERT INTO book VALUES books_inbook_index);……

  END

  不Oracle数据10g之前,以FORAll方式使用集合有一个重要的限制:数据IN子句中的第一行到最后一行,依次取集合的内容

  。如果在内遇到一个未定的行,Oracle数据将引ORA-22160异常事件:

  ORA-22160 element at index [N] does not exist

  FORALL简单应用,规则不会引起任何麻。但是,如果想尽可能地充分利用FORALL,那要求任意FORALL驱动都要依次填充可

  能会增加程序的复杂性并降低性能。

  在Oracle数据10g中,PL/SQL在在FORALL句中提供了两个新子句:INDICES OFVALUES OF,它使你能细选择驱动

  DML句来理的行。

  当定数组为稀疏数或者包含有INDICES OF会非常有用。该语句的FORALL indx IN INDICES

  OF sparse_collection INSERT INTO my_table VALUES sparse_collection indx);

  VALUES OF用于一不同的情况:定数可以是稀疏数,也可以不是,但我只想使用中元素的一个子集。那我就可以使用VALUES

  OF来指向我希望在DML操作中使用的该语句的FORALL indx IN VALUES OF pointer_array INSERT INTO my_table VALUES binding_array indx);

  不用FOR而改用FORALL假定我需要写一个程序,合格工(由comp_analysis.is_eligible函数确定)加薪,于不符合加薪条件的工的告并写入

  employee_history表。我在一个非常大的公司工作;我工非常非常多。

  于一位PL/SQL开发并不是一十分困的工作。我甚至不需要使用BULK COLLECTFORALL就可以完成这项工作,如清 1所示

  ,我使用一个CURSOR FOR独的INSERTUPDATE句。这样的代码简洁明了;不幸地是,我花了10来运行此代,我的"老式"方法

  要运行30或更长时间

  清 1CREATE OR REPLACE PROCEDURE give_raises_in_department dept_in IN employee.department_id%TYPE newsal IN employee.salary%TYPE

  IS CURSOR emp_cur IS SELECT employee_id salary hire_date FROM employee WHERE department_id = dept_inBEGIN FOR emp_rec IN emp_cur LOOP IF comp_analysis.is_eligible emp_rec.employee_id

  THEN UPDATE employee SET salary = newsal WHERE employee_id = emp_rec.employee_idELSE INSERT INTO employee_historyemployee_id salary hire_date activity

  VALUES emp_rec.employee_id emp_rec.salary emp_rec.hire_date 'RAISE DENIED');END IFEND LOOPEND give_raises_in_department

  好在我公司的数据到了Oracle9i,而且更幸运的是,在最近的Oracle会上(以及Oracle网站提供的非常不的演示中)我了解

  到了批量理方法。所以我决定使用集合与批量理方法重新写程序。写好的程序如清 2所示。

  清 21 CREATE OR REPLACE PROCEDURE give_raises_in_department 2     dept_in IN employee.department_id%TYPE 3   newsal IN employee.salary%TYPE 4

  5 IS 6     TYPE employee_aat IS TABLE OF employee.employee_id%TYPE 7        INDEX BY PLS_INTEGER8     TYPE salary_aat IS TABLE OF employee.salary%TYPE 9        INDEX BY PLS_INTEGER10     TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE 11        INDEX BY PLS_INTEGER12 13     employee_ids employee_aat14     salaries salary_aat15     hire_dates hire_date_aat16 17     approved_employee_ids employee_aat18 19     denied_employee_ids employee_aat20     denied_salaries salary_aat21     denied_hire_dates hire_date_aat22 23     PROCEDURE retrieve_employee_info 24     IS 25     BEGIN 26        SELECT employee_id salary hire_date 27        BULK COLLECT INTO employee_ids salaries hire_dates 28          FROM employee 29         WHERE department_id = dept_in30     END31 32     PROCEDURE partition_by_eligibility 33     IS 34     BEGIN 35        FOR indx IN employee_ids.FIRST …… employee_ids.LAST 36        LOOP 37           IF comp_analysis.is_eligible employee_ids indx))

  38           THEN 39              approved_employee_ids indx = employee_ids indx);40           ELSE 41              denied_employee_ids indx = employee_ids indx);42              denied_salaries indx = salaries indx);43              denied_hire_dates indx = hire_dates indx);44           END IF45        END LOOP46     END47 48     PROCEDURE add_to_history 49     IS 50     BEGIN 51        FORALL indx IN denied_employee_ids.FIRST …… denied_employee_ids.LAST 52           INSERT INTO employee_history 53                       employee_id 54                      salary 55                      hire_date activity 56                      

  57                VALUES denied_employee_ids indx

  58                      denied_salaries indx

  59                      denied_hire_dates indx), 'RAISE DENIED' 60                       );61     END62 63     PROCEDURE give_the_raise 64     IS 65     BEGIN 66        FORALL indx IN approved_employee_ids.FIRST …… approved_employee_ids.LAST 67           UPDATE employee 68              SET salary = newsal 69            WHERE employee_id = approved_employee_ids indx);70     END71 BEGIN 72     retrieve_employee_info73     partition_by_eligibility74     add_to_history75     give_the_raise76 END give_raises_in_department

  一眼清1 和清2 就会清楚地认识到:改用集合和批量理方法将增加代量和复杂性。但是,如果你需要大幅度提升性能,这还

  的。下面,我不看些代,我来看一看当使用FORALL,用什CURSOR FOR内的条件逻辑

  定集合型与集合

  在清 2中,声明段的第一部分(第6行至第11行)定了几不同的集合型,与我将从工表索出的列相对应。我更喜基于employee%

  ROWTYPE来声明一个集合型,但是FORALL不支持某些记录集合的操作,在这样记录中,我将引用个字段。所以,我须为员ID

  薪金和雇用日期分声明其各自的集合。

  接下来为每一列声明所需的集合(第13行至第21行)。首先定与所查询列相对应的集合(第13行至第15行):

  employee_ids employee_aatsalaries salary_aathire_dates hire_date_aat

  然后我需要一个新的集合,用于存放已被批准加薪的工的ID(第17行):

  approved_employee_ids employee_aat

  最后,我再为每一列声明一个集合(第19行至第21行),用于记录没有加薪格的工:

  denied_employee_ids employee_aatdenied_salaries salary_aatdenied_hire_dates hire_date_aat

  深入了解代

  数据构确定后,我们现在跳过该程序的行部分(第72行至第75行),了解如何使用些集合来加速程。

  retrieve_employee_infopartition_by_eligibilityadd_to_historygive_the_raise

  我写此程序使用了逐步细化法(也被称"向下设计")。所以行部分不是很,也不理解,只有四行,按名称对过程中的步进

  行了描述。首先工信息(指定部的所有工)。然后行划分,将要加薪和不予加薪的工区分出来。完成之后,我就可以将那些不

  予加薪的工添加至史表中,其他行加薪。

  以这种方式写代使最终结果的可性大大增。因而我可以深入到程序中我有意的任何部分。

  有了已声明的集合,我在就可以使用BULK COLLECT工信息(第23行至第30行)。一部分有效地替代了CURSOR FOR。至此,数

  据被加到集合中。

  划分逻辑(第32行至第46行)要求对刚刚填充的集合中的一行检查,看其是否符合加薪条件。如果符合,我就将该员ID查询填充的

  集合制到符合条件的工的集合。如果不符合,则复该员ID、薪金和雇用日期,因为这些都需要插入到employee_history表中。

  初始数据在已被分两个集合,可以将其分用作两个不同的FORALL句(分从第51行和第66始)的驱动器。我将不合格工的集合

  中的数据批量插入到employee_historyadd_to_history)表中,并通give_the_raise程,在employee表中批量更新合格工的信息。

  最后再仔地看一看add_to_history(第48行至第61行),以此来对这个重新写的程序的分析。FORALL句(第51行)包含一个IN子句

  ,它指定了要用于批量INSERT的行号范。在程序行第二次重写的明中,我将把用于定的集合称"驱动集合".但在

  add_to_history一版本中,我简单假定: 使用在denied_employee_ids中定的所有行。在INSERT自身内部,于不合格工的三个集

  合都会被用到;我将把些集合称"数据集合".可以看到,驱动集合与数据集合无需匹配。在学Oracle数据10g的新特性是一个

  点。

  果,清 2 的行数大是清 1行数的2倍,但是清 2 中的代会在要求的时间内运行。在使用Oracle数据10g之前,在这种情况下,

  我只会时间内运行代始下一个任务这一点感到高

  不,有了Oracle数据10g中最新版的PL/SQL在我就可以在性能、可性和代量方面作出更多的改

  将VALUES OF用于此

  在Oracle数据10g中,可以指定FORALL句使用的驱动集合中的行的子集。可以使用以下两方法之一来定义该子集:

  将数据集合中的行号与驱动集合中的行号行匹配。你需要使用INDICES OF子句。

  将数据集合中的行号与驱动集合中所定行中找到的值进行匹配。需要使用VALUES OF子句。

  在give_raises_in_department行第二次和最后一次改写中我将使用VALUES OF子句。清 3 包含个版本的全部代。我将略过这一程序

  中与前一版本相同的部分。

  从声明集合始,注意我不再另外定集合来存放合格的和不合格的工信息,而是在清 3 (第17行至第21行)中声明两个""集合:

  一个用于符合加薪要求的工,另一个用于不符合加薪要求的工。两个集合的数据型都是布型;不久将会看到,些集合的数据

  与FORALL句毫无系。FORALL句只心定了哪些行。 工表中50 000行信息的give_raises_in_department的三种执行方法的占

  用时间 行方法 CURSOR FOR 000038.01 Oracle数据10g之前的批量 000006.09 Oracle数据10g的批量 000002.06

  在工表中100000行数据的give_raises_in_department的三种执行方法的占用时间 行方法 CURSOR FOR 000058.01 Oracle数据10g之前的批量 000012.00 Oracle数据10g的批量 000005.05

  表150000行和100000行数据的用时测试结

  retrieve_employee_info子程序与前面的相同,但是数据行划分的方式完全不同(第32行至第44行)。我没有将记录从一个集合制到另

  一个集合(个操作相对较慢),而只是确定与ID集合中的行号相匹配的相集合中的行(通过为其指定一个TRUE)。

  在可以在两个不同FORALL句(由第49行和第65始)中,将approved_listdenied_list集合用作驱动集合。

  了插入到employee_history表中,我使用了如下句:

  FORALL indx IN VALUES OF denied_list

  行更新(给员行加薪),我使用一格式:

  FORALL indx IN VALUES OF approved_list

  在两个DML句中,数据集合是在BULK COLLECT 步骤中填充的最初的集合;没有过复制。利用VALUES OFOracle数据些数据

  集合的行中筛选使用行号与驱动集合中行号相匹配的行利用本程序中的VALUES OF,可以避免全部记录进制,而是用行号的一个简单列表来替于大型数制的开销

  是非常可的。测试Oracle数据10g越性,我装入employee表并50000行和100000行的数据运行测试了模更多的现实情况

  ,我将Oracle数据10g之前的批量理的行方法作了修改以行集合内容的多次制。然后我使用SQL*Plus SET TIMING ON示运行各个

  不同的行方法所用的时间。表 1 出了果。

  从时间测定得到的结论非常清楚:由DML变为批量理将大幅短耗用时间,数据50000的用38秒减6秒,数据

  100000的用58秒减12秒。而且,通使用VALUES OF来避免制数据,我可以将用时缩短一半左右。

  即使没有性能上的改VALUES OF及其同子句——INDICES OF也提高了PL/SQL言的灵活性,使开发松地写出更直和更容易

  维护的代

  在品寿命一点上天空PL/SQL是一成熟且功能大的言。因而,其很多新特性都是逐增加和改而成的。不些新特性是使

  程序的性能和开发开发效率有了重大改VALUES OF就是这种特性的一个很好的例


相关文章

  • Oracle sysaux表空间使用过大处理-3
    博客文章除注明转载外,均为原创.转载请注明出处. Oracle sysaux表空间使用过大处理第三篇,前两篇请见Oracle sysaux表空间使用过大处理-1(http://blog.chinaunix.net/uid-31396856- ...
  • Oracle sysaux表空间使用过大处理-2
    博客文章除注明转载外,均为原创.转载请注明出处. 接上文(Oracle sysaux表空间使用过大处理-1http://blog.chinaunix.net/uid-31396856-id-5819732.html)介绍另一种处理方法: 基 ...
  • Oracle sysaux表空间使用过大处理-1
    博客文章除注明转载外,均为原创.转载请注明出处. Oracle sysaux表空间占用过大处理办法: step1:检查表空间的使用情况 sysaux表空间空间使用是很大的. step2:检查表空间被内容占用: 主要是awr信息库使用了sys ...
  • oracle10 版本 jdbc 驱动中 OraclePreparedStatement 执行excuteQuery 时返回的ResultSet的日期类型字段使用getObject 获得对象时,返回的是 java.sql.Date .  这 ...
  • 在用Oracle的SQL*PLUS的时候SPOOL真是好用啊.在MySQL中如何实现呢?下面来实验说明 MySQL中可以用tee参数具体实现方式有三种 第一种:tee参数加入my.cnf参数文件 例子: tee =/mysql/mysql_ ...
  • Oracle 12C Sharding部署和测试
    Oracle 12C Sharding部署和测试 日期:2019-02-26 作者:陈举超   一:环境说明 OS:CentOS Linux release 7.5 DB:Oracle 12.2.0.1.0 GSMOCI:2.2.1 本次使 ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.013 s.
京ICP备10005923号