Als PDF Downloaden!

Tipps & Tricks: Januar 2002
Bereich:
PL/SQL
Erstellung:
01/2002 HA
Versionsinfo:
8.1.7, 9.2, 10.2, 11.1
Letzte Überarbeitung:
06/2009 EF
Schnelle Verarbeitung großer Datenmengen in PL/SQL
Um beliebig viele Datensätze in einem Schritt in eine Collection (Link einfügen) einzulesen, braucht man nur die
Klausel BULK COLLECT INTO anstatt des üblichen INTO beim Einlesen eines einzelnen Datensatzes. Einen
Geschwindigkeitsvergleich zwischen Bulk Binds, FOR-Schleife und DBMS_SQL finden Sie hier.
Vor Version 9.2 durfte die Collection keinen Record als Datentyp haben, man mußte also genauso viele
Collections deklarieren, wie man Spalten auslesen wollte.
Beispiel (ab 9.2):
DECLARE
TYPE emp_tab_type IS TABLE OF emp%ROWTYPE;
emp_tab emp_tab_type;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
SELECT * BULK COLLECT INTO emp_tab FROM emp;
FOR i IN emp_tab.FIRST..emp_tab.LAST LOOP
-- Beispiel für Verarbeitung der Daten
DBMS_OUTPUT.PUT_LINE
(emp_tab(i).ename||', '||emp_tab(i).job||', '||emp_tab(i).sal);
END LOOP;
/* alternativ:
OPEN c1;
FETCH c1 BULK COLLECT INTO emp_tab;
-- Verarbeitung der Daten
CLOSE c1;
*/
END;
/
Wenn die Tabelle allerdings ein paar Millionen Zeilen hat, verbraucht der Bulk Collect natürlich jede Menge
Arbeitsspeicher. Deshalb empfiehlt es sich, im Zusammenhang mit einem Cursor die LIMIT-Klausel zu
verwenden, um die Anzahl der auf einmal in den Arbeitsspeicher geladenen Datensätze zu limitieren (hier auf
1000).
...
OPEN v_cur;
LOOP
FETCH v_cur BULK COLLECT INTO emp_tab LIMIT 1000;
EXIT WHEN emp_tab.COUNT = 0;
...
END LOOP;
CLOSE v_cur;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 2
...
Ab Version Oracle 10g führt der Compiler übrigens auch für Cursor-For-Schleifen hinter den Kulissen einen Bulk
Collect mit jeweils 100 Zeilen aus, so dass die Geschwindigkeitsunterschiede zwischen einem expliziten Bulk
Collect und einer Cursor-For-Loop je nach Größe der Tabelle längst nicht mehr so groß sind, wie noch in Version
9.2.
Dafür muss der Parameter PLSQL_OPTIMIZE_LEVEL auf 2 stehen (das ist allerdings der Default).
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 2