Chapter 5. Bulking Up with PLSQL 8.1 | Oracle PL/SQL Programming: Guide to Oracle8i Features


Team-Fly

	Oracle PL/SQL Programming Guide to Oracle 8 i Features By Steven Feuerstein
	Table of Contents

Chapter 5. Bulking Up with PL/SQL 8.1

We all know that PL/SQL is tightly integrated with the underlying SQL engine in the Oracle database. PL/SQL is the database programming language of choice for Oracleeven if you now can at least theoretically use Java inside the database as well.

But this tight integration does not necessarily mean that there isn't any overhead associated with running SQL from a PL/SQL program. When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but it passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control (shown in Figure 5.1) between the PL/SQL and SQL engines is called a context switch . Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades. Oracle 8.1 now offers two enhancements to PL/SQL that allow you to bulk together multiple context switches into a single switch, thereby improving the performance of your applications.

Figure 5.1. Context switching between PL/SQL and SQL

These new features are as follows :

FORALL: A variation on the FOR loop that bundles together multiple DML statements based on data in a collection
BULK COLLECT: An enhancement to implicit and explicit query cursor syntax that allows the transfer of multiple rows of data in a single round-trip between the PL/SQL and SQL engines

Make Collections a Part of Your PL/SQL Diet

With each new release, PL/SQL gets more robust, faster, and easier to use. The "bulk bind" features discussed in this chapter, FORALL and BULK COLLECT, are clear reflections of this trend.

As you will soon see, you won't be able to take advantage of FORALL and BULK COLLECT unless you work with collections (variable arrays, nested tables, or index-by tables). Sadly, many PL/SQL developers have not yet become conversant in these relatively new structures (variable arrays and nested tables are new with Oracle8; index-by tables were first available in PL/SQL 2.3 as "PL/SQL tables"). If you are one of those developers, perhaps these bulk bind capabilities will offer the required incentive for you to dig in to your books and knowledge bases.

If you don't use collections and you don't use bulk bind operations, then the applications you write will run more slowly and be more complicated than youand your managerwould like.


Team-Fly

Top