 |
|
Sparse Collections
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
All the previous examples have dealt with
dense collections, such that all values from 1 to N have an associated
value. This may not be the case in many real-world situations or
in naturally sparse collections, such as associative arrays, since
records may be deleted.
Support for bulk binds with sparse collections
was introduced in Oracle 10g by adding the INDICES OF and VALUES OF
clauses in the FORALL statement.
The INDICES OF clause allows a bulk operation
on a sparse collection by removing the reference to specific elements.
In addition, upper and lower bounds can be specified using the BETWEEN
clause. Allowable syntaxes include:
FORALL index
IN INDICES OF collection
FORALL index IN INDICES OF collection BETWEEN start AND end
The usage of the INDICES OF clause is shown in
the forall_indices_of.sql script below.
forall_indices_of.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
l_tab
t_forall_test_tab := t_forall_test_tab();
BEGIN
FOR i IN 1 .. 1000 LOOP
l_tab.extend;
l_tab(l_tab.last).id
:= i;
l_tab(l_tab.last).code
:= TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;
-- Make
collection sparse.
l_tab.delete(301);
l_tab.delete(601);
l_tab.delete(901);
EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;
DBMS_OUTPUT.put_line(‘Start FORALL’);
BEGIN
-- This will fail due to sparse collection.
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO forall_test VALUES l_tab(i);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;
DBMS_OUTPUT.put_line(‘Start FORALL INDICES OF’);
--
This works fine with sparse collections.
FORALL i IN INDICES OF l_tab
INSERT INTO forall_test VALUES l_tab(i);
END;
/
The forall_indices_of.sql script creates and
populates a collection, but then deletes some elements making it
sparse. It then performs a normal FORALL statement which fails
due to the sparse nature of the collection. Finally it uses the
INDICES OF clause to allow successful execution of the bulk operation.
An example of its output is shown below.
SQL> @forall_indices_of.sql
Start FORALL
ORA-22160: element at index [301] does not exist
Start FORALL INDICES OF
PL/SQL
procedure successfully completed.
In a FORALL statement, the VALUES OF clause
allows the values of one collection to be used as index pointers to
another collection, as shown in the forall_values_of.sql script.
forall_values_of.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
TYPE t_idx_tab IS TABLE OF BINARY_INTEGER;
l_tab
t_forall_test_tab := t_forall_test_tab();
l_idx_tab t_idx_tab
:= t_idx_tab();
BEGIN
FOR i IN 1 .. 1000 LOOP
l_tab.extend;
l_tab(l_tab.last).id
:= i;
l_tab(l_tab.last).code
:= TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
IF MOD(i, 100) = 0 THEN
l_idx_tab.extend;
l_idx_tab(l_idx_tab.last) := i;
END IF;
END LOOP;
EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;
--
This works fine with sparse collections.
FORALL i IN VALUES OF l_idx_tab
INSERT INTO forall_test VALUES l_tab(i);
END;
/
This example shows that the main collection is
populated with 1000 rows, but the index collection is only populated
with 10 rows (every 100th row). Since the index collection is
being used as a pointer to the main collection, insertion of only 10
rows is expected. The output below verifies this expectation.
SQL> @forall_values_of.sql
PL/SQL
procedure successfully completed.
SQL> SELECT
* FROM forall_test;
ID CODE DESCRIPTION
---------- ---------- -------------------
100 100
Description: 100
200 200
Description: 200
300 300
Description: 300
400 400
Description: 400
500 500
Description: 500
600 600
Description: 600
700 700
Description: 700
800 800
Description: 800
900 900
Description: 900
1000 1000
Description: 1000
10 rows
selected.
The next section looks at the use of host
arrays in bulk operations.
Host Arrays in Bulk Operations
Bulk operations can be used with host arrays
when programming in Pro*C or when using the Oracle Call Interface (OCI).
In these situations, the program defines the appropriate array and
binds it to a statement, like the one shown below, to pass to the
server.
BEGIN
FORALL i IN :lower_bound .. :upper_bound
DELETE FROM forall_test
WHERE id = :array(i)
END;
In this statement, the upper and lower bounds
are also bind variables. The preceding “:” indicates these are
host variables not PL/SQL variables.
The next section explains the use of the
BULK_ROWCOUNT pseudo column during bulk operations.
BULK_ROWCOUNT
It is sometimes necessary to check the number
of rows affected by an insert, update or delete statement. The
rowcount_test.sql script shows how this can be done using the
SQL%ROWCOUNT cursor attribute.
rowcount_test.sql
CREATE TABLE
rowcount_test AS
SELECT *
FROM all_users;
SET
SERVEROUTPUT ON
BEGIN
UPDATE rowcount_test
SET username = username;
DBMS_OUTPUT.put_line(‘Rows affected: ‘ || SQL%ROWCOUNT);
END;
/
DROP TABLE
rowcount_test;
The rowcount_test.sql script creates a test
table as a copy of the all_users view, updates all the rows in the
test table, displays the number of affected rows and drops the test
table. The output from this script is listed below.
SQL> @rowcount_test.sql
Table
created.
Rows
affected: 70
PL/SQL
procedure successfully completed.
Table
dropped.
The SQL%ROWCOUNT attribute works for a single
statement, but how are the same results for a bulk operation achieved?
The answer is by using the SQL%BULK_ROWCOUNT cursor attribute.
This is a composite cursor attribute
implemented using an associative array containing a matching subscript
for each associated FORALL statement subscripts. If the
collection driving the FORALL statement is sparse, the
SQL%BULK_ROWCOUNT collection will also be sparse. The
bulk_rowcount_test.sql script creates a test table as a copy of the
all_users view, performs a bulk operation, reports the number of
affected rows and drops the test table.
bulk_rowcount_test.sql
CREATE TABLE
bulk_rowcount_test AS
SELECT *
FROM all_users;
SET
SERVEROUTPUT ON
DECLARE
TYPE t_array_tab IS TABLE OF VARCHAR2(30);
l_array t_array_tab := t_array_tab('SCOTT', 'SYS',
'SYSTEM', 'DBSNMP', 'BANANA');
BEGIN
--
Perform bulk delete operation.
FORALL i IN l_array.first .. l_array.last
DELETE FROM bulk_rowcount_test
WHERE username = l_array(i);
--
Report affected rows.
FOR i
IN l_array.first .. l_array.last LOOP
DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ')
||
' Rows affected: ' || SQL%BULK_ROWCOUNT(i));
END LOOP;
END;
/
DROP TABLE
bulk_rowcount_test;
The resulting output shows that a single row
was affected by each delete except for the username “BANANA”, which is
not present in the test table.
SQL> @bulk_rowcount_test.sql
Table created.
Element:
SCOTT Rows
affected: 1
Element: SYS
Rows affected: 1
Element: SYSTEM Rows affected: 1
Element: DBSNMP
Rows affected: 1
Element: BANANA
Rows affected: 0
PL/SQL
procedure successfully completed.
Table
dropped.
Typically the INSERT statement is expected to
affect a single row, except for INSERT … SELECT statements that may
result in many inserted rows.
Other cursor attributes are also available for
bulk operations but their usage differs slightly:
-
SQL%FOUND: Refers to the last
execution of the FORALL statement.
-
SQL%NOTFOUND: Refers to the
last execution of the FORALL statement.
-
SQL%ROWCOUNT: Refers to the
total number of rows affected by the whole bulk operation. It
is the sum of the SQL%BULK_ROWCOUNT values.
During a bulk operation the SQL%FOUND and
SQL%NOTFOUND attributes are not very useful. The “per-execution”
values for these attributes can be implied by the contents of the
SQL%BULK_ROWCOUNT attribute, in which a zero value equates to %NOTFOUND
and a non-zero value equates to %FOUND.
In the next section, exception handling
associated with bulk operations is presented.
|
and include the URL for the page.
