Using bulk collect clause- bulk bind performance improvement, PL-SQL Programming

Using the BULK COLLECT Clause

The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the FETCH INTO, SELECT INTO, and RETURNING INTO clauses. The syntax for the above is shown below:

... BULK COLLECT INTO collection_name[, collection_name] ...

The SQL engine bulk-binds all the collections referenced in the INTO list. The parallel columns should store the scalar (not composite) values. In the illustration, the SQL engine loads the whole empno and ename database columns into the nested tables before returning the tables to the PL/SQL engine:

DECLARE

TYPE NumTab IS TABLE OF emp.empno%TYPE;

TYPE NameTab IS TABLE OF emp.ename%TYPE;

enums NumTab; -- no need to initialize

names NameTab;

BEGIN

SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;

...

END;

The SQL engine initializes and then expands the collections for you. (Though, it cannot expands the varrays beyond their maximum size.) Then, starting at index 1, it inserts the elements successively and overwrites any pre-existent elements.

The SQL engine bulk-binds the whole database columns. Therefore, if a table has 50,000 rows, then the engine loads 50,000 column values into the target collection. Though, you can use the pseudocolumn ROWNUM to limit the number of rows processed. In the illustration below, you limit the number of rows to 100:

DECLARE

TYPE NumTab IS TABLE OF emp.empno%TYPE;

sals NumTab;

BEGIN

SELECT sal BULK COLLECT INTO sals FROM emp WHERE ROWNUM <= 100;

...

END;

Posted Date: 10/4/2012 3:30:09 AM | Location : United States







Related Discussions:- Using bulk collect clause- bulk bind performance improvement, Assignment Help, Ask Question on Using bulk collect clause- bulk bind performance improvement, Get Answer, Expert's Help, Using bulk collect clause- bulk bind performance improvement Discussions

Write discussion on Using bulk collect clause- bulk bind performance improvement
Your posts are moderated
Related Questions
Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

Structure of an Object Type: Similar to package, an object type has 2 parts: the specification and the body. The specification is the interface to your applications; it declar

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

Avoid the NOT NULL Constraint In the PL/SQL, using the NOT NULL constraint incur a performance cost. Consider the illustration as shown below: PROCEDURE calc_m IS m NUMB

Built-In Functions The PL/SQL provides a lot of powerful functions to help you to manipulate the data. These built-in functions fall into the categories as shown below: error r

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )