Kein Folientitel

Oracle's Data Warehousing Features
1
DOAG - Regionaltreffen
Hannover
Donnerstag, 06.03.2003
Oracle 9i Data Warehousing Features
Henrik Rünger
Thales-IS
[email protected]
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
© Thales IS GmbH 2002
External Tables
Forall- und Bulk-Collect
Oracle's Data Warehousing Features
2
Agenda EXTERNAL TABLES
© Thales IS GmbH 2002
• Was sind external Tables?
• Beispiele
• technischer Aufbau
• Einschränkungen beim Gebrauch
• Performance Hints
• Vergleich SQL*Loader und external Tables
• Ausblick
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
3
Was sind external Tables?
• Pendant zum SQL*Loader
• Datei befindet sich physisch im Filesystem
• Aussehen z.B. CSV Dateien
• Die Dateien werden innerhalb der DB durch ein
logisches Verzeichnis erkannt
© Thales IS GmbH 2002
• Tabellen können abgefragt und gejoined werden (auch
parallel Benutzung mit SQL)
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
4
Anwendungsgebiete
• Alternative zum konventionellen Datenladen
> da Datenzugriff ohne langwierige Ladeprogramme
> Änderung der Daten ohne Commit/Rollback abwarten zu müssen
• Data Warehouses
© Thales IS GmbH 2002
> grosse Datenlieferungen
> viele Schnittstellen
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
5
© Thales IS GmbH 2002
Beispiele _traditionell
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
6
Traditionelles Laden
• Verarbeitungstabellen
• Arbeitstabellen
• SQL*Loader Konfigurationen
• Ladescripte z.B. PL/SQL
LOAD DATA
INFILE sh_sales.dat
APPEND INTO TABLE sales
( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)
$ sqlldr sh/sh control=sh_sales.ctl direct=true
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
© Thales IS GmbH 2002
FIELDS TERMINATED BY "|"
Oracle's Data Warehousing Features
7
© Thales IS GmbH 2002
Beispiel _als external Table
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
8
Laden als external Tabelle
• Flat-Files == Verarbeitungstabellen
• Datensatzdefinitionen werden auf Minimum reduziert
• Zwischenschritte bei der Verarbeitung entfallen
• Pflegeaufwand wird weitaus geringer
CREATE TABLE sales_transactions_ext
( PROD_ID NUMBER(6), CUST_ID NUMBER, TIME_ID DATE, CHANNEL_ID
CHAR(1), PROMO_ID NUMBER(6), QUANTITY_SOLD NUMBER(3),
AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2), UNIT_PRICE
NUMBER(10,2) )
ORGANIZATION external
INSERT /*+ APPEND */ INTO COSTS (
TIME_ID, PROD_ID, UNIT_COST,
UNIT_PRICE )
DEFAULT DIRECTORY data_file_dir
SELECT TIME_ID, PROD_ID,
SUM(UNIT_COST), SUM(UNIT_PRICE)
FROM sales_transactions_ext
ACCESS PARAMETERS
GROUP BY time_id, prod_id;
( TYPE oracle_loader
© Thales IS GmbH 2002
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE
log_file_dir:'sh_sales.bad_xt' LOGFILE log_file_dir:'sh_sales.log_xt' FIELDS
TERMINATED BY "|" LDRTRIM )
location
( 'sh_sales.dat' )
)REJECT LIMIT UNLIMITED;
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
9
technischer Aufbau
CREATE TABLE sales_transactions_ext (
PROD_ID NUMBER(6), CUST_ID
NUMBER, TIME_ID DATE,
CHANNEL_ID CHAR(1), PROMO_ID
NUMBER(6), QUANTITY_SOLD
NUMBER(3), AMOUNT_SOLD
NUMBER(10,2), UNIT_COST
NUMBER(10,2), UNIT_PRICE
NUMBER(10,2) )
ORGANIZATION external (
TYPE oracle_loader
DEFAULT DIRECTORY data_file_dir
ACCESS PARAMETERS (
BADFILE log_file_dir:'sh_sales.bad_xt' LOGFILE
log_file_dir:'sh_sales.log_xt' FIELDS TERMINATED BY "|" LDRTRIM )
location ( 'sh_sales.dat' )
)REJECT LIMIT UNLIMITED;
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
© Thales IS GmbH 2002
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
Oracle's Data Warehousing Features
10
technischer Aufbau
• Zusätzlich lässt sich beim Insert 9i MERGE verwenden
• Unterschiede zwischen Definition und Daten erlaubt
• geklärt sein muss:
> Datei Format der Quell-Datei
> Datenformat innerhalb dieser Datei
© Thales IS GmbH 2002
• Unterschiede zwischen Definition und Daten werden
über AccessDriver ausgeglichen versucht
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
11
Einschränkungen beim Gebrauch
• Read-Only (kein DML)
• Column processing
• Indexe sind nicht möglich
> Schnelligkeit durch Betriebssystem (RAID, caching)
© Thales IS GmbH 2002
• keine Constraints möglich
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
12
Performance Hints
• vorgegebene Längen in Datensätzen werden schneller
verarbeitet
© Thales IS GmbH 2002
• Joins und Unions sind möglich
• Datentyp-Konvertierung erfordert mehr Zeit
• Condition Clauses (WHEN,NULLIF,DEFAULTIF)
vermindert Ausführungsgeschwindigkeit
• insert as select ist möglich  parallel load
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
13
Vergleich SQL*Loader und external Tables
Discard; Bad File für jedes Datenfile
Für alle Datendateien zusammen nur
jeweils eins
Client NLS Umgebung
Server NLS Umgebung
1/3/5 Sequence
1/2/3 laufende Rejected Auflistung
ContinueIF Concatenate
Werden nicht unterstützt
Graphic, Graphic external, Vargraphic
Werden nicht unterstützt
Leistungsfähigkeit SQL*Loader
Alle Register des Filesystems
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
© Thales IS GmbH 2002
 Achtung! Parallel Load
Oracle's Data Warehousing Features
14
Ausblick
• Reduzierung von Stellen zur Anpassung
• Geschwindigkeitsvorteile beim Massendatenladen
• Vorteile des Filesystems
Zwischenschritt auf dem Weg zur XML-DB
© Thales IS GmbH 2002
> Ablage von XML-Dateien in der DB möglich
> Definition dynamischer als in external Table Definition
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
15
Ende Teil 1
© Thales IS GmbH 2002
Ihre Fragen bitte
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
16
Agenda FORALL- und BULK-Collect
• Einführung
• Begriffserklärung
• Beispiel
• Praxisbeispiel forall & bulk
• Bulk Binding
© Thales IS GmbH 2002
• Deklaration
• Ausblick auf 9i
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
17
Einführung
• PL/SQL greift bei SQL
Anweisungen auf SQL-Engine zu
3x
• SQL-Engine liefert u.U. Daten
zurück
© Thales IS GmbH 2002
• jede Konversation erzeugt
einen Overhead
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
18
Einführung (2)
• Zuweisen von Werten in
PL/SQL Variablen in SQL
Statements = Binding
• Binding einer ganzen
Collection = Bulk Binding
1x
© Thales IS GmbH 2002
• Durch Bulk Binding werden
Kontext Switches minimiert
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
19
© Thales IS GmbH 2002
Beispiel
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
20
Praxisbeispiel forall & bulk
srec sammlung_rec;
CURSOR c_sammlung
IS
select *
from sammlung;
Thales Information Systems GmbH
BEGIN
OPEN c_sammlung;
FETCH c_sammlung BULK COLLECT INTO
srec.nr,srec.bez;
CLOSE c_sammlung;
FORALL k IN 1 .. srec.nr.COUNT
insert into sammlung_forall (nr, bez)
values
(srec.nr(k)
,srec.bez(k));
COMMIT;
END;
Im Team
für Ihren
Erfolg
© Thales IS GmbH 2002
declare
TYPE numarray IS TABLE OF NUMBER;
TYPE varchararray IS TABLE OF
VARCHAR2 (2000);
TYPE datearray IS TABLE OF DATE;
TYPE sammlung_rec IS RECORD (
nr numarray
,bez VARCHARARRAY);
Oracle's Data Warehousing Features
21
Bulk Binding
• SQL Engine wird angewiesen, Daten zu sammeln, ehe
sie an die PL/SQL Engine zurückgeliefert werden
© Thales IS GmbH 2002
• Ziel muss immer eine Collection sein
• Collection von Records ist nicht möglich
• stark in Kombination mit FORALL
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
22
Deklaration
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums,
names FROM emp;
...
END;
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
© Thales IS GmbH 2002
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab;
names NameTab;
Oracle's Data Warehousing Features
23
Ausblick auf 9i
• bei forall schlägt nicht mehr das ganze Statement fehl
• sql%bulk_exceptions collection
© Thales IS GmbH 2002
• Multilevel Collection syntax
• native dynamic SQL ist möglich
• bulk-collect ist mit %ROWTYPE Collection möglich
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg
Oracle's Data Warehousing Features
24
Ende Teil 2
© Thales IS GmbH 2002
Ihre Fragen bitte
Thales Information Systems GmbH
Im Team
für Ihren
Erfolg