Database Reference
In-Depth Information
When to Use It
Whenever more than one row has to be inserted or modified, using the array interface makes sense. You just have to
take into consideration that more memory is used on the client to store the array. Usually, this isn't an issue unless an
exaggerated array size is used.
Pitfalls and Fallacies
In the execution statistics generated through SQL trace, there is no explicit information about the utilization of the
array processing. However, if you know the SQL statement, by looking at the ratio between the number of modified
rows and the number of executions, you should be able to identify whether array processing was used. For example,
in the following execution statistics, a plain
INSERT
statement, which was executed only once, inserted 2,342 rows.
Something like that is possible only when the array interface is used:
INSERT INTO T VALUES (:B1 , :B2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 78 522 2342
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 78 522 2342
On to Chapter 16
This chapter describes some advanced optimization techniques aimed at improving performance. Some of them
(materialized views, result caches, parallel processing, and direct-path inserts) should be used only if with “regular”
optimization techniques it isn't possible to achieve the required performance. In contrast, others (row prefetching
and array processing) should always be used if possible.
Although this chapter mainly describes optimization techniques that aren't commonly used, the next (and last)
chapter covers techniques that basically apply to every table stored in a database. In fact, when you perform the
translation from logical design to physical design, it's necessary to decide how data is physically stored for every table.
