Bulk binds advantages, PL-SQL Programming

Assignment Help:

Bulk Binds advantages

In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedural statements but sends SQL statements to the SQL engine that executes the SQL statements and, in many cases, returns the data to the PL/SQL engine.

1509_bulk bind advantage.png

Figure: Context Switching

Each of the context switches between the PL/SQL and SQL engines adds to the overhead. Therefore, if many switches are needed, the performance suffers. That can happen when the SQL statements execute inside a loop using the collection (nested table, index-by table, varray, or the host array) elements as the bind variables. For e.g., the DELETE statement below is sent to the SQL engine with each and every iteration of the FOR loop:

DECLARE

TYPE NumList IS VARRAY(20) OF NUMBER;

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

BEGIN

...

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

...

DELETE FROM emp WHERE deptno = depts(i);

END LOOP;

END;

In such cases, when the SQL statement affects five or more database rows, the use of bulk binds can improve the performance significantly.


Related Discussions:- Bulk binds advantages

Prepare a slq project, Project Description: I want to write some SQL st...

Project Description: I want to write some SQL statements. The things I need are between pages 5-7. The only problem is that i want it till tomorrow. Skills required is SQL

Best practices/Data Warhousing, What are 3 good practices of modeling and/o...

What are 3 good practices of modeling and/or implementing data warehouses?

Loop statements, LOOP Statements The LOOP statements execute a series o...

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Interesting properties of cross join - sql, Interesting properties of CROSS...

Interesting properties of CROSS JOIN - SQL Compare these with the "interesting properties of JOIN", CROSS JOIN is associative but not commutative. Unlike JOIN and NATURAL JOI

Differentiate between snowflake schema and star schema, Problem: (a) De...

Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

Conditionals - sql, Conditionals - SQL At first sight SQL does not app...

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Max and min operator in sql, MAX and MIN operator in SQL Example: ...

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

Providing results of queries, Providing Results of Queries Expressing ...

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Parameter and keyword description - procedures, Parameter and Keyword Descr...

Parameter and Keyword Description: procedure_name The user-defined procedure is declared by this construct. parameter_name: This identifies the formal parameter t

Explicitly specifying the join condition - sql, Explicitly specifying the j...

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd