`
wsql
  • 浏览: 12034296 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

Oracle Bulk 与 性能优化 说明

 
阅读更多

一. Bulk概述

本来只想测试一下Bulk Collect和update性能的,但发现Bulk的东西还是很多的,在OTN上搜了一些,整理如下。

1.1 Bulk Binding和Bulk SQL

From:http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_packages.htm#ADFNS343

Oracle Databaseuses two engines to run PL/SQL blocks and subprograms.The PL/SQL engineruns procedural statements, whilethe SQL engineruns SQL statements. During execution,every SQL statement causes a context switch between the two engines, resulting in performance overhead.

-- Oracle使用2个引擎来执行SQL和代码块:SQL引擎和PL/SQL引擎,SQL语句会导致在两个引擎之间进行context switch,从而影响性能。

Performance can be improved substantially byminimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that usescollection elements as bind variables, the large number of context switches required by the block can cause poor performance.Collections include:

(1)Varrays

(2)Nested tables

(3)Index-by tables

(4)Host arrays

--从本质上讲,使用特殊的block或者subprogram来降低context switches可以提高性能。当SQL语句在loop内使用collection elements作为bind variables来运行时,就会产生大量的context switches。

Bulk SQLminimizes the performance overhead of the communication between PL/SQL and SQL.

PL/SQL and SQL communicate as follows:

To run a SELECT INTO or DML statement, the PL/SQL engine sends the query or DML statement to the SQL engine. The SQL engine runs the query or DML statement and returns the result to the PL/SQL engine.

-- PL/SQL和SQL引擎的交流方式

The PL/SQL features that comprise bulk SQL are the FORALL statement and the BULK COLLECT clause.

TheFORALL statementsends DML statements from PL/SQL to SQL in batches rather thanone at a time.

The BULK COLLECTclause returns results from SQL to PL/SQL in batches rather than one at a time. If a query or DML statement affects four or more database rows, then bulk SQL can significantly improve performance.

Assigning values to PL/SQL variables that appear in SQL statements iscalledbinding.

PL/SQL binding operations fall into these categories:

Binding Category

When This Binding Occurs

In-bind

When an INSERT or UPDATE statement stores a PL/SQL or host variable in the database

Out-bind

When the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement assigns a database value to a PL/SQL or host variable

DEFINE

When a SELECT or FETCH statement assigns a database value to a PL/SQL or host variable

For in-binds and out-binds, bulk SQL usesbulk binding;that is, it binds an entire collection of values at once.

For a collection of n elements, bulk SQL uses a single operationto perform the equivalent of n SELECT INTO or DML statements. A query that uses bulk SQL can return any number of rows, without using a FETCH statement for each one.

Binding is the assignment of values to PL/SQL variables in SQL statements.Bulk binding isbinding an entire collection at once. Bulk binds pass the entire collection back andforth between the two engines in a single operation.

--Binding是在SQL语句里分配一个value给PL/SQL变量

--Bulk Binding是一次分配所有的数据,然后通过这个entire collection,在一个操作就可以完成两个引擎处理。

Typically, using bulk binds improves performance for SQL statements that affect four or more database rows.The more rows affected by a SQL statement, the greater the performance gain from bulk binds.

注意:

Parallel DML statements are disabled with bulk binds and bulk SQL.

并行的DML操作会禁用bulk binds和bulk SQL.

Note:

This section provides an overview of bulk binds to help you decide whether to use them in your PL/SQL applications. For detailed information about using bulk binds, including ways to handle exceptions that occur in the middle of a bulk bind operation, seeOracle Database PL/SQL Language Reference.

1.2 When to Use Bulk Binds

Consider using bulk binds to improve the performance of:

DML Statements that Reference Collections

SELECT Statements that Reference Collections

FOR Loops that Reference Collections and Return DML

1.2.1DML Statements that Reference Collections

A bulk bind,which uses the FORALL keyword, can improve the performance of INSERT, UPDATE, or DELETE statements that reference collection elements.

The PL/SQL block inExample 6-9increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, with and without bulk binds. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each updated employee, leading to context switches that slow performance.

Example 6-9 DML Statements that Reference Collections

declare

type numlist is varray (100) of number;

id numlist := numlist(7902, 7698, 7839);

begin

-- Efficient method, using bulk bind:

foralli in id.first..id.last

update employees

set salary = 1.1 * salary

where manager_id = id(i);


-- Slower method:

fori in id.first..id.lastloop

update employees

set salary = 1.1 * salary

where manager_id = id(i);

end loop;

end;

/

1.2.2SELECT Statements that Reference Collections

TheBULK COLLECT INTO clause can improve the performance of queries that reference collections. You can use BULK COLLECT INTO with tables of scalar values, or tables of %TYPE values.

The PL/SQL block inExample 6-10queries multiple values into PL/SQL tables, with and without bulk binds. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each selected employee, leading to context switches that slow performance.

Example 6-10 SELECT Statements that Reference Collections

declare

type var_tab is table of varchar2(20)

index by pls_integer;

empnovar_tab;

enamevar_tab;

counternumber;

cursor c is

select employee_id, last_name

from employees

where manager_id = 7698;

begin

-- Efficient method, using bulk bind:

select employee_id, last_name bulk collect

into empno, ename

from employees

where manager_id = 7698;

-- Slower method:

counter := 1;

for rec in c loop

empno(counter) := rec.employee_id;

ename(counter) := rec.last_name;

counter := counter + 1;

end loop;

end;

/

1.2.3FOR Loops that Reference Collections and Return DML

You can use the FORALL keyword with the BULK COLLECT INTO keywords to improve the performance of FOR loops that reference collections and return DML.

The PL/SQL block inExample 6-11updates the EMPLOYEES table by computing bonuses for a collection of employees. Then it returns the bonuses in a column called bonus_list_inst. The actions are performed with and without bulk binds. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each updated employee, leading to context switches that slow performance.

Example 6-11 FOR Loops that Reference Collections and Return DML

declare

type emp_list is varray(100) of employees.employee_id%type;

empids emp_list := emp_list(182, 187, 193, 200, 204, 206);

type bonus_list is table of employees.salary%type;

bonus_list_instbonus_list;

begin

-- Efficient method, using bulk bind:

forall i in empids.first..empids.last

update employees

set salary = 0.1 * salary

where employee_id = empids(i)

returning salary bulk collect into bonus_list_inst;


-- Slower method:

fori in empids.first..empids.lastloop

update employees

set salary = 0.1 * salary

where employee_id = empids(i)

returning salary into bonus_list_inst(i);

end loop;

end;

/

1.3 Triggers

A trigger is a special kind of PL/SQL anonymous block. You can define triggers to fire before or after SQL statements, either on a statement level or for each row that is affected. You can also define INSTEAD OF triggers or system triggers (triggers on DATABASE and SCHEMA).

二.有关Bulk SQL和Bulk Binding的更多示例

From:

Bulk SQL and Bulk Binding

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/tuning.htm

2.1FORALL Statement

TheFORALL statement, a feature of bulk SQL, sends DML statements from PL/SQL to SQL in batches rather than one at a time.

To understand the FORALL statement, first consider theFOR LOOP statement inExample 12-7.It sends these DML statements from PL/SQL to SQLone at a time:

delete from employees_temp where department_id = depts(10);

delete from employees_temp where department_id = depts(30);

delete from employees_temp where department_id = depts(70);

Example 12-7 DELETE Statement in FOR LOOP Statement

DROP TABLE employees_temp;

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE

TYPE NumList IS VARRAY(20) OF NUMBER;

depts NumList := NumList(10, 30, 70);-- department numbers

BEGIN

FORi IN depts.FIRST..depts.LASTLOOP

DELETE FROM employees_temp

WHERE department_id = depts(i);

END LOOP;

END;

/

Now consider the FORALL statement inExample 12-8.It sends the same threeDML statements from PL/SQL to SQL as a batch.

Example 12-8 DELETE Statement in FORALL Statement

DROP TABLE employees_temp;

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE

TYPE NumList IS VARRAY(20) OF NUMBER;

depts NumList := NumList(10, 30, 70);-- department numbers

BEGIN

FORALL i IN depts.FIRST..depts.LAST

DELETE FROM employees_temp

WHERE department_id = depts(i);

END;

/

A FORALL statement is usuallymuch faster than an equivalent FOR LOOP statement.However, aFOR LOOP statement can contain multiple DML statements,while a FORALL statement can contain only one.

--FORALL只能包含一条DML语句,而FOR LOOP可以包含多条

The batch of DML statements that a FORALL statement sends to SQL differ only in their VALUES and WHERE clauses. The values in those clauses must come from existing, populated collections.

Note:

The DML statement in a FORALL statement can reference multiple collections, but performance benefits apply only to collection references that use the FORALL index variable as an index.

Example 12-9inserts the same collection elements into two database tables, using a FOR LOOP statement for the first table and a FORALL statement for the second table and showing how long each statement takes. (Times vary from run to run.)

Example 12-9 Time Difference for INSERT Statement in FOR LOOP and FORALL Statements

DROP TABLE parts1;

CREATE TABLE parts1 (

pnum INTEGER,

pname VARCHAR2(15)

);

DROP TABLE parts2;

CREATE TABLE parts2 (

pnum INTEGER,

pname VARCHAR2(15)

);


DECLARE

TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;

TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;

pnumsNumTab;

pnamesNameTab;

iterationsCONSTANT PLS_INTEGER := 50000;

t1INTEGER;

t2INTEGER;

t3INTEGER;

BEGIN

FOR j IN 1..iterations LOOP-- populate collections

pnums(j) := j;

pnames(j) := 'Part No. ' || TO_CHAR(j);

END LOOP;

t1 := DBMS_UTILITY.get_time;

FOR i IN 1..iterations LOOP

INSERT INTO parts1 (pnum, pname)

VALUES (pnums(i), pnames(i));

END LOOP;


t2 := DBMS_UTILITY.get_time;


FORALL i IN 1..iterations

INSERT INTO parts2 (pnum, pname)

VALUES (pnums(i), pnames(i));

t3 := DBMS_UTILITY.get_time;

DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');

DBMS_OUTPUT.PUT_LINE('---------------------');

DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100));

DBMS_OUTPUT.PUT_LINE('FORALL:' || TO_CHAR((t3 - t2)/100));

COMMIT;

END;

/

Result is similar to:

Execution Time (secs)

---------------------

FOR LOOP: 2.16

FORALL:.11


PL/SQL procedure successfully completed.

InExample 12-10, the FORALL statement applies to a subset of a collection.

Example 12-10 FORALL Statement for Subset of Collection

DROP TABLE employees_temp;

CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE

TYPE NumList IS VARRAY(10) OF NUMBER;

depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);

BEGIN

FORALL j IN 4..7

DELETE FROM employees_temp WHERE department_id = depts(j);

END;

/

2.2FORALL Statements for Sparse Collections

If theFORALL statement bounds clause references asparse collection,then specify only existing index values, using either the INDICES OF or VALUES OF clause. You can use INDICES OF for any collection except an associative array indexed by string. You can use VALUES OF only for a collection of PLS_INTEGER elements indexed by PLS_INTEGER.

A collection of PLS_INTEGER elements indexed by PLS_INTEGER can be anindex collection; that is, a collection of pointers to elements of another collection (the indexed collection).

Index collections are useful for processing differentsubsets of the same collection with different FORALL statements.Instead of copying elements of the original collection into new collections that represent the subsets (which can use significant time and memory), represent each subset with an index collection and then use each index collection in the VALUES OF clause of a different FORALL statement.

Example 12-11uses a FORALL statement with the INDICES OF clause to populate a table with the elements of a sparse collection. Then it uses two FORALL statements with VALUES OF clauses to populate two tables with subsets of a collection.

Example 12-11 FORALL Statements for Sparse Collection and Its Subsets

DROP TABLE valid_orders;

CREATE TABLE valid_orders (

cust_nameVARCHAR2(32),

amountNUMBER(10,2)

);

DROP TABLE big_orders;

CREATE TABLE big_orders AS

SELECT * FROM valid_orders

WHERE 1 = 0;

DROP TABLE rejected_orders;

CREATE TABLE rejected_orders AS

SELECT * FROM valid_orders

WHERE 1 = 0;


DECLARE

SUBTYPE cust_name IS valid_orders.cust_name%TYPE;

TYPE cust_typ IS TABLE OF cust_name;

cust_tabcust_typ;-- Collection of customer names

SUBTYPE order_amount IS valid_orders.amount%TYPE;

TYPE amount_typ IS TABLE OF NUMBER;

amount_tabamount_typ;-- Collection of order amounts

TYPE index_pointer_t IS TABLE OF PLS_INTEGER;


/* Collections for pointers to elements of cust_tab collection

(to represent two subsets of cust_tab): */


big_order_tabindex_pointer_t := index_pointer_t();

rejected_order_tabindex_pointer_t := index_pointer_t();

PROCEDUREpopulate_data_collectionsIS

BEGIN

cust_tab := cust_typ(

'Company1','Company2','Company3','Company4','Company5'

);

amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);

END;

BEGIN

populate_data_collections;

DBMS_OUTPUT.PUT_LINE ('--- Original order data ---');

FOR i IN 1..cust_tab.LAST LOOP

DBMS_OUTPUT.PUT_LINE (

'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)

);

END LOOP;


-- Delete invalid orders:

FOR i IN 1..cust_tab.LAST LOOP

IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN

cust_tab.delete(i);

amount_tab.delete(i);

END IF;

END LOOP;

-- cust_tab is now a sparse collection.

DBMS_OUTPUT.PUT_LINE ('--- Order data with invalid orders deleted ---');

FOR i IN 1..cust_tab.LAST LOOP

IF cust_tab.EXISTS(i) THEN

DBMS_OUTPUT.PUT_LINE (

'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)

);

END IF;

END LOOP;


-- Using sparse collection, populate valid_orders table:

FORALL i IN INDICES OF cust_tab

INSERT INTO valid_orders (cust_name, amount)

VALUES (cust_tab(i), amount_tab(i));


populate_data_collections;-- Restore original order data

-- cust_tab is a dense collection again.


/* Populate collections of pointers to elements of cust_tab collection

(which represent two subsets of cust_tab): */


FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP

IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN

rejected_order_tab.EXTEND;

rejected_order_tab(rejected_order_tab.LAST) := i;

END IF;

IF amount_tab(i) > 2000 THEN

big_order_tab.EXTEND;

big_order_tab(big_order_tab.LAST) := i;

END IF;

END LOOP;

/* Using each subset in a different FORALL statement,

populate rejected_orders and big_orders tables: */


FORALL i IN VALUES OF rejected_order_tab

INSERT INTO rejected_orders (cust_name, amount)

VALUES (cust_tab(i), amount_tab(i));

FORALL i IN VALUES OF big_order_tab

INSERT INTO big_orders (cust_name, amount)

VALUES (cust_tab(i), amount_tab(i));

END;

/

2.3Unhandled Exceptions in FORALL Statements

In aFORALL statement without the SAVE EXCEPTIONS clause,if one DML statement raises anunhandled exception, then PL/SQL stops the FORALL statement and rolls back all changes made by previous DML statements.

For example, the FORALL statement inExample 12-8executes these DML statements in this order, unless one of them raises an unhandled exception:

DELETE FROM employees_temp WHERE department_id = depts(10);

DELETE FROM employees_temp WHERE department_id = depts(30);

DELETE FROM employees_temp WHERE department_id = depts(70);

If the third statement raises an unhandled exception, then PL/SQL rolls back the changes that the first and second statements made. If the second statement raises an unhandled exception, then PL/SQL rolls back the changes that the first statement made and never runs the third statement.

You canhandle exceptions raised in a FORALL statement in either of these ways:

(1)As each exception is raised (see"Handling FORALL Exceptions Immediately")

(2)After the FORALL statement completes execution, by including the SAVE EXCEPTIONS clause (see"Handling FORALL Exceptions After FORALL Statement Completes")

2.4 Handling FORALL Exceptions Immediately

To handle exceptions raised in a FORALL statementimmediately, omit the SAVE EXCEPTIONS clause and write the appropriate exception handlers. (For information about exception handlers, seeChapter 11, "PL/SQL Error Handling.") If one DML statement raises a handled exception, then PL/SQL rolls back the changes made by that statement,but does not roll back changes made by previous DML statements.

InExample 12-12, the FORALL statement is designed to run three UPDATE statements. However, the second one raises an exception. An exception handler handles the exception, displaying the error message and committing the change made by the first UPDATE statement. The third UPDATE statement never runs.

Example 12-12 Handling FORALL Exceptions Immediately

DROP TABLE emp_temp;

CREATE TABLE emp_temp (

deptno NUMBER(2),

job VARCHAR2(18)

);

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS

TYPE NumList IS TABLE OF NUMBER;

deptsNumList := NumList(10, 20, 30);

error_messageVARCHAR2(100);

BEGIN

-- Populate table:

INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');

INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');

INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');

COMMIT;

-- Append 9-character string to each job:


FORALL j IN depts.FIRST..depts.LAST

UPDATE emp_temp SET job = job || ' (Senior)'

WHERE deptno = depts(j);


EXCEPTION

WHEN OTHERS THEN

error_message := SQLERRM;

DBMS_OUTPUT.PUT_LINE (error_message);

COMMIT;-- Commit results of successful updates

RAISE;

END;

/

Result:

Procedure created.

Invoke procedure:

BEGIN

p;

END;

/

Result:

ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19,

maximum: 18)

ORA-06512: at "HR.P", line 27

ORA-06512: at line 2

PL/SQL procedure successfully completed.

Query:

SELECT * FROM emp_temp;

Result:

DEPTNO JOB

---------- ------------------

10 Clerk (Senior)

20 Bookkeeper

30 Analyst

3 rows selected.


2.5 Handling FORALL Exceptions After FORALL Statement Completes

To allow a FORALL statement to continue even if some of its DML statements fail, include the SAVE EXCEPTIONS clause.When a DML statement fails, PL/SQL does not raise an exception; instead, it saves information about the failure. After the FORALL statement completes, PL/SQL raises a single exception for the FORALL statement (ORA-24381).In the exception handler forORA-24381, you can get information about each individual DML statement failure from the implicit cursor attributeSQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONSis like an associative array of information about the DML statements that failed during the most recently run FORALL statement.

SQL%BULK_EXCEPTIONS.COUNTis the number of DML statements that failed. If SQL%BULK_EXCEPTIONS.COUNT is not zero, then for each index value i from 1 through SQL%BULK_EXCEPTIONS.COUNT:

(1)SQL%BULK_EXCEPTIONS(i).ERROR_INDEX is the number of the DML statement that failed.

(2)SQL%BULK_EXCEPTIONS(i).ERROR_CODE is the Oracle Database error code for the failure.

For example, if a FORALL SAVE EXCEPTIONS statement runs 100 DML statements, and the tenth and sixty-fourth ones fail with error codesORA-12899andORA-19278, respectively, then:

SQL%BULK_EXCEPTIONS.COUNT = 2

SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10

SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899

SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64

SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278

Note:

After a FORALL statementwithout the SAVE EXCEPTIONS clauseraises an exception,SQL%BULK_EXCEPTIONS.COUNT = 1.

With the error code, you can get the associated error message with theSQLERRM function (described in"SQLERRM Function"):

SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))

However, the error message that SQLERRM returns excludes any substitution arguments (compare the error messages inExample 12-12andExample 12-13).

Example 12-13is likeExample 12-12except:

(1)The FORALL statement includes the SAVE EXCEPTIONS clause.

(2)The exception-handling part has an exception handler forORA-24381, the internally defined exception that PL/SQL raises implicitly when a bulk operation raises and saves exceptions. The example givesORA-24381the user-defined name dml_errors.

(3)The exception handler for dml_errors uses SQL%BULK_EXCEPTIONS and SQLERRM (and some local variables)to show the error message and which statement, collection item, and string caused the error.

Example 12-13 Handling FORALL Exceptions After FORALL Statement Completes

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS

TYPE NumList IS TABLE OF NUMBER;

deptsNumList := NumList(10, 20, 30);

error_messageVARCHAR2(100);

bad_stmt_noPLS_INTEGER;

bad_deptnoemp_temp.deptno%TYPE;

bad_jobemp_temp.job%TYPE;


dml_errorsEXCEPTION;

PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN

-- Populate table:

INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');

INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');

INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');

COMMIT;

-- Append 9-character string to each job:

FORALL j IN depts.FIRST..depts.LASTSAVE EXCEPTIONS

UPDATE emp_temp SET job = job || ' (Senior)'

WHERE deptno = depts(j);

EXCEPTION

WHEN dml_errors THEN

FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

DBMS_OUTPUT.PUT_LINE (error_message);

bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;

DBMS_OUTPUT.PUT_LINE('Bad statement #: ' || bad_stmt_no);

bad_deptno := depts(bad_stmt_no);

DBMS_OUTPUT.PUT_LINE('Bad department #: ' || bad_deptno);

SELECT job INTO bad_job FROM emp_temp WHERE deptno = bad_deptno;

DBMS_OUTPUT.PUT_LINE('Bad job: ' || bad_job);

END LOOP;

COMMIT;-- Commit results of successful updates

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unrecognized error.');

RAISE;

END;

/

Result:

Procedure created.

Invoke procedure:

BEGIN

p;

END;

/

Result:

ORA-12899: value too large for column(actual: , maximum: )

Bad statement #: 2

Bad department #: 20

Bad job: Bookkeeper

PL/SQL procedure successfully completed.

Query:

SELECT * FROM emp_temp;

Result:

DEPTNO JOB

---------- ------------------

10 Clerk (Senior)

20 Bookkeeper

30 Analyst (Senior)

3 rows selected.

2.6 Sparse Collections and SQL%BULK_EXCEPTIONS

If theFORALL statement bounds clause references asparse collection, then to find the collection element that caused a DML statement to fail,you must step through the elements one by one until you find the element whose index is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX.Then,if the FORALL statement uses the VALUES OF clauseto reference a collection of pointers into another collection, you must find the element of the other collection whose index is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX.

2.7 Getting Number of Rows Affected by FORALL Statement

After aFORALL statement completes, you can get the number of rows that each DML statement affected from the implicit cursor attributeSQL%BULK_ROWCOUNT. (To get the total number of rows affected by the FORALL statement, use the implicit cursor attribute SQL%ROWCOUNT, described in"SQL%ROWCOUNT Attribute: How Many Rows Were Affected?".)

SQL%BULK_ROWCOUNT is like an associative array whose ith element is the number of rows affected by the ith DML statement in the most recently completed FORALL statement.

Example 12-14uses SQL%BULK_ROWCOUNT to show how many rows each DELETE statement in the FORALL statement deleted and SQL%ROWCOUNT to show the total number of rows deleted.

Example 12-14 Showing Number of Rows Affected by Each DELETE in FORALL

DROP TABLE emp_temp;

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList := NumList(30, 50, 60);

BEGIN

FORALL j IN depts.FIRST..depts.LAST

DELETE FROM emp_temp WHERE department_id = depts(j);

FOR i IN depts.FIRST..depts.LAST LOOP

DBMS_OUTPUT.PUT_LINE (

'Statement #' || i || ' deleted ' ||

SQL%BULK_ROWCOUNT(i) || ' rows.'

);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' ||SQL%ROWCOUNT);

END;

/

Result:

Statement #1 deleted 6 rows.

Statement #2 deleted 45 rows.

Statement #3 deleted 5 rows.

Total rows deleted: 56

Example 12-15uses SQL%BULK_ROWCOUNT to show how many rows each INSERT SELECT construct in the FORALL statement inserted and SQL%ROWCOUNT to show the total number of rows inserted.

Example 12-15 Showing Number of Rows Affected by Each INSERT SELECT in FORALL

DROP TABLE emp_by_dept;

CREATE TABLE emp_by_dept AS

SELECT employee_id, department_id

FROM employees

WHERE 1 = 0;

DECLARE

TYPE dept_tab IS TABLE OF departments.department_id%TYPE;

deptnumsdept_tab;

BEGIN

SELECT department_id BULK COLLECT INTO deptnums FROM departments;


FORALL i IN 1..deptnums.COUNT

INSERT INTO emp_by_dept (employee_id, department_id)

SELECT employee_id, department_id

FROM employees

WHERE department_id = deptnums(i)

ORDER BY department_id, employee_id;


FOR i IN 1..deptnums.COUNT LOOP

-- Count how many rows were inserted for each department; that is,

-- how many employees are in each department.

DBMS_OUTPUT.PUT_LINE (

'Dept '||deptnums(i)||': inserted '||

SQL%BULK_ROWCOUNT(i)||' records'

);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT);

END;

/

Result:

Dept 10: inserted 1 records

...

Dept 280: inserted 0 records

Total records inserted: 106

2.8 BULK COLLECT Clause

TheBULK COLLECT clause, a feature of bulk SQL, returns results from SQL to PL/SQL in batches rather than one at a time.The BULK COLLECT clause can appear in:

(1)SELECT INTO statement

(2)FETCH statement

(3)RETURNING INTO clause of:

(A)DELETE statement

(B)INSERT statement

(C)UPDATE statement

(D)EXECUTE IMMEDIATE statement

With the BULK COLLECT clause,each of the preceding statements retrieves an entire result set and stores it in one or morecollection variables in a single operation(which is more efficient than using a loop statement to retrieve one result row at a time).

Note:

PL/SQL processes the BULK COLLECT clause similar to the way it processes a FETCH statement inside a LOOP statement.PL/SQL does not raise an exception when a statement with aBULK COLLECT clause returns no rows. You must check the target collections for emptiness (if they are associative arrays) or nullness (if they are varrays or nested tables), as inExample 12-22.

2.9SELECT INTO Statement with BULK COLLECT Clause

TheSELECT INTO statement with theBULK COLLECT clause (also called the SELECT BULK COLLECT INTO statement) selects an entire result set into one or more collection variables. For more information, see"SELECT INTO Statement".

Caution:

The SELECT BULK COLLECT INTO statement is vulnerable to aliasing, which can cause unexpected results. For details, see"SELECT BULK COLLECT INTO Statements and Aliasing".

Example 12-16uses a SELECT BULK COLLECT INTO statement to select two database columns into two collections (nested tables).

Example 12-16 Bulk-Selecting Two Database Columns into Two Nested Tables

DECLARE

TYPE NumTab IS TABLE OF employees.employee_id%TYPE;

TYPE NameTab IS TABLE OF employees.last_name%TYPE;

enums NumTab;

names NameTab;

PROCEDURE print_first_n (n POSITIVE) IS

BEGIN

IF enums.COUNT = 0 THEN

DBMS_OUTPUT.PUT_LINE ('Collections are empty.');

ELSE

DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');

FOR i IN 1 .. n LOOP

DBMS_OUTPUT.PUT_LINE (

'Employee #' || enums(i) || ': ' || names(i));

END LOOP;

END IF;

END;


BEGIN

SELECT employee_id, last_name

BULK COLLECT INTO enums, names

FROM employees

ORDER BY employee_id;

print_first_n(3);

print_first_n(6);

END;

/

Example 12-17uses a SELECT BULK COLLECT INTO statement to select a result set into a nested table of records.

Example 12-17 Bulk-Selecting into Nested Table of Records

DECLARE

CURSOR c1 IS

SELECT first_name, last_name, hire_date

FROM employees;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

stock_managersNameSet;-- nested table of records

BEGIN

-- Assign values to nested table of records:


SELECT first_name, last_name, hire_date

BULK COLLECT INTO stock_managers

FROM employees

WHERE job_id = 'ST_MAN'

ORDER BY hire_date;


-- Print nested table of records:

FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP

DBMS_OUTPUT.PUT_LINE (

stock_managers(i).hire_date || ' ' ||

stock_managers(i).last_name|| ', ' ||

stock_managers(i).first_name

);

END LOOP;END;

/

2.10 SELECT BULK COLLECT INTO Statements and Aliasing

In a statement of the form

SELECT column BULK COLLECT INTO collection FROM table ...

column and collection are analogous to IN NOCOPY and OUT NOCOPY subprogram parameters, respectively, and PL/SQL passes them by reference. As with subprogram parameters that are passed by reference,aliasing can cause unexpected results.

See Also:

"Subprogram Parameter Aliasing with Parameters Passed by Reference"

InExample 12-18, the intention is to select specific values from a collection, numbers1, and then store them in the same collection. The unexpected result is that all elements of numbers1 are deleted. For workarounds, seeExample 12-19andExample 12-20.

Example 12-18 SELECT BULK COLLECT INTO Statement with Unexpected Results

CREATE OR REPLACE TYPE numbers_type IS

TABLE OF INTEGER

/

CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS

numbers1numbers_type := numbers_type(1,2,3,4,5);

BEGIN

DBMS_OUTPUT.PUT_LINE('Before SELECT statement');

DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());

FOR j IN 1..numbers1.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));

END LOOP;

--Self-selecting BULK COLLECT INTO clause:


SELECT a.COLUMN_VALUE

BULK COLLECT INTO numbers1

FROM TABLE(numbers1) a

WHERE a.COLUMN_VALUE > p.i

ORDER BY a.COLUMN_VALUE;


DBMS_OUTPUT.PUT_LINE('After SELECT statement');

DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());

END p;

/

Invoke p:

BEGIN

p(2);

END;

/

Result:

Before SELECT statement

numbers1.COUNT() = 5

numbers1(1) = 1

numbers1(2) = 2

numbers1(3) = 3

numbers1(4) = 4

numbers1(5) = 5

After SELECT statement

numbers1.COUNT() = 0

Invoke p:

BEGIN

p(10);

END;

/

Result:

Before SELECT statement

numbers1.COUNT() = 5

numbers1(1) = 1

numbers1(2) = 2

numbers1(3) = 3

numbers1(4) = 4

numbers1(5) = 5

After SELECT statement

numbers1.COUNT() = 0


Example 12-19uses a cursor to achieve the result intended byExample 12-18.

Example 12-19 Cursor Workaround forExample 12-18

CREATE OR REPLACE TYPE numbers_type IS

TABLE OF INTEGER

/

CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS

numbers1numbers_type := numbers_type(1,2,3,4,5);


CURSOR c IS

SELECT a.COLUMN_VALUE

FROM TABLE(numbers1) a

WHERE a.COLUMN_VALUE > p.i

ORDER BY a.COLUMN_VALUE;

BEGIN

DBMS_OUTPUT.PUT_LINE('Before FETCH statement');

DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());

FOR j IN 1..numbers1.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));

END LOOP;


OPEN c;

FETCH c BULK COLLECT INTO numbers1;

CLOSE c;


DBMS_OUTPUT.PUT_LINE('After FETCH statement');

DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());

IF numbers1.COUNT() > 0 THEN

FOR j IN 1..numbers1.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));

END LOOP;

END IF;

END p;

/

Invoke p:

BEGIN

p(2);

END;

/

Result:

Before FETCH statement

numbers1.COUNT() = 5

numbers1(1) = 1

numbers1(2) = 2

numbers1(3) = 3

numbers1(4) = 4

numbers1(5) = 5

After FETCH statement

numbers1.COUNT() = 3

numbers1(1) = 3

numbers1(2) = 4

numbers1(3) = 5

Example 12-20selects specific values from a collection, numbers1, and then stores them in a different collection, numbers2.Example 12-20runs faster thanExample 12-19.

Example 12-20 Second Collection Workaround forExample 12-18

CREATE OR REPLACE TYPE numbers_type IS

TABLE OF INTEGER

/

CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS

numbers1numbers_type := numbers_type(1,2,3,4,5);

numbers2numbers_type := numbers_type(0,0,0,0,0);


BEGIN

DBMS_OUTPUT.PUT_LINE('Before SELECT statement');

DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());

FOR j IN 1..numbers1.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));

END LOOP;

DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());

FOR j IN 1..numbers2.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));

END LOOP;

SELECT a.COLUMN_VALUE

BULK COLLECT INTO numbers2-- numbers2 appears here

FROM TABLE(numbers1) a-- numbers1 appears here

WHERE a.COLUMN_VALUE > p.i

ORDER BY a.COLUMN_VALUE;

DBMS_OUTPUT.PUT_LINE('After SELECT statement');

DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());

IF numbers1.COUNT() > 0 THEN

FOR j IN 1..numbers1.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());

IF numbers2.COUNT() > 0 THEN

FOR j IN 1..numbers2.COUNT() LOOP

DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));

END LOOP;

END IF;

END p;

/

2.11 Row Limits for SELECT BULK COLLECT INTO Statements

A SELECT BULK COLLECT INTO statement thatreturns a large number of rows produces a large collection. To limit the number of rows and the collection size, use either theROWNUM pseudocolumn(described inOracle Database SQL Language Reference) orSAMPLE clause (described inOracle Database SQL Language Reference).

InExample 12-21, the first SELECT BULK COLLECT INTO statement uses ROWNUM to limit the number of rows to 50, and the second SELECT BULK COLLECT INTO statement uses SAMPLE to limit the number of rows to approximately 10% of the total.

Example 12-21 Limiting Bulk Selection with ROWNUM and SAMPLE

DECLARE

TYPE SalList IS TABLE OF employees.salary%TYPE;

sals SalList;

BEGIN

SELECT salary BULK COLLECT INTO sals

FROM employees

WHERE ROWNUM <= 50;

SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10);

END;

/

2.12 Guidelines for Looping Through Collections

When a result set is stored in a collection, it is easy to loop through the rows and refer to different columns.This technique can be very fast, but also very memory-intensive.If you use it often:

(1)To loop once through the result set, use a cursor FOR LOOP (see"Query Result Set Processing With Cursor FOR LOOP Statements"). This technique avoids the memory overhead of storing a copy of the result set.

(2)Instead of looping through the result set to search for certain values or filter the results into a smaller set, do the searching or filtering in the query of the SELECT INTO statement.

For example, in simple queries, use WHERE clauses; in queries that compare multiple result sets, use set operators such as INTERSECT and MINUS. For information about set operators, seeOracle Database SQL Language Reference.

(3)Instead of looping through the result set and running another query for each result row, use a subquery in the query of the SELECT INTO statement (see"Query Result Set Processing with Subqueries").

(4)Instead of looping through the result set and running another DML statement for each result row, use the FORALL statement (see"FORALL Statement").

2.13 FETCH Statement with BULK COLLECT Clause

TheFETCH statement with theBULK COLLECT clause (also called theFETCH BULK COLLECT statement) fetches an entire result set into one or more collection variables. For more information, see"FETCH Statement".

Example 12-22uses a FETCH BULK COLLECT statement to fetch an entire result set into two collections (nested tables).

Example 12-22 Bulk-Fetching into Two Nested Tables

DECLARE

TYPE NameList IS TABLE OF employees.last_name%TYPE;

TYPE SalList IS TABLE OF employees.salary%TYPE;

CURSOR c1 IS

SELECT last_name, salary FROM employeesWHERE salary > 10000

ORDER BY last_name;

namesNameList;

salsSalList;

TYPE RecList IS TABLE OF c1%ROWTYPE;

recs RecList;

v_limit PLS_INTEGER := 10;

PROCEDURE print_results IS

BEGIN

-- Check if collections are empty:

IF names IS NULL OR names.COUNT = 0 THEN

DBMS_OUTPUT.PUT_LINE('No results!');

ELSE

DBMS_OUTPUT.PUT_LINE('Result: ');

FOR i IN names.FIRST .. names.LAST

LOOP

DBMS_OUTPUT.PUT_LINE('Employee ' || names(i) || ': $' || sals(i));

END LOOP;

END IF;

END;

BEGIN

DBMS_OUTPUT.PUT_LINE ('--- Processing all results simultaneously ---');

OPEN c1;

FETCH c1 BULK COLLECT INTO names, sals;

CLOSE c1;

print_results();

DBMS_OUTPUT.PUT_LINE ('--- Processing ' || v_limit || ' rows at a time ---');

OPEN c1;

LOOP

FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;

EXIT WHEN names.COUNT = 0;

print_results();

END LOOP;

CLOSE c1;

DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---');

OPEN c1;

FETCH c1 BULK COLLECT INTO recs;

FOR i IN recs.FIRST .. recs.LAST

LOOP

-- Now all columns from result set come from one record

DBMS_OUTPUT.PUT_LINE (

'Employee ' || recs(i).last_name || ': $' || recs(i).salary

);

END LOOP;

END;

/

Example 12-23uses a FETCH BULK COLLECT statement to fetch a result set into a collection (nested table) of records.

Example 12-23 Bulk-Fetching into Nested Table of Records

DECLARE

CURSOR c1 IS

SELECT first_name, last_name, hire_date FROM employees;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

stock_managersNameSet;-- nested table of records

TYPE cursor_var_type is REF CURSOR;

cv cursor_var_type;

BEGIN

-- Assign values to nested table of records:

OPEN cv FOR

SELECT first_name, last_name, hire_date FROM employees

WHERE job_id = 'ST_MAN'ORDER BY hire_date;

FETCH cv BULK COLLECT INTO stock_managers;

CLOSE cv;

-- Print nested table of records:

FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP

DBMS_OUTPUT.PUT_LINE (

stock_managers(i).hire_date || ' ' ||

stock_managers(i).last_name|| ', ' ||

stock_managers(i).first_name

);

END LOOP;END;

/

2.14 Row Limits for FETCH BULK COLLECT Statements

A FETCH BULK COLLECT statement that returns a large number of rows produces a large collection. To limit the number of rows and the collection size, use theLIMIT clause.

InExample 12-24, with each iteration of the LOOP statement, the FETCH statement fetches ten rows (or fewer) into associative array empids (overwriting the previous values). Note the exit condition for the LOOP statement.

Example 12-24 Limiting Bulk FETCH with LIMIT

DECLARE

TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

CURSOR c1 IS SELECT employee_id

FROM employees WHERE department_id = 80 ORDER BY employee_id;

empidsnumtab;

BEGIN

OPEN c1;

LOOP-- Fetch 10 rows or fewer in each iteration

FETCH c1 BULK COLLECT INTOempids LIMIT 10;

EXIT WHEN empids.COUNT = 0;-- Not: EXIT WHEN c1%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');

FOR i IN 1..empids.COUNT LOOP

DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids(i));

END LOOP;

END LOOP;

CLOSE c1;

END;

/

2.15RETURNING INTO Clause with BULK COLLECT Clause

TheRETURNING INTO clause with theBULK COLLECT clause (also called theRETURNING BULK COLLECT INTO clause) can appear in an INSERT, UPDATE, DELETE, or EXECUTE IMMEDIATE statement. With the RETURNING BULK COLLECT INTO clause, the statement stores its result set in one or more collections. For more information, see"RETURNING INTO Clause".

Example 12-25uses a DELETE statement with the RETURNING BULK COLLECT INTO clause to delete rows from a table and return them in two collections (nested tables).

Example 12-25 Returning Deleted Rows in Two Nested Tables

DROP TABLE emp_temp;

CREATE TABLE emp_temp AS

SELECT * FROM employees

ORDER BY employee_id;

DECLARE

TYPE NumList IS TABLE OF employees.employee_id%TYPE;

enumsNumList;

TYPE NameList IS TABLE OF employees.last_name%TYPE;

namesNameList;

BEGIN

DELETE FROM emp_temp WHERE department_id = 30

RETURNING employee_id, last_name BULK COLLECT INTO enums, names;


DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

FOR i IN enums.FIRST .. enums.LAST

LOOP

DBMS_OUTPUT.PUT_LINE ('Employee #' || enums(i) || ': ' || names(i));

END LOOP;

END;

/

2.16Using FORALL Statement and BULK COLLECT Clause Together

In aFORALL statement, the DML statement can have aRETURNING BULK COLLECT INTO clause. For each iteration of the FORALL statement, the DML statement stores the specified values in the specified collections—without overwriting the previous values,as the same DML statement would do in a FOR LOOP statement.

InExample 12-26, the FORALL statement runs a DELETE statement that has a RETURNING BULK COLLECT INTO clause. For each iteration of the FORALL statement, the DELETE statement stores the employee_id and department_id values of the deleted row in the collections e_ids and d_ids, respectively.

Example 12-26 DELETE with RETURN BULK COLLECT INTO in FORALL Statement

DROP TABLE emp_temp;

CREATE TABLE emp_temp AS

SELECT * FROM employees

ORDER BY employee_id, department_id;

DECLARE

TYPE NumList IS TABLE OF NUMBER;

deptsNumList := NumList(10,20,30);

TYPE enum_t IS TABLE OF employees.employee_id%TYPE;

e_idsenum_t;

TYPE dept_t IS TABLE OF employees.department_id%TYPE;

d_idsdept_t;


BEGIN

FORALLj IN depts.FIRST..depts.LAST

DELETE FROM emp_temp WHERE department_id = depts(j)

RETURNINGemployee_id, department_id

BULK COLLECT INTOe_ids, d_ids;


DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

FOR i IN e_ids.FIRST .. e_ids.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)

);

END LOOP;

END;

/

Example 12-27is likeExample 12-26except that it uses a FOR LOOP statement instead of a FORALL statement.

Example 12-27 DELETE with RETURN BULK COLLECT INTO in FOR LOOP Statement

DECLARE

TYPE NumList IS TABLE OF NUMBER;

deptsNumList := NumList(10,20,30);

TYPE enum_t IS TABLE OF employees.employee_id%TYPE;

e_idsenum_t;

TYPE dept_t IS TABLE OF employees.department_id%TYPE;

d_idsdept_t;


BEGIN

FORj IN depts.FIRST..depts.LASTLOOP

DELETE FROM emp_temp WHERE department_id = depts(j)

RETURNINGemployee_id, department_id

BULK COLLECT INTOe_ids, d_ids;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

FOR i IN e_ids.FIRST .. e_ids.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)

);

END LOOP;

END;

/

2.17 Client Bulk-Binding of Host Arrays

Client programs (such as OCI and Pro*C programs) can use PL/SQL anonymous blocks tobulk-bind input and output host arrays. This is the most efficient way to pass collections to and from the database server.

In the client program, declare and assign values to thehost variables to be referenced in the anonymous block. In the anonymous block, prefix each host variable name with a colon (:) to distinguish it from a PL/SQL collection variable name. When the client program runs, the database server runs the PL/SQL anonymous block.

InExample 12-28, the anonymous block uses a FORALL statement to bulk-bind a host input array. In the FORALL statement, the DELETE statement refers to four host variables: scalars lower, upper, and emp_id and array depts.

Example 12-28 Anonymous Block Bulk-Binds Input Host Array

BEGIN

FORALLi IN :lower..:upper

DELETE FROM employees WHERE department_id = :depts(i);

END;

/

三.小结

在第一节讲了Bulk的原理,第二节举了Bulk的例子。在这里做一个简单的回顾,到底什么是bulk.

One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine.When rows are retrieved using Oracle bulk collect, they are retrieved with only 2 context switches.The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.

Starting in Oracle10g, an Oracle bulk collect may be performed by the the PL/SQL engine for you.The PL/SQL engine may automatically use Oracle bulk collect to collect 100 rows at a time because of a cursor loop. This use of Oracle bulk collect allows your code to process rows without having to setup and execute the Oracle bulk collect operation. The result of this use of Oracle bulk collect is that bulk collecting 75 rows may not provide you with much of a benefit,but using Oracle bulk collect to collect large numbers of rows (many hundreds) will provid increased performance.

Oracle有2个引擎来执行PL/SQL blocks和subprograms。那么在执行的时候,PL/SQL引擎把DML语句发送给SQL引擎,然后由SQL引擎执行,执行完毕后,SQL引擎把结果集在发送给PL/SQL引擎。

这个是一条语句的执行过程,如果我们有一个大事务,比如insert 100万的数据,那么这个时候,如果按照原始的方法,每次处理一条,这样在2个引擎之间就会发生大量的context switches,这样就会影响SQL的效率。

而bulk就是从减少引擎之间context switches的方式来提高sql的效率。把对SQL进行打包处理。有2个bulk:

(1)FORALL.将数据打包,一次性从PL/SQL引擎发送给SQL引擎。

如:

BEGIN

FORALL j IN 1..10

DELETE FROM employees_temp WHERE department_id = depts(j);

END;

如果这里用for ..loop循环,那么会发送10次,而用Forall,一次行全部发送过去。

(2)bulk collect:将处理之后的结果集放到bulk collect里,然后一次性把bulk collect从SQL引擎发送给PL/SQL引擎。这个bulk collect需要我们先定义好才能使用。

通过以上说明可以看出,如果使用bulk,那么只有2次context switches,当要处理的数据量越大,使用bulk和不使用bulk性能区别就越明显。

-------------------------------------------------------------------------------------------------------

分享到:
评论

相关推荐

    ORACLE存储过程性能优化技巧

    以下是一些关于"ORACLE存储过程性能优化技巧"的关键知识点: 1. **绑定变量**:使用绑定变量可以减少解析次数,提高执行效率。当存储过程中的参数作为SQL查询的一部分时,应避免直接拼接字符串,而是使用?作为占位...

    oracle bulk collect

    `BULK COLLECT`是Oracle提供的一种用于优化数据检索的方法,它允许一次性从数据库表或视图中检索多行记录,并将这些记录存储到PL/SQL集合中。这种机制可以显著减少数据库访问次数,从而提高应用程序性能。 ### 使用...

    oracle批量处理(bulk collect)

    #### 五、性能优化技巧 - **减少循环处理的开销**:通过使用`BULK COLLECT`,可以减少循环处理数据时的开销,因为不再需要逐行处理数据。 - **使用LIMIT**:在某些情况下,可能需要限制`BULK COLLECT`获取的结果集...

    Oracle性能优化

    Oracle性能优化是数据库管理员和开发人员关注的重要领域,特别是在处理大量数据和复杂查询的企业级应用中。SQL索引管理分析是实现性能优化的关键步骤。本文将深入探讨Oracle性能优化的相关知识点,以及SQL编写的一般...

    oracle性能优化教程下载

    在学习《Oracle性能优化教程》的过程中,你将掌握如何结合理论知识与实践经验,解决实际工作中遇到的性能问题。通过深入理解Oracle的内部工作机制,结合监控和诊断工具,以及持续的学习和实践,你将成为一名出色的...

    Oracle_SQL性能优化技巧大总结

    Oracle SQL性能优化是数据库管理员和开发人员关注的重要领域,它涉及到如何提高SQL查询的执行效率,减少资源消耗,提升整体系统性能。以下是一些关键的优化技巧: 1. **选择有效的表名顺序**:在基于规则的优化器中...

    Oracle sql 性能优化调整

    Oracle SQL性能优化调整是数据库管理员和开发人员提升系统效率的关键技术。以下是对这些知识点的详细说明: 1. **选择适合的Oracle优化器**:Oracle数据库提供了两种主要的优化器——基于成本的优化器(CBO)和基于...

    Oracle查询优化改写-技巧与案例

    Oracle查询优化是数据库管理中的关键技能,尤其是在处理大型数据集时,有效的查询优化能显著提升系统性能,降低资源消耗。...通过学习,读者能够提升其在Oracle数据库性能优化方面的专业能力,实现更高效的数据处理。

    oracle sql性能优化.doc

    Oracle SQL性能优化是一个复杂且关键的领域,涉及到多个层面的技术策略。以下是一些核心知识点的详细解释: 1. **选择适合的优化器** ORACLE提供了三种优化器:基于规则(RULE)、基于成本(COST)和选择性...

    Oracle Bulk Binds技术分析.pdf

    Oracle Bulk Binds技术是一种优化数据库操作性能的策略,尤其适用于处理大量数据的场景。批量绑定技术的核心在于减少PL/SQL和SQL引擎之间的上下文切换次数,从而提高效率。在传统的SQL语句执行过程中,每次SQL的执行...

    oracle性能优化

    Oracle性能优化是数据库管理员和开发人员关注的重要领域,尤其是在处理大量数据和复杂查询的企业级应用中。Oracle数据库作为世界上最广泛使用的数据库系统之一,其性能优化技巧对于提升系统效率、减少资源消耗以及...

    Oracle SQL性能优化最佳实践.docx

    Oracle SQL性能优化是数据库管理的关键环节,它直接影响到应用程序的响应速度和系统资源的利用率。以下是一些重要的最佳实践: 1. **SQL语句执行步骤**:SQL执行过程包括解析、优化、执行和返回结果。解析阶段将SQL...

    Oracle 查询优化,个人练习

    Oracle查询优化是数据库管理中的关键技能,尤其是在处理大型数据集时,有效的查询优化能显著提升系统性能,降低资源消耗。本实践主要关注如何利用Oracle数据库的特性与工具来改进SQL查询的执行效率。 首先,了解...

    基于Oracle的SQL优化典型案例分

    然而,随着数据量的增长,SQL查询的性能优化成为了一个关键问题。本篇将深入探讨“基于Oracle的SQL优化典型案例分析”,旨在帮助数据库管理员和开发人员提升SQL查询效率,降低系统负载,从而提高整体系统性能。 1. ...

    Oracle语句优化规则汇总.pdf

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统之一,其SQL语句优化对于系统的性能有着至关重要的影响。这篇文档“Oracle语句优化规则汇总.pdf”很可能提供了关于如何提高Oracle数据库查询效率、减少资源...

    Oracle查询优化改写技巧与案例

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其性能优化是DBA和开发人员日常工作中不可或缺的部分。查询优化是提高数据库性能的关键环节,通过合理的查询改写,可以显著提升数据检索速度,降低系统资源...

    oracle游标优化

    ### Oracle游标优化 ...通过上述方法,可以在一定程度上优化Oracle游标的性能,从而提高应用程序的整体响应时间和资源利用率。需要注意的是,每种优化策略都应该根据具体的业务需求和技术环境来选择和实施。

    Oracle表字段更新存储过程效率优化思路

    本文将深入探讨Oracle表字段更新的存储过程效率优化思路,以及优化后可能带来的性能提升。 首先,理解存储过程的执行原理是优化的基础。存储过程在创建时会被编译成优化器可以理解的执行计划。这个计划会保存在...

    oracle性能

    Oracle性能优化是数据库管理员和开发人员关注的重要领域,特别是在处理大量数据时,高效的Oracle性能能够显著提升系统响应速度,减少资源消耗。以下是一些关于Oracle性能优化的关键知识点: 1. **Oracle批量绑定...

    Oracle BULK COLLECT批量取数据解决方法

    在处理大数据时,理解并合理利用BULK COLLECT和级联操作等技术是优化Oracle数据库性能的关键。通过这种方式,开发者可以编写出更高效、更稳定的PL/SQL代码,从而提升整个系统的性能和响应速度。

Global site tag (gtag.js) - Google Analytics