Einleitung Realisierung

Betrifft
Associative Arrays in Oracle9i Release2
oder wie man das auch in älteren Versionen lösen konnte
Autor
Sven Vetter ([email protected])
Art der Info
Technische Background Info (Mai 2002)
Quelle
Aus unserer Schulungs- und Beratungstätigkeit
Einleitung
Sehr oft steht man in PL/SQL vor der Aufgabe, Daten zu validieren, welche z.B. aus
externen Quellen (ASCII-Files für DWH, Userinput, ...) geladen werden. Dafür werden
häufig so genannte LookUp-Tabellen eingesetzt.
Bei sehr vielen Zugriffen auf solche Tabellen empfiehlt es sich, diese ins Memory zu laden
und nicht bei jedem Check einen SELECT-Befehl abzusetzen, vor allem wenn die Tabellen
nicht allzu gross sind.
Dies hat zwei Vorteile:
- Die Daten werden einmal ins Memory gelesen und verbleiben dann dort. Dadurch
muss weniger I/O durchgeführt werden
- Bei jedem SELECT-Zugriff auf die Tabelle würde ein Switch von der PL/SQL- in die SQLEngine durchgeführt und bei vielen Zugriffen dauert das ziemlich lang
Realisierung
Nehmen wir als Beispiel an, wir haben eine Tabelle r_zipcode, in welcher die
Zuordnungen Postleitzahl zu Ort gespeichert ist:
CREATE TABLE r_zipcode
( zipcode number(5,0) PRIMARY KEY,
town
varchar2(30)
) TABLESPACE users;
In unserem Programm soll gecheckt werden, ob eine bestimmte Postleitzahl existiert und
der dazugehörige Ort ermittelt werden.
Dafür lesen wir die Daten der Tabelle als erstes ins Memory und kommen damit zum Titel
des Artikels "Associative Arrays". Was ist das denn das neues?
Eigentlich nichts, wir kennen dies schon seit langer Zeit als Index-By-Table (oder auch
PL/SQL-Table). Zur Erweiterung in Oracle9i Release2 kommen wir etwas später...
Um ein Associatives Array benutzen zu können, deklarieren wir einen TYPE. Hier basiert er
auf dem Variablentyp des Feldes town der Tabelle r_zipcode, er kann aber natürlich auch
auf beliebigen anderen Typen einschliesslich Records basieren. Weiterhin benötigen wir
noch eine Variable (vZipCodes), in welcher dann die gesamte Tabelle gespeichert wird:
...
TYPE
tyZipCode IS TABLE OF r_zipcode.town%TYPE
INDEX BY binary_integer;
vZipCodes tyZipCode;
...
Im nächsten Step, hier in eine Prozedur ausgelagert, werden die Daten über einen Cursor
(cData) mittels eines Loops in das Array geladen:
PROCEDURE Load_ZipCode IS
CURSOR cData IS
SELECT zipcode, town FROM r_zipcode;
BEGIN
FOR rData IN cData LOOP
vZipCodes(rData.zipcode):=rData.town;
END LOOP;
END Load_ZipCode;
Für den Test, ob eine bestimmte PLZ existiert, habe ich eine kleine Funktion geschrieben.
Diese gibt eine Fehlermeldung zurück, wenn die entsprechende PLZ nicht existiert,
ansonsten den dazugehörigen Ort:
FUNCTION Check_ZipCode
( pZipCode r_zipcode.zipcode%TYPE
) RETURN r_zipcode.town%TYPE IS
BEGIN
IF NOT vZipCodes.EXISTS(pZipCode) THEN
RETURN pZipCode||' does not exists.';
ELSE
RETURN vZipCodes(pZipCode);
END IF;
END;
Beim Aufruf dieser Funktion wird dann die zu testende Postleitzahl als Parameter
übergeben, z.B.:
dbms_output.put_line(Check_ZipCode(3000));
OK, bis hierher ist das nichts Neues, werden die meisten sagen.
Die Erweiterung in Oracle9i Release2
Vergleichen wir mal das Syntax-Diagramm aus Version 9i Release1
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY BINARY_INTEGER;
mit dem aus 9i Release2:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2( size_limit)];
Die Erweiterung sieht auf dem ersten Blick nicht so "umwerfend" aus, hat aber recht grosse
Auswirkungen.
Wenn wir bei meinem obigen Beispiel bleiben, funktioniert es gut, wenn wir testen, ob es
eine bestimmte Postleitzahl gibt. Aber was ist, wenn wir testen wollen, ob ein bestimmter
Ort existiert?
Der Index, in welchem ich die Postleitzahl gespeichert habe, durfte nur vom Typ
binary_integer sein! Also kann ich darin keine Zeichenketten speichern (oder nur über
einen Umweg, siehe etwas später in diesem Artikel...).
Mit der neuen Oracleversion ist dies überhaupt kein Problem mehr. Ich definiere als
Index(Pointer) einen varchar2-Type (varchar, string oder long gehen übrigens auch):
...
TYPE
vTowns
...
tyTown IS TABLE OF r_zipcode%ROWTYPE
INDEX BY r_zipcode.town%TYPE;
-- das ist varchar2(30)
tyTown;
Dies ist auch gerade ein Beispiel, dass das Array nicht auf einem skalaren Typ aufbauen
muss, es ist auch ein Record (hier vom Typ der Tabelle r_zipcode) möglich.
Die Funktion zum Testen, ob eine bestimmte Stadt existiert, ähnelt der vorigen
Checkfunktion, nur das ich hier keine Fehlermeldung zurückgebe, sondern -1:
FUNCTION Check_Town
( pTown r_zipcode.town%TYPE
) RETURN r_zipcode.zipcode%TYPE IS
BEGIN
IF NOT vTowns.EXISTS(pTown) THEN
RETURN -1;
ELSE
RETURN vTowns(pTown).zipcode;
END IF;
END;
Das einzige Problem dabei ist ein organisatorisches:
Viele Ortschaften werden mehrere Postleitzahlen haben, der Index des Arrays ist aber
eindeutig, dass heisst, es wird (pro Ort) nur die Postleitzahl des zuletzt aus der Tabelle
r_zipcode gelesenen Ortes gespeichert...
Lösung vor Oracle9i Release2
Was ist nun aber, wenn jemand noch nicht mit Oracle9i Release 2 arbeitet (soll es ja doch
noch geben /)?
Dort muss der Index der Index-By-Table zwingend vom Typ binary_integer sein.
Die Frage ist deshalb:
Wie können wir einen Text umwandeln in einen eindeutigen, numerischen Wert?
Und die Antwort dazu liefert uns Oracle:
Wir verwenden einen HASH-Wert auf unseren Text, hier also auf den Ort.
Im Package dbms_utility gibt es dafür eine fertige Funktion:
FUNCTION GET_HASH_VALUE RETURNS NUMBER
Argument Name
Type
In/Out
----------------- ---------- -----NAME
VARCHAR2
IN
BASE
NUMBER
IN
HASH_SIZE
NUMBER
IN
Dieser Funktion muss als erster Parameter der umzuwandelnde Text übergeben werden.
Durch die weiteren Parameter werden Startwert und Anzahl Einträge die HASH-Table
bestimmt.
Damit jeder Text-Wert einen eindeutigen numerischen Wert zugeordnet bekommt, muss
die HASH-Table gross genug anlegt werden. Da die Grösse aber keine 100%ige Sicherheit
bietet, checke ich in meinen Programmen beim Laden der Tabelle immer ab, ob ein Wert
schon mal vorhanden ist, wenn ja, erhöhe ich dynamisch den Wert von HASH_SIZE und
lade die Daten nochmals.
Diese Funktion kann auch direkt in SQL benutzt werden, hier ein Beispiel, wie der HASHWert für die Start Bern gebildet wird, wobei der untere Wert 0 sein soll, der obere max.
999 (also hast die HASH-Table 1000 Einträge):
SQL> SELECT dbms_utility.get_hash_value('Bern',0,1000)
2
FROM DUAL;
DBMS_UTILITY.GET_HASH_VALUE('BERN',0,1000)
-----------------------------------------813
Mit diesem Wissen können wir nun unsere Ladeprozedur anpassen:
...
cHashSize CONSTANT number := 10000;
TYPE
tyTownHash IS TABLE OF r_zipcode%ROWTYPE
INDEX BY binary_integer;
vTownsH
tyTownHash;
...
PROCEDURE Load_ZipCode_Town_Hash IS
CURSOR cData IS
SELECT * FROM r_zipcode;
BEGIN
FOR rData IN cData LOOP
vTownsH(
dbms_utility.get_hash_value(rData.town,1,cHashSize))
:=rData;
END LOOP;
END Load_ZipCode_Town_Hash;
Und die Testprozedur sieht so aus:
FUNCTION Check_Town_Hash
( pTown r_zipcode.town%TYPE
) RETURN r_zipcode.zipcode%TYPE IS
BEGIN
RETURN vTownsH(
dbms_utility.get_hash_value(pTown,1,cHashSize)).zipcode;
EXCEPTION
WHEN no_data_found THEN
RETURN -1;
END;
Performance
Und warum machen wir dies alles? Natürlich um bessere Performance zu erzielen.
Deswegen möchte ich Ihnen die Performanceunterschiede nicht vorenthalten, es soll für
Sie ein Anreiz sein, Associative Arrays (Sie können sie auch immer noch Index-By-Tables
nennen) einzusetzen.
In meinem Test habe ich jeweils 10'000 Abfragen mit unterschiedlichen Zugriffstrategien
getätigt:
Zugriff
Dauer (sek.)
Associatives Array mit 1000 Einträgen, Index= town (unter Nutzung des
0.05
neuen Features)
Associatives Array mit 1000 Einträgen, mit einer HASH-Funktion über town 0.26
als Index (so wie man es schon immer machen sollte)
Index Organized Table mit 1000 Zeilen und zwei Feldern (town, zipcode) – 2.95
also einen Unique Index Zugriff
Normale Tabelle mit dem Feld town Unique indexiert, also einen Unique
3.24
Index Zugriff + einen Tabellen Zugriff über ROWID
Die Unterschiede sind zwischen 0.05 Sekunden (für 10'000 Zugriffe!!) und 3.24 Sekunden
doch erheblich, oder?
Der Zugriff über die "selbstgebaute" HASH-Table ist zwar etwas langsamer als der in 9i
Release2 eingebaute, aber immer noch mehr als Faktor 10 schneller als der Index- bzw.
Tabellenzugriff.
Zusammenfassung
Ich hoffe, dass ich Sie etwas motivieren konnte, Associative Arrays einzusetzen.
Diese und andere neue Features von Oracle9i, Release 2 werden im Techno-Circle "New
Features Oracle9i, Release 2" der Trivadis behandelt.
Dort erhalten Sie auf einer Diskette auch den Quellcode meines Packages mit allen
Beispielen und dem Performancetest. Bei Bedarf können Sie es auch per Mail anfordern.
Verwendete Dokumentation
1. PL/SQL User’s Guide and Reference Release 2 (9.2)
March 2002 (Part No. A96624-01)
Viel Spass mit Oracle und schicken Sie mir eine Mail (am Besten aus Oracle), falls Ihre
Programme schneller geworden sind Sven Vetter
Trivadis AG
Oracle Systems Development
Papiermühlestrasse 159
3063 Ittigen b. Bern
Mail:
Tel:
Fax:
Internet:
[email protected]
+41-31-928 09 50
+41-31-928 09 51
www.trivadis.com