DWH_Technik_im_Fokus_Feb_2017_TK_Fassung

Oracle Data Warehouse Technik im Fokus
Praxis-Seminar, Oracle, Feb. 2017
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Die Themen
• Einführung und Konzepte
• Partitionierung
• Schlüssel und Indizierung
• Data Optimization
• Materialized Views
• In-Memory
• ETL
• Security
• Monitoring und Verwaltung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
2
Informationsbedarfe: Wen interessiert was?
Nutzen und
Wettbewerbsvorteile
Vergangenes -> reagieren
Zukünftiges -> agieren
Machine Learning
Standberichte
Interaktive Berichte
Statistik
Algorithmen, Modelle
Simulation
Data Mining
Data Mining
Tag, Monat, Quartal, Jahr
Was ist
geschehen
Warum ist
es geschehen
Was wird
geschehen
Was könnte
geschehen
Reife der Analyse-Aktivitäten im Unternehmen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Evolution des Data Warehouse
DWH-Systeme werden zunehmend auch in einem operativen Sinn genutzt
Hochvolumig / granular
Überschaubar / aggregiert
Operativ
überschaubar
Taktisch
DWH
Strategisch
Jahr/Quartal/Monat
Woche/Tag
Komplexe InformationsAusarbeitung und Analysen
Periodische Berichte
Stunde/Minute/Sekunde/Realtime
oft und schnell
wiederholbare Einzel-informationen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
4
Modelle und Schichten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
5
Unternehmensweites Data Warehouse
Logistik
D a t a
Service
Marketing
W a r e h o u s e
Zentral, unternehmensweit,
einheitlich, verstehbar,
angereichert, historisch
Controlling
Einkauf
Vertrieb
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
6
Historisches – DWH –Konzepte aus den 90ern
Service
Logistik
Stage
D
Kern-Schicht
Große Nähe
zu operativen
Systemen
Near 3NF
granular
F
D
D
F
D
D
D
Stage
Große Nähe
zu operativen
Systemen
Analyse-FokusBezogene
Data Marts
Multidimension
Inmon
D
Vertrieb
Marketing
D
D
Einkauf
Controlling
D
D
F
D
D
F
D
D
Zusammenhängende
multidimensionale
Modelle
(conformed dimensions)
D
D
F
F
D
Multidimension
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Kimball
DATA WAREHOUSE
7
Single Point of True – selten erreichtes Ziel
• Ursprung vieler Daten des Data Warehouse sind operative Vorsysteme mit
teils isolierter Datennutzung
• Viele Warehouse – Systeme sind oft nur eine Ansammlung von
Data Marts und zentraler technischer Administration
– Historisch entstanden, aus zeitlicher Abfolge einzelner Data Mart-Projekte
– Falsch verstandenem Fachabteilungs-Fokus
• Echte Integrationsaufgaben wurden oft vernachlässigt
– Fehlendes Verständnis
– Kostenscheu
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
8
Single Point Of True gelingt nur über die Eindeutigkeit und
Bestimmtheit aller Objekte in dem Data Warehouse
• Zentrales einmaliges Vorhalten von Daten auch wenn Daten durch
unterschiedliche (analytische) Anwendungen mehrfach genutzt werden
• Innerhalb des (Data Warehouse-) Systems werden Bezeichner nur
einmal zur Identifizierung von Objekten genutzt
– Synonyme / Homonyme auf Attribut + Objektebene sind aufzulösen
• Eindeutigkeit ist das Ergebnis echter Integrationsarbeit
* S. Brenner 1988
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
9
Das missverstandene Data Warehouse Prinzip
Stage
Technisches Data Warehouse
Service
B
Servicekunde
D
S
Logistikaufwand
B
Controlling
Vertrieb
Kundenhistorie
S
S
B
SCleansing
S
S
B
Marketing
Marketingsicht
D
S SS
Logistik
Einfache
Profitabilität Kopie
operativer
Einkauf
Daten
Produkte&Trends
Data Marts
B
S
S
S
Teilweise
isolierte
Daten
BI-Tool
D
F
D
D
F
D
Isolierte
Data
D
Marts
D
D
F
D
D
F
D
Sachgebietsbezogene
(isolierte)
Analysen
D
Eindeutigkeit
kaum
kontrollierbar
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
10
Das missverstandene Data Warehouse Prinzip
• Kein echter „Single Point of True“
• Bedeutungs-Schwergewicht liegt auf den Data Marts
–
–
–
–
Müssen permanent weiter entwickelt werden
Können nicht ad hoc neu aufgebaut werden
Unflexibel, fehlende Änderungsfreundlichkeit
Fehlendes übergreifende Abfragemöglichkeit
• Zentrale Warehouse-Schicht dient nur der Datenbereitstellung für die Data Marts
– keine konsolidierende Funktion
– nur Cleansing
• Stage: nur Durchgangs-Kopierfunktion aus Vorsystemen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
11
Informationsbeschaffung organisieren
• Integration Layer (Stage)
– Zusammenführen von Daten aus unterschiedlichen Vorsystemen
– Prüfen, harmonisieren, brauchbar machen
• Enterprise Layer (DWH-Kernschicht)
–
–
–
–
Zentrale Ablage aller Informationen des Data Warehouse
Stammdaten, Referenzdaten, Bewegungsdaten
Prozess- /Referenz, Stammdaten
Langlebig, strategisch
• User View Layer (Data Marts)
– Nach Sachgebieten sortierte analysefähiger Ausschnitt von Daten der
Kernschicht
– Endbenutzerverständlich
– Analysezeitraum- und Projekt-bezogen, taktisch
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Die strategische Rolle der Warehouse-Schicht
Service
Integration
Layer
Enterprise Layer
Core - DWH / Info Pool
User View
Layer
Logistik
Einkauf
Service
Logistik
?
Strategische
Daten
?
Einkauf
Vertrieb
Vertrieb
Controlling
Controlling
Marketing
Wirkungsweite des
Systems muss
festgelegt sein
Eindeutigkeit in
der Kernschicht
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Marketing
DATA WAREHOUSE
Die strategische Rolle der Warehouse-Schicht
Service
Logistik
Einkauf
Vertrieb
Integration
Layer
Die
selben
Geschäftsobjekte
in unterschiedlichen
Prozessen
?
Enterprise Layer
Core - DWH / Info Pool
Strategische
Daten
Controlling
Marketing
Wirkungsweite des
Systems muss
festgelegt sein
User View
Layer
Wunsch
für
unterschiedliche
Sichten
auf die
selben
Geschäftsobjekte
?
Eindeutigkeit in
der Kernschicht
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Service
Logistik
Einkauf
Vertrieb
Controlling
Marketing
DATA WAREHOUSE
Was verdienen wir an gelben + bunten Fahrrädern?
Logistik
WARE
Waren_Nr
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Einkauf
ARTIKEL
Artikel_Nr
Einheit
Preis
Lieferant
Vertrieb
PRODUKT
Produkt_Nr
Einheit
Farbe
Preis
Integration
T_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
Lieferant
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Vert_Einheit
Farbe
Vert_Preis
Rabatte
LieferantenDiscounts
Enterprise Layer
LIEFERANT
PK_Lieferanten_ID
Lieferant_Name
Es geht um
Gesamtsichten:
Verpackungen
S_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
FK_Lieferanten_ID
Gebinde
Gewicht
PK_Verpackungs_ID
Vert_Einheit
FK_Farben_ID
Vert_Preis
FARBE
PK_Farben_ID
Farbe
Aufschlag
User View
„Breite“ der
Datenmodelle
Gelagerte
Artikel,
Menge +
Größe
Lager
Lieferanten
Gekaufte
Artikel,
Menge +
Preise
Zeit
VERPACKUNGSART
PK_Verpackungs_ID
Hoehe
Laenge
Breite
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Artikel
Verkaufte
Artikel
Menge +
Preise
Gewinn =
Verkaufspr. –
Einkaufspr. –
Lagerkosten
DATA WAREHOUSE
Was verdienen wir an gelben + bunten Fahrrädern?
Logistik
WARE
Waren_Nr
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Einkauf
ARTIKEL
Artikel_Nr
Einheit
Preis
Lieferant
Vertrieb
PRODUKT
Produkt_Nr
Einheit
Farbe
Preis
Integration
T_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
Lieferant
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Vert_Einheit
Farbe
Vert_Preis
User View
Enterprise Layer
LIEFERANT
PK_Lieferanten_ID
Lieferant_Name
Verpackungen
S_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
FK_Lieferanten_ID
Gebinde
Gewicht
PK_Verpackungs_ID
Vert_Einheit
FK_Farben_ID
Vert_Preis
FARBE
PK_Farben_ID
Rabatte
Farbe
Lieferanten- Aufschlag
Discounts
VERPACKUNGSART
PK_Verpackungs_ID
Hoehe
Laenge
Breite
D_ARTIKEL_LAGER
PK_Artikel_ID
Log_Waren_Nr
Gebinde
Gewicht
Verpackung
D_ARTIKEL_EINK
PK_Artikel_ID
Eink_Artikel_Nr
Eink_Einheit
Eink_Preis
Lieferanten
Farben
D_ARTIKEL_VERT
PK_Artikel_ID
Vert_Produkt_Nr
Eink_Einheit
Vert_Einheit
Farben
Vert_Preis
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Lager
Gelagerte
Artikel,
Menge +
Größe
Lieferanten
Gekaufte
Artikel,
Menge +
Preise
Zeit
Verkaufte
Artikel
Menge +
Preise
DATA WAREHOUSE
Es geht
um
Gesamtsichten:
„Breite“
der
Datenmodelle
Die strategische Rolle der Warehouse-Schicht
• Standardisierte Begriffe / Bezeichner
– Definition aller Datenobjekte (Sachverhalte, Kennzahlen)
– Lösung der Homonymen / Synonymen – Thematik
– Informationen (Daten) sind nur ein Mal vorhanden (Eindeutig / Redundanzfrei)
• Definieren der nötigen Granularität (Business Events)
• Bereithalten aller Informationen zum Aufbau von Auswertemodellen
– Historisierung / Langlebigkeit der Informationen
– Schlüsselinformationen (enthält künstliche und Original-Schlüssel)
– Hierarchisierungs-Informationen (Optionen für späteres multidimensionales
Modell)
• Anwendungs- und Geschäftsprozess-neutral
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Künstliche Schlüssel im Data Warehouse
• Gründe für den zusätzlichen Aufwand künstlicher Schlüssel sind:
– Integration
• In mehreren Vorsystemen gibt es unterschiedliche Schlüssel
– Stabilität
• Natürliche Schlüssel können sich ändern
• Geschäftsbereiche können sich ändern
– DWH langlebiger als operative Anwendungen
• Schlüssel sind einfach zu benutzen und kurz, um
– Speicherplatz zu sparen
– Fehler zu vermeiden
• Nach Möglichkeit keine zusammengesetzten Schüssel
– Erfordert beim Zugriff unnötig viel Vorwissen zu den einzelnen Schlüsselbestandteilen
– Schlüsselbestandteile können leicht NULL-Wert annehmen, die Eindeutigkeit ist gefährdet
• Keine Felder wählen, die NULL werden können
• Spaltenwerte sollten stabil sein und sich nicht mehr ändern
18
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Hilfsmittel bei dem Auffinden von Synonymen
• Ziel:
Methoden und Hilfsmittel zur Synonymensuche
– Wiederverwendung von Objekten
und Informationen
• Analyse von Bezeichnern – Feldliste
– Standardisierung von Bezeichnern
• Wortstammanalyse
• Methodenmix
• Arbeiten mit Standard-Abkürzungen
• Permanente Aufgabe während
der Weiterentwicklung des
Systems
• Beschreibung durch Deskriptoren
• Aufwand sollte berücksichtigt
werden
• Synonymen-Suche durch Normalisierung von
Entitäten
• Systematische Datentypklassifikation
Hilfsmittel: Business Glossar
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
19
Wo werden wann welche Artikel verkauft?
Zeit
Tag
Woche
Monat
Quartal
Jahr
Dekade
Hierarchie
Tag/Monat/
Quartal/Jahr
Artikel
Hierarchie
Tag/Woche/Jahr
Parent
Hierarchie
Parent
Zeit_ID
Artikel_ID
Region_ID
Channel_ID
Menge
Umsatz_Pos
Channel
Medium
Kampagne
Channel
Keys
Gruppen_Name
Gruppen_Nr
Artikel_Name
Artikel_Nr
Artikel_ID
Food
Non-Food
Services
Sanitär
Garten
Elektro
Aggregation
Aggregation
Business Key
Künstlicher
Dimension Key
Bohrhammer 4711
Farbtopf_Lack_rot
CU_Muffe_18mm
Facts /Kennzahlen
Ort
VertGebiet
Kreis
Bundesland
Land
Region
Sparten_Name
Sparten_Nr
• Multidimensionales Modell
• Star Schema
Region
• Intuitives (End-benutzer-geeignetes)
Modell
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Data Optimization
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
21
Es gibt Data Warehouse-spezifische Rahmenbedingungen
OLTP
Data Warehouse
Interaktion
Lesen und Schreiben
Eher nur Lesen / konzentriertes
Mengen-Schreiben
Verteilung der
Aktionen
Oft auf viele Tabellen
verteilt
Oft zentrisches Arbeiten auf
wenigen Tabellen
Art von Lese-/SchreibAktionen
Verteilt und nur einzelne
Sätze
Konzentriert und viele Sätze
gleichzeitig
Art der physischen
Speicherung
Satzorientiert in kleinen
Blöcken
Spaltenorientiert in sortieren Blöcken
Anzahl Benutzer
Hoch
Weniger
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
22
Was bedeutet das für die eingesetzte Technologie?
OLTP
Data Warehouse
Interaktion
Lesen und Schreiben
Eher nur Lesen / konzentriertes
Mengen-Schreiben
Parallelisierung
Gering
Hoch
Hochverfügbarkeit
Hoch
Eher gering
Storagesystem
SAN
Dedicated
Backup
komplett
selektiv
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
23
Bei der Wahl von Mitteln immer berücksichtigen:
10 – 50 Tabellen
500 – 1000 Tabellen
Große Tabellen
Partitioniert
Namentlich bekannt
> 70 % des Datenvolumens
24
KleineTabellen
Nicht Partitioniert
Unkenntliche Masse
< 30 % des Datenvolumens
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Abfragen
100
95
Speicherplatz
5%
35 %
100 %
60 %
50 %
60
10 %
60 % aller Abfragen benötigen weniger als 10% der DWH Daten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
25
50 TB Data Warehouse (z. B. Exadata ¼ Rack)
RAM
2010
2013
2017
5%
10%
20%
15 %
20 %
50-100 %
80 %
70 %
0-50%
100-200 mal schneller
Flash
100-200 mal schneller
SAS/Sata
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
26
60 – 70 % der Data Warehouse – Abfragen
können heute InMemory stattfinden
(allerdings sind nicht alle Abfragen InMemory-tauglich)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
27
Partitionierung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partitioning unterstützt viele Aufgaben
Große Tabellen
Query Performance
Partition Pruning
Beschleunigung
des Ladeprozesses
Unterstützung ILM
(Information Lifecycle
Management)
Leichterer Umgang mit
Indizierung
Unterstützung im
Backup-Prozess
Steuerung der
Komprimierung
Unterstützung bei der Aktualisierung von Materialized Views
(Partition Change Tracking)
Hochverfügbarkeit auch
während des Ladens und
Maintenance
Tablespace
Tablespace
Tablespace
Tablespace
Tablespace
Tablespace
Feld für PartitionierungsKriterium nach fachlichen oder
organisatorischen
Gesichtspunkten
ausgewählt
29
Range
List
Hash
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partitioning Varianten und Verwaltung
Partitioning-Typen
• Range
• List
• Hash
•
•
•
•
•
Reference
Interval
Interval-Reference
System
Virtual Column
Subpartitioning-Typen
• Range - Hash
• Range - List
• Range - Range
• List - Range
• List - Hash
• List – List
• Hash - Hash
Management von Partitioning
• ADD PARTITION
• DROP PARTITION
• TRUNCATE PARTITION
• MOVE PARTITION
• SPLIT PARTITION
• MERGE PARTITION
• EXCHANGE PARTITION
• Verändern der Default-/
realen Attribute
• Partition Exchange Loading
30
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partitioning ist transparent
• Gesamte Tabelle selektieren
SELECT * FROM orders;
Alle Partitionen werden selektiert
• Abfrage nur auf eine Partition
Jan 2017
Feb 2017
SELECT * FROM orders
WHERE order_dat between
Partition
Pruning: AND
to_date
('2017-01-01')
Automatische
Beschränkung
to_date
('2017-01-31');
Mär 2017
Apr 2017
auf
betroffene
Partition
SQL-Abfrage
ist von
Partitionierung
unabhängig
Mai 2017
Jun 2017
31
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partition Elimination / Dynamic Pruning
• Nicht angesprochene Partitionen
werden nicht gelesen
• Subpartitions erlauben zusätzliche
Eliminierung
• Range
– Bereichsabfragen mit Operatoren
• IN,
• LIKE,
• BETWEEN auf Partition Keys
32
• Hash
– Gleichheitsabfragen mit
Operatoren
• =,
• IN auf Partition Keys
• List
– Range oder List auf Partition
Keys
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Range Partitioning - Partitionierung nach Wertebereichen
• Partitionierung nach Wertebereichen
– Für sortierte Wertebereiche
– LESS THAN:
Angabe eines maximalen Wertes pro Partition
CREATE TABLE F_Umsatz (
Artikel_ID
number,
Kunden_ID
number,
Zeit_ID
DATE,
Region_ID
number,
Umsatz
number,
Menge
number
) PARTITION BY RANGE (Zeit_ID) (
PARTITION M1 VALUES LESS THAN
(to_date('2017-02-01','YYYY-DD-MM')),
PARTITION M2 VALUES LESS THAN
(to_date('2017-03-01','YYYY-DD-MM')),...
33
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Jan 2017
Feb 2017
Mär 2017
Apr 2017
Mai 2017
Jun 2017
DATA WAREHOUSE
Interval Partitioning
• Erweiterung der Range-Partitionierung
• Automatisierung für gleichgroße
Range-Partitionen
• Partitionierung wird als
Metadaten-Information abgelegt
April
März
Februar
• Sobald neue Daten hinzukommen
werden Segmente allokiert
Januar
Maidaten
?
Aprildaten
April
Märzdaten
März
Februardaten
Januardaten
• Lokale Indizes werden automatisch
mitgepflegt
Produkte
– Start-Partition ist dabei persistent
Mai
Februar
Januar
Kunden
• Partition Key muss NUMBER oder DATE sein
• Partition-Typ muss RANGE sein
34
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Interval Partitioning Syntax
CREATE TABLE "BESTELLUNG" (
"BESTELLNR"
NUMBER(10) NOT NULL,
"KUNDENCODE"
NUMBER(10),
"BESTELLDATUM"
DATE,
"AUFTRAGSART" VARCHAR2(30))
PARTITION BY RANGE ("BESTELLDATUM")
INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) (
PARTITION "Jan07"
VALUES LESS THAN (TO_DATE(' 2017-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')) TABLESPACE "TS_PAR„
,.............)
;
CREATE TABLE „POSITION" (
"POSITIONSNR" NUMBER(10) NOT NULL,
"BESTELLNR"
NUMBER(10)
"ARTIKELNUMMER"
NUMBER) contraint FK_BEST FOREIGN KEY (BESTELLNR) REFERENCES
BESTELLUNG PARTITION BY REFERENCE (FK_BEST);
35
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Interval Partitioning – Nachträgliches Ändern der Zyklen
• Range-partitionierte Tabellen können umgestellt werden
• Einfaches Metadaten Kommando
• Investitionsschutz
Table F_Umsatz_Interval
...
...
2005
Q1 2006
Q2 2006
...
Oct 2006
Bisherige Range Partition Table
Neue monatliche
Interval Partitions
ALTER TABLE F_Umsatz_Interval SET INTERVAL(NUMTOYMINTERVAL(1,'month');
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
List Partitioning
AMER
• Für diskrete, unsortierte Werte
EMEA
• Angabe einer Werteliste pro Partition
• VALUES (DEFAULT) für “alles andere”
APAC
– Verhalten wie MAXVALUE in der Range-Partitionierung
CREATE TABLE bestellung (
bestellnr number,
auftragsart varchar2(40),
land varchar2(2)
)
PARTITION BY LIST (land) (
PARTITION EMEA VALUES ('DE','FR',[..]),
PARTITION AMER VALUES ('US','CA',[..]),
PARTITION APAC VALUES ('JP','CN',[..]),
PARTITION OTHERS VALUES (DEFAULT)
)
37
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Hash Partitioning - Gleichverteilung der Daten
• Partitionierung nach Hash-Wert des Partition Key
– Schlüsseltypen:
Alle built-in Datentypen außer ROWID, LONG, LOB
– Ziel: Gleichverteilung der Daten
– Anzahl Partitionen: als Potenz von 2 empfohlen
CREATE TABLE F_Umsatz_HASH (
Artikel_ID
number,
Kunden_ID
number,
Zeit_ID
DATE,
Region_ID
number,
Umsatz
number,
Menge
number
) PARTITION BY HASH (Zeit_ID)
PARTITIONS 4
38
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Reference Partitioning
• Anwendung im DWH eher selten
• Beispiel abhängige Faktentabellen
• Bondaten (Kopf- / Positionsdaten)
PK
BestellNr
KundenNr
BestellDatum
PK
FK
April
März
Februar
Januar
Bestellungen
BestellNr
ArtikelNr
Menge
PosNr
FK
April
BestellNr
LieferNr
März
PosNr
April
Februar
März
Januar
Februar
Bestell_Positionen
Januar
Auslieferungen
39
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Interval-Reference Partitioning
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Virtual Column Partitioning
Produktnummer
Menge
Preis
4711GBEMP9147
370
32,50
....
2385GBEMP1239
579
22,10
....
0801GBEMP1138
120
16,30
....
4711LEERM9147
750
89,50
....
2385LEERM1239
589
12,70
....
0801LEERM1138
121
11,20
....
4721UAGBM9147
837
39,50
....
1385UAGBM1039
599
17,10
Partitionierung einer
Bestelltabelle nach den
0901UAGBM1338
578
17,70
Produktgruppen.
Die Nummer der Produktgruppen
substr(Produktnummer,4,5)
ist allerdings Bestandteil
der Produktnummer
(5.- 9. Stelle).
....
• Anwendung im DWH
eher selten
• IM ETL-Prozess würde
man stattdessen eine
zusätzliche Spalte
erstellen
Abnehmer
P1
P2
P3
....
• Für alle Partitioning-/Subpartitioning-Varianten einsetzbar
42
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Composite Partitioning
• Range ...
– Range – Range
– Range – Hash
– Range - List
• List ...
– List - Range
– List - Hash
– List - List
43
JAN 07
FEB 07
MAR 07
Produkt
Service
Storno
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
PK
ARTIKEL_ID
FK
KUNDEN_ID FK
ZEIT_ID
FK
REGION_ID
FK
KANAL_ID
UMSATZ
FK
MENGE
UMSATZ_GESAMT
Partitionierung im Star
Range-Partitioning
nach Zeit
Mehr als 80 % aller
Partitionierungen sind
so aufgebaut.
F_UMSATZ
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
44
PK: Btree Index
FK: Bitmap Index
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
PK
ARTIKEL_ID
FK
KUNDEN_ID FK
ZEIT_ID
FK
REGION_ID
FK
KANAL_ID
UMSATZ
FK
MENGE
UMSATZ_GESAMT
Partitionierung im Star
Range-Partitioning
nach Zeit
List-Partitioning
nach Vertriebskanal
Mehr als 80 % aller
Partitionierungen sind
so aufgebaut.
F_UMSATZ
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
45
PK: Btree Index
FK: Bitmap Index
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beispiel Range-List
+
CREATE TABLE f_umsatz_range_list
(ARTIKEL_ID
NUMBER(10),
KUNDEN_ID
NUMBER(10),
ZEIT_ID
DATE,
REGION_ID
NUMBER(10),
KANAL_ID
NUMBER(10),
UMSATZ
NUMBER(10),
MENGE
NUMBER(10),
UMSATZ_GESAMT
NUMBER(10) )
PARTITION BY RANGE (ZEIT_ID)
SUBPARTITION BY LIST (KANAL_ID) SUBPARTITION
TEMPLATE
(
SUBPARTITION kanal1 VALUES (1),
SUBPARTITION kanal2 VALUES (2),
SUBPARTITION kanal3 VALUES (3),
SUBPARTITION kanal4 VALUES (4),
SUBPARTITION kanal5 VALUES (5),
SUBPARTITION kanal6 VALUES (6),
SUBPARTITION kanal7 VALUES (7)
)
(
PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01','SYYYY-MM-DD')),
PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03-01','SYYYY-MM-DD')),
PARTITION nov11 VALUES LESS THAN (TO_DATE('2011-12-01','SYYYY-MM-DD')),
PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')),
PARTITION next_month VALUES LESS THAN (MAXVALUE));
List-List
CREATE TABLE "BESTELLUNG"
(
"BESTELLNR" NUMBER(10) NOT NULL,
"KUNDENCODE" NUMBER(10),
"BESTELLDATUM" DATE,
"LIEFERDATUM" DATE,
"BESTELL_TOTAL" NUMBER(12, 2),
"AUFTRAGSART" VARCHAR2(30),
"VERTRIEBSKANAL" NUMBER
)
PARTITION BY LIST ("VERTRIEBSKANAL")
SUBPARTITION BY LIST ("AUFTRAGSART")
SUBPARTITION TEMPLATE
(
SUBPARTITION Produkt VALUES ('ARTIKEL','TAUSCHWARE'),
SUBPARTITION Service VALUES ('SERVICE','REISE'),
SUBPARTITION Storno VALUES ('RETOURE','KOMMISSION'),
SUBPARTITION Andere VALUES (default)
) (
PARTITION Telefon
VALUES (1,2,3),
PARTITION Aussendienst VALUES (4,5),
PARTITION Web
VALUES (6,7),
PARTITION PARTNER
VALUES (8,9,10) );
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Beispiel für die nachfolgende
Systemabfrage
DATA WAREHOUSE
Systemabfragen
col subpartition_name format a15
col partition_name format a15
col SUBPARTITION_POSITION format 99999999
SELECT table_name, partition_name,
subpartition_name, subpartition_position
FROM user_tab_subpartitions;
TABLE_NAME
-------------------BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
BESTELLUNG
47
PARTITION_NAME
--------------Jan08
Jan08
Jan08
Jan08
Feb08
Feb08
Feb08
Feb08
Mar08
Mar08
Mar08
SUBPARTITION_NA SUBPARTITION_POSITION
--------------- --------------------Jan08_ANDERE
4
Jan08_STORNO
3
Jan08_SERVICE
2
Jan08_PRODUKT
1
Feb08_ANDERE
4
Feb08_STORNO
3
Feb08_SERVICE
2
Feb08_PRODUKT
1
Mar08_ANDERE
4
Mar08_STORNO
3
Mar08_SERVICE
2
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partitionwise Join im DWH
• Das Partitionieren von Dimensionen ergibt heute oft nur eingeschränkt
einen Sinn
– Oft zu klein im Vergleich zu einer Faktentabelle
– Fachlich ist meist kein gleiches Partitionierungskriterium zu finden
• Ausnahmen
– Abfragen über 2 große Tabellen z. B. große Faktentabellen
Bonköpfe
Zahlungen
Leistungen
1:n
Bonpositionen
48
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Add / Rename / Truncate Partition
• Add Partition (Globale und lokale Indizes bleiben “USABLE”)
ALTER TABLE BESTELLUNG
ADD PARTITION "NOV08"
VALUES LESS THAN (to_date('2008-11-30 00:00:00','SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "TS_PAR"
• Umbenennen einer Partition
ALTER TABLE Bestellung RENAME PARTITION Andere TO Bestellung_Rest;
• Truncate einer Partition
ALTER TABLE Bestellung TRUNCATE PARTITION Service DROP STORAGE;
• Ändern des Tablespace einer Partition (Wirkt sich nur auf künftige Partitionen aus)
ALTER TABLE BESTELLUNG
MODIFY DEFAULT ATTRIBUTES FOR PARTITION "Jan08"
TABLESPACE TS_PAR_JAN ;
49
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
CREATE TABLESPACE TS_PAR_Archiv DATAFILE ‚
D:\o11\oradata\o11\TS_PAR_Archiv.f' SIZE 10m REUSE;
Moving Partition
ALTER TABLE BESTELLUNG
MOVE PARTITION "Jan08"
TABLESPACE TS_PAR_Archiv;
• Fragmentierung beheben
• Umziehen in einen anderen Tablespace
CREATE TABLESPACE TS_PAR_Archiv_Jan DATAFILE
'D:\o11\oradata\o11\TS_PAR_Archiv_Jan.f' SIZE
10m REUSE;
• Komprimierung der Daten einer Partition
ALTER TABLE BESTELLUNG
MOVE SUBPARTITION "Jan08_STORNO"
TABLESPACE TS_PAR_Archiv_Jan;
TS_Einzel_1
TS_Gesamt
50
P1 sub
sub
sub
P2 sub
sub
sub
P3 sub
sub
sub
P4 sub
sub
sub
P5 sub
sub
sub
P6 sub
sub
sub
P7 sub
sub
sub
sub
TS_Einzel_2
• Transparente MOVE PARTITION ONLINE
Operation
• Gleichzeitig DML und Abfragen möglich
• Index Pflege für lokale und globale Indizes
sub
TS_Archiv
sub
sub
sub
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Split und Merge von Partition
ALTER TABLE Bestellung
SPLIT PARTITION "Jan08"
AT (to_date('15-JAN-2008','DD-MON-YYYY'))
INTO (PARTITION Jan08_1, PARTITION Jan08_2)
UPDATE GLOBAL INDEXES;
SELECT table_name, partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'BESTELLUNG ';
ALTER TABLE BESTELLUNG
MERGE SUBPARTITIONS TELEFON_STORNO, TELEFON_ANDERE
INTO SUBPARTITION TELEFON_OBJEKTE
TABLESPACE TS_PAR;
sub1
subX
sub2
Par1
• Verschmolzen wird immer eine Liste von
Partitionen / Subpartitionen
ParX
Par2
• Nicht für Reference / Hash Partitioned
Tables anwendbar
• Indizes werden UNUSABLE gesetzt
51
• Die neue Partition darf noch nicht existieren
• Funktioniert auch für Reference Partitioning,
d.h. MERGE-Operation auf Parent Table wirkt
sich auch auf die abhängige Tabelle aus
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Erweiterte Partitionspflege Operationen
• Ein Operation bezieht sich auf mehrere Partitionen
• Parallelisiert
• Transparente Pflege lokaler und globaler Inidizes
ALTER TABLE orders
MERGE PARTITIONS Jan2009, Feb2009, Mar2009
INTO PARTITION Quarter1_2009 COMPRESS FOR ARCHIVE HIGH;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Ändern von Werten bei List Partitioning
SQL> ALTER TABLE Bestellung
2 MODIFY PARTITION Produkt
3 ADD VALUES ('WARE');
Tabelle wurde geändert.
SQL> ALTER TABLE Bestellung
2 MODIFY PARTITION Storno
3 DROP VALUES ('KOMMISSION');
Tabelle wurde geändert.
SQL> SELECT partition_name, tablespace_name, high_value
2 FROM user_tab_partitions
3 WHERE table_name = 'BESTELLUNG';
PARTITION_NAME
-------------------------------PRODUKT
'WARE'
SERVICE
STORNO
ANDERE
53
TABLESPACE_NAME
HIGH_VALUE
----------------------------------------------------------USERS
'ARTIKEL', 'TAUSCHWARE', 'PRODUKT',
USERS
USERS
USERS
'SERVICE', 'REISE'
'RETOURE'
default
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Erstellen einer Tabelle aus einer Partition EXCHANGE
SQL> CREATE TABLE Bestellung_Produkte AS
2 SELECT * FROM bestellung WHERE 1=2
SQL> /
Tabelle wurde erstellt.
SQL> ALTER TABLE Bestellung
2 EXCHANGE PARTITION Produkt
3 WITH TABLE Bestellung_Produkte;
TS_Gesamt
54
P1 sub
sub
sub
P2 sub
sub
sub
P3 sub
sub
sub
P4 sub
sub
sub
P5 sub
sub
sub
P6 sub
sub
sub
P7 sub
sub
sub
 Die Zieltabelle muss existieren
Tabelle X
Allgemeine Syntax:
ALTER TABLE <Tabellen-Name>
EXCHANGE PARTITION <Partition-Name>
WITH TABLE <neue Tabelle>
<including | excluding> INDEXES
<with | without> VALIDATION
EXCEPTIONS INTO <Schema.Tabellen-Name>;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Wo und wie wird im DWH partitioniert
Lade-Aktivitäten
Partitioning und Ladesteuerung laufen oft synchron
Parallelisierung
Lese-Aktivitäten
Partition Pruning
Parallelisierung
Data Integration Layer
User View Layer
Enterprise Information Layer
R: Referenztabellen
T
20%
T
PEL
R
R
S
S
S
D
T: Transfertabellen
D
S: Stammdaten
B: Bewgungsdaten
F
B
T
80%
D: Dimensionen
D
F: Fakten
D
B
ILM
Backup
Vorbereitung
Temporäre
Tabellen
(Prüfungen etc)
56
Bewegungsdaten sind
Partitioniert (80/20 – Prinzip)
Faktentabellen
und Würfel
können partitioniert
sein
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Wo wird Partitioning wieder vorkommen
• Indizierung
• Bessere Verwaltung großer Tabellen
• Materialized Views
• Verbesserung der Verfügbarkeit und
Performance
• Partition Exchange and Load
• Parallelisierung
• Life Cycle Management /
Storage Management
• Ressourcen werden geschont und geben
Rechenkapazitäten frei
• Transparente Anwendung, d.h. Nutzung ohne
Änderung an der Applikation
• Ermöglicht Information Lifecycle
Management: Nutzung unterschiedlicher
Storage-Klassen, je nach Zugriffshäufigkeit ->
Einsparung von Storagekosten
57
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Schlüssel und Indizierung im
Data Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Schlüssel / Indizierung
• Künstliche Schlüssel im Data Warehouse (Konzept)
• Indexvarianten
– Btree -> Single Row Access (OLTP)
– Bitmap -> bessere Unterstützung bei Mengenoperationen
• Star Schema Transformation
• Partitionierung von Indexen (local, global)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
59
B*Tree Index – 4 Zugriffe bis zum Wert
1
2
Clustering
Factor
Zugriff über die
RowID
3
4
60
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Bitmap – Zugriff auf Werte per Bit Stream
Rowid
Name
Abschluss
Rating
AAAHfVAAJAAAKOKAAA
Meier
Klasse_10
5
AAAHfVAAJAAAKOKAAB
Schubert
Abitur
5
AAAHfVAAJAAAKOKAAC
Klaus-Gustav
Abitur
5
AAAHfVAAJAAAKOKAAD
Schmidt
Diplom
5
AAAHfVAAJAAAKOKAAE
Langbein
Doktor
5
AAAHfVAAJAAAKOKAAF
Hund
Klasse_10
5
AAAHfVAAJAAAKOKAAG
Vogel
Abitur
5
AAAHfVAAJAAAKOKAAH
Messner
Abitur
5
Abschluss=
Klasse_10
SELECT Name
FROM KD_Table
WHERE Abschluss=‘Diplom‘;
61
Abschluss=
Abitur
Abschluss=
Diplom
Abschluss=
Doktor
AAAHfVAAJAAAKOKAAA
1
0
0
0
AAAHfVAAJAAAKOKAAB
0
1
0
0
AAAHfVAAJAAAKOKAAC
0
1
0
0
AAAHfVAAJAAAKOKAAD
0
0
1
0
AAAHfVAAJAAAKOKAAE
0
0
0
1
AAAHfVAAJAAAKOKAAF
1
0
0
0
AAAHfVAAJAAAKOKAAG
0
1
0
0
AAAHfVAAJAAAKOKAAH
0
1
0
0
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Platzverbrauch im Vergleich
CREATE TABLE I_Kunde
(KD_NR
Name
Geb_Dat
Bildungsgruppe
KR_Rating_1_bis_Variabel
Tests mit unterschiedlicher
Kardinalität
number,
varchar2(30),
date,
varchar2(30),
number);
SELECT index_name,index_type blevel, leaf_blocks, distinct_keys FROM
user_indexes;
Anzahl
Sätze
Bildungsgruppe
Bildungsgruppe
Geb_Dat
KR_Rating_1_bis_Variabe
KD_NR
62
100000
100000
100000
100000
100000
Distinct
Werte Prozent
5
100
14575
43211
100000
Leaf_
Blocks
BTree
0.005
0.1
14.575
43.211
100
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
271
192
265
220
222
Leaf_
Blocks
bitmap
11
34
97
179
348
DATA WAREHOUSE
(Bitmap-) Indizierung – Ideal für Warehouse-Abfragen
Selektivität
D_KUNDE (1 Million)
(Dimension)
F_UMSATZ
(Fakten)
KANAL_ID
FK
KUNDEN_ID FK
ZEIT_ID
FK
REGION_ID
FK
ARTIKEL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
D_ARTIKEL
(Dimension)
ARTIKEL_ID PK
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
KUNDEN_ID PK
KUNDENNR
GESCHLECHT
VORNAME
NACHNAME
TITEL
GEBDAT
BRANCHE
WOHNART
KUNDENART
BILDUNG
ANZ_KINDER
EINKOMMENSGRUPPE
BERUFSGRUPPE
STATUS
KONTAKTPERSON
FIRMENRABATT
BERUFSGRUPPEN_NR
BILDUNGS_NR
EINKOMMENS_NR
WOHNART_NR
KUNDENKARTE
• Bitmap-Indizierung für
Unique
3 Werte
0,0003 %
• WerteMengenorientierte
Bereichsabfragen
20 Werte
0,0020 %
• Immer bei
Selektivität < 30%
10 Werte
0,0010 %
20 Werte
100 Werte
0,0020 %
0,01 %
Unique
2 Werte
• Fast immer bei
Dimensionsattributen
0,0002 %
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Aktualisieren von Indexen
• OLTP-Option: Rebuild Index Operation
ALTER INDEX index_name REBUILD [ NOLOGGING ];
• Schneller als DROP / CREATE
– NOLOGGING-Klausel
• Fragmentierung wird beseitigt
• Wenig hilfreich im DWH
– Änderungen aber oft als Batch-Lauf durchgeführt
 Zunächst DROP INDEX (beschleunigt den Batch-Lauf)
Dann Neuerstellen des Index
Oder 1. INDEX auf Unusable setzen [Alter index index_name usable]
2. ETL-Massen-Load
3. INDEX Rebuild [Alter index index_name rebuild]
64
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partitioning und Indizes
• Lokale Indizes
• Nicht-partitionierte oder partitionierte globale Indizes
• USABLE oder UNUSABLE Index Segmente
– Nicht-persistenter Index Status
– Losgelöst von der Tabelle
• Partielle lokale und globale Indizes
– Erwirkt spezielle Metadaten auf [Sub]Partitionsebene
– Interagiert mit dem USABLE/UNUSABLE Status für lokale Indizes
– Indizierung jederzeit anpassbar
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Prefixed / Non Prefixed Indexes
Local Index
Indiziert nach
Bestelldatum
Einfacher
Index
Im DWH relevante Variante
66
Indiziert nach
Bestelldatum,
Kundennummer
Non-Prefixed
Indiziert nach
Auftragsart
Auftragsart
Kundennummer
Bestelldatum
Partition Key
Bestelldatum
Partitionierte
Tabelle
“Bestellung”
Prefixed local
Index
Gut für
Management
der Partitionen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Gut für nicht Part-Key
gesteuerte Abfragen
DATA WAREHOUSE
Globale Indexe
CREATE TABLE f_umsatz_range
(ARTIKEL_ID NUMBER(10),
KUNDEN_ID NUMBER(10),
ZEIT_ID
DATE,
REGION_ID NUMBER(10),
KANAL_ID
NUMBER(10),
UMSATZ
NUMBER(10),
MENGE
NUMBER(10),
UMSATZ_GESAMT
NUMBER(10)
)
PARTITION BY RANGE (ZEIT_ID) (
PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01','SYYYY-MM-DD')),
PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03-01','SYYYY-MM-DD')),
PARTITION mar10 VALUES LESS THAN (TO_DATE('2010-04-01','SYYYY-MM-DD')),
- - - - - - - - - - - - - - - - - - - - - - - - - PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')),
PARTITION next_month VALUES LESS THAN (MAXVALUE));
CREATE INDEX idx_UMSATZ_RANGE on f_umsatz_range (Kunden_id)
GLOBAL
PARTITION BY RANGE (Kunden_id) (
PARTITION index100 VALUES LESS THAN (100),
PARTITION index500 VALUES LESS THAN (500),
PARTITION index1000 VALUES LESS THAN (1000),
PARTITION index_MAX VALUES LESS THAN (MAXVALUE) );
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lokale Indizes
CREATE INDEX idx_Artikel_id ON
F_Umsatz_range (Artikel_id) LOCAL;
CREATE TABLE f_umsatz_range
(ARTIKEL_ID NUMBER(10),
KUNDEN_ID
NUMBER(10),
ZEIT_ID
DATE,
REGION_ID
NUMBER(10),
CREATE INDEX idx_region_id
KANAL_ID
NUMBER(10),
F_Umsatz_range (region_id)
UMSATZ
NUMBER(10),
MENGE
NUMBER(10),
UMSATZ_GESAMT
NUMBER(10)
)
PARTITION BY RANGE (ZEIT_ID) (
PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01','SYYYY-MM-DD')),
PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03-01','SYYYY-MM-DD')),
PARTITION mar10 VALUES LESS THAN (TO_DATE('2010-04-01','SYYYY-MM-DD')),
- - - - - - - - - - - - - - - - - - - - - - - - - PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')),
PARTITION next_month VALUES LESS THAN (MAXVALUE));
68
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
ON
LOCAL;
DATA WAREHOUSE
Indizes anzeigen lassen
• Welche Indexe gibt es für eine Tabelle
SELECT index_name, partitioned
FROM user_indexes
WHERE table_name = 'BESTELLUNG_RANGE';
INDEX_NAME
PAR
------------------------------ --PK_DATE_BESTELL
YES
• Auflistung von Index-Partitionen
SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value
FROM user_ind_partitions ip, user_indexes ui
WHERE ip.index_name = ui.index_name
AND ui.table_name = 'BESTELLUNG';
INDEX_NAME
-----------------------------BEST_DAT
BEST_DAT
BEST_DAT
BEST_DAT
- - - - - - - - - - - - - - - - -
69
COM
--NO
NO
NO
NO
- -
PARTITION_NAME
-----------------------------Feb07
Jan07
Mar07
Apr07
- - - - - - - - - - - - - - - - -
HIGH_VALUE
-------------------TO_DATE(' 2017-02-28
TO_DATE(' 2017-01-31
TO_DATE(' 2017-03-31
TO_DATE(' 2017-04-30
- - - - - - - -
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partielle lokale und globale Indizes
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Operationen auf lokale Indizes
• Änderungs-Operationen wie ADD, DROP, SPLIT, MERGE werden von der
Tabelle auf den Index übertragen
• Rebuild Partiton Index
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
REBUILD UNUSABLE LOCAL INDEXES;
• Rebuild Subpartiton Index
ALTER TABLE <table_name>
MODIFY SUBPARTITION <subpartition_name>
REBUILD UNUSABLE LOCAL INDEXES;
• Verschieben auf einen
anderen Table
71
ALTER INDEX <index_name>
REBUILD PARTITION <partition_name>
TABLESPACE <new_tablespace_name>;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Asynchrone Global Index Pflege
• Nach DROP oder TRUNCATE PARTITION bleibt der globale Index auch
ohne Index Pfege valide
• Betroffene Partitionen sind intern bekannt und werden während des
Zugriffs herausgefiltert
• Verzögerte Global Index Pflege
– Anstoß durch ALTER INDEX REBUILD|COALESCE
– Automatisierbar in einem Datenbank-Job
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Star Query Transformation
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
D_KUNDE
Beispiel Star Schema
D_ARTIKEL
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
ARTIKEL_ID
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
PK
D_REGION
REGION_ID
ORTNR
ORT
KREISNR
KREIS
LANDNR
LAND
REGIONNR
REGION
74
PK
F_UMSATZ
FK
ARTIKEL_ID
FK
KUNDEN_ID
ZEIT_ID
FK
FK
REGION_ID
KANAL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
PK
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
PK: Btree Index
FK: Bitmap Index
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
KUNDEN_ID
PK
KUNDENNR
GESCHLECHT
VORNAME
NACHNAME
TITEL
ANREDE
GEBDAT
BRANCHE
WOHNART
KUNDENART
BILDUNG
ANZ_KINDER
EINKOMMENSGRUPPE
ORTNR NUMBER,
BERUFSGRUPPE
STATUS
STRASSE
TELEFON
TELEFAX
KONTAKTPERSON
FIRMENRABATT
BERUFSGRUPPEN_NR
BILDUNGS_NR
EINKOMMENS_NR
WOHNART_NR
HAUSNUMMER
PLZ
ORT
KUNDENKARTE
ZAHLUNGSZIEL_TAGE
TOTAL
TOTAL_NR
DATA WAREHOUSE
Star Query Transformation
Optimierung für Joins mit großen Faktentabellen
SELECT sum(summe) FROM
F_Umsatz
1.000.000
65
12.834
U,
D_Artikel A,
D_Region R,
3.074
1.029
D_Zeit Z,
D_Kunde K
WHERE
U.FK_Kunden_ID
= K.Kunden_ID
AND
U.FK_Datum_ID
AND
U.FK_Ort_ID
= R.Ort_ID
AND
U.FK_Artikel_Nummer
= A.Nummer
= Z.Datum_ID
AND Z.JAHR_NUMMER
= 2008
AND A.GRUPPE_NR
= 3
AND K.KUNDENART
= 8
AND R.REGION_Name IN ('MITTE','SUED','NORD');
75
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
STAR_TRANSFORMATION_ENABLED=FALSE;
Abgelaufen: 00:00:03.48
-------------------------------------------------------------------------------------| Id
| Operation
| Name
Rows |Bytes |Cost (%CPU)| Time
|
---------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
1 |
50 |1057
|
1 |
SORT AGGREGATE
|
1 |
50 |
|
|
2 |
NESTED LOOPS
|
|
|
|
|
3 |
|
12 |
600 |1057
(2)| 00:00:13
|*
4 |
|
31 | 1209 |1026
(2)| 00:00:13
|*
5 |
|
121 | 3993 |1022
(2)| 00:00:13
|*
6 |
TABLE ACCESS FULL
| D_ZEIT
152 | 1216 |
7
(0)| 00:00:01
|*
7 |
HASH JOIN
|
2459 |61475 |1015
(2)| 00:00:13
|*
8 |
TABLE ACCESS FULL
| D_KUNDE
|
9 |
TABLE ACCESS FULL
| F_UMSATZ
|* 10 |
|* 11 |
|* 12 |
NESTED LOOPS
HASH JOIN
HASH JOIN
TABLE ACCESS FULL
INDEX UNIQUE SCAN
3 |
1010K|
18 |
(2)| 00:00:13
9
(0)| 00:00:01
18M|1001
(2)| 00:00:13
| D_ARTIKEL
16 |
96 |
3
(0)| 00:00:01
| PK_REGION
1 |
|
0
(0)| 00:00:01
1 |
11 |
1
(0)| 00:00:01
TABLE ACCESS BY INDEX ROWID| D_REGION
----------------------------------------------------------------------------------------
76
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
STAR_TRANSFORMATION_ENABLED=TRUE;
Abgelaufen: 00:00:00.76
-----------------------------------------------------------------------------------------------| Id | Operation
| Name
|Rows | Bytes| Cost (%CPU)| Time|
------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
19 |199(2)| 00:00:03 |
|
1 | SORT AGGREGATE
|
|
1 |
19 |
|
|
|
2 |
TABLE ACCESS BY INDEX ROWID | F_UMSATZ
| 14 | 268 |199(2)| 00:00:03 |
|
3 |
BITMAP CONVERSION TO ROWIDS|
|
|
|
|
|
|
4 |
BITMAP AND
|
|
|
|
|
|
|
5 |
BITMAP MERGE
|
|
|
|
|
|
|
6 |
BITMAP KEY ITERATION
|
|
|
|
|
|
|* 7 |
TABLE ACCESS FULL
| D_KUNDE
|
3 |
18 | 9(0)| 00:00:01 |
|* 8 |
BITMAP INDEX RANGE SCAN| IDX_FK_KUNDEN_ID_BM
|
|
|
|
|
|
9 |
BITMAP MERGE
|
|
|
|
|
|
| 10 |
BITMAP KEY ITERATION
|
|
|
|
|
|
|* 11 |
TABLE ACCESS FULL
| D_ARTIKEL
| 16 |
96 | 3(0)| 00:00:01 |
|* 12 |
BITMAP INDEX RANGE SCAN| IDX_FK_ARTIKEL_NUMMER_BM |
|
|
|
|
| 13 |
BITMAP MERGE
|
|
|
|
|
|
| 14 |
BITMAP KEY ITERATION
|
|
|
|
|
|
|* 15 |
TABLE ACCESS FULL
| D_ZEIT
| 152 | 1216 | 7(0)| 00:00:01 |
|* 16 |
BITMAP INDEX RANGE SCAN| IDX_FK_DATUM_ID_BM
|
|
|
|
|
| 17 |
BITMAP MERGE
|
|
|
|
|
|
| 18 |
BITMAP KEY ITERATION
|
|
|
|
|
|
|* 19 |
TABLE ACCESS FULL
| D_REGION
|5069 |55759 | 69(0)| 00:00:01 |
|* 20 |
BITMAP INDEX RANGE SCAN| IDX_FK_ORT_ID_BM
|
|
|
|
|
------------------------------------------------------------------------------------------------
77
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
ARTIKEL_ID
FK
KUNDEN_ID FK
ZEIT_ID
FK
REGION_ID
FK
KANAL_ID
UMSATZ
FK
MENGE
UMSATZ_GESAMT
Bitmap-Indizierung im Star Schema
F_UMSATZ
D_ARTIKEL
ARTIKEL_SPARTEN_NR
ARTIKEL_SPARTE
ARTIKEL__SEGMENT_NR
ARTIKEL__SEGMENT
ARTIKEL_GRUPPEN_NR
ARTIKEL_GRUPPE
ARTIKEL_PREIS
ARTIKEL_NAME
ARTIKEL ID PK
10
100
1.000
100.000
Star-Transformation
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
78
PK: Btree Index
FK: Bitmap Index
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Star Query Transformation
Wie funktioniert es
1.
Zugriff auf die Faktentabelle und Lookup mit
den Filterkriterien auf Dimension 1 zur
Erzeugung eines Bitmap entsprechend der
Primary Keys
2.
Wiederholen für alle Dimensionen
3.
AND-Verknüpfung der Bitmaps und Suchen
nach den Faktentabellen-Row IDs
4.
5.
Bedingungen
• STAR_TRANSFORMATION_ENABLED=TRUE
• Keine Bind Variable im SELECT Statement, kein
CONNECT BY und kein START WITH
verwenden
• Die Faktentabelle
– Muss mehr als 15000 Sätze haben (Stand 10g)
Zugriff mit gefundenen Row IDs auf die
Faktentabelle
– Kann keine View sein
Evtl. Join-back auf die Dimensionen für die
restlichen Spalten, die benötigt werden.
– Muss mehr als 2 Bitmap Indizes haben
 Es findet zu keinem Zeitpunkt ein Full Table Scan auf der
Faktentabelle statt
– Kann keine Remote-Tabelle sein
• Die Foreign Key Felder müssen als Bitmap Index
definiert sein (Faktentabelle)
• Ein Foreign Key Constraint als solches muss nicht
definiert sein
79
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
D_KUNDE
Indizierung im Star
D_ARTIKEL
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
ARTIKEL_ID
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
PK
D_REGION
REGION_ID
ORTNR
ORT
KREISNR
KREIS
LANDNR
LAND
REGIONNR
REGION
80
PK
F_UMSATZ
FK
ARTIKEL_ID
FK
KUNDEN_ID
ZEIT_ID
FK
FK
REGION_ID
KANAL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
PK
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
PK: Btree Index
FK: Bitmap Index
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
KUNDEN_ID
PK
KUNDENNR
GESCHLECHT
VORNAME
NACHNAME
TITEL
ANREDE
GEBDAT
BRANCHE
WOHNART
KUNDENART
BILDUNG
ANZ_KINDER
EINKOMMENSGRUPPE
ORTNR NUMBER,
BERUFSGRUPPE
STATUS
STRASSE
TELEFON
TELEFAX
KONTAKTPERSON
FIRMENRABATT
BERUFSGRUPPEN_NR
BILDUNGS_NR
EINKOMMENS_NR
WOHNART_NR
HAUSNUMMER
PLZ
ORT
KUNDENKARTE
ZAHLUNGSZIEL_TAGE
TOTAL
TOTAL_NR
DATA WAREHOUSE
Werden Index immer gebraucht?
• Im DWH gibt es eine grundsätzlich andere Verwendung von
Indexen
OLTP
DWH
Einzelne Selects
Selects über Datenbereiche
Einzelne Inserts
Massen-Inserts
Oft Verwaltung über Contraints (z. B.
Unique Key)
Möglichkeiten im Verlauf des ETLProzesses
alter index PK_BESTELLNR_PART_RANGE_HASH monitoring usage
SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED FROM SYS.V$OBJECT_USAGE
• => Im DWH eher Bitmap-Indexe als Btree
• Usage-Monitor zeigt, ob ein Index wirklich genutzt wurde
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Wo und wie wird im DWH indiziert
Lade-Aktivitäten
Lese-Aktivitäten
Data Integration Layer
Enterprise Information Layer
User View Layer
Process neutral / 3 NF
Keine Indexe
B*tree für Eindeutigkeit
und als Primary Key
Bitmaps
Bitmaps
B*tree für Primary Keys
In den Dimensionen
Tabellen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Query-Optimizer und System-Statistiken im
Data Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Statistiken sammeln
• Regelmäßig aktuelle Statistiken sind wichtig für gute Ausführungspläne
• Ständiges Aktualisieren belastet das System
• Best Practice im DWH
– Statistiken in Verbindung mit dem ETL-Prozesse aktualisieren.
– Nur diejenigen Tabellen, Partitionen und Indexe aktualisieren, die aktuell geladen
bzw. verändert wurden.
– => Automatisiertes Aktualisieren sollte genau überlegt werden
DBMS_STATS.GATHER_TABLE_STATS(Ownname=><OWNER>, Tabname=><TABLE_NAME>);
DBMS_STATS.GATHER_TABLE_STATS(Ownname=><OWNER>, Tabname=><TABLE_NAME>,
Partname=><PARTITION_NAME>, GRANULARITY=>'PARTITION');
DBMS_STATS.GATHER_INDEX_STATS(Ownname=><OWNER>,Indexname=><TABLE_NAME>);
84
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Sammeln von Statistiken
• Tabellen -> GATHER_TABLE_STATS
• Indexe
-> GATHER_INDEX_STATS
• Schema -> GATHER_SCHEMA_STATS
• Automatisiertes Sammeln für ein Schema
• Automatisiertes Sampling
– Parameter DBMS_STATS.AUTO_SAMPLE_SIZE
EXEC DBMS_STATS.GATHER_TABLE_STATS (
'PART','BESTELLUNG_PART_RANGE',
estimate_percent=>100);
EXEC dbms_stats.gather_schema_stats(
ownname => 'PERF',
estimate_percent => 5,block_sample => TRUE)
Begin
dbms_stats.gather_schema_stats(
ownname
=> 'PERF'
,options
=> 'GATHER AUTO'
,estimate_percent => 5
,block_sample
=> TRUE);
end;
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(
'OE',DBMS_STATS.AUTO_SAMPLE_SIZE);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Sammeln von Column-bezogenen Statistiken (Historgramme)
• Sinnvoll bei komplexen where-Bedingungen und starker UngleichVerteilung der Werte innerhalb einer Spalte
begin
DBMS_STATS.GATHER_TABLE_STATS(Ownname=>'DWH',
Tabname=>'F_UMSATZ' ,
METHOD_OPT =>
'FOR COLUMNS SIZE AUTO KUNDEN_ID,ARTIKEL_ID,ZEIT_ID, REGION_ID, KANAL_ID’);
end;
begin
DBMS_STATS.GATHER_TABLE_STATS(Ownname=>'DWH',
Tabname=>'F_UMSATZ' ,
METHOD_OPT =>
'FOR COLUMNS SIZE 20 KUNDEN_ID,ARTIKEL_ID,ZEIT_ID, REGION_ID’);
end;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Abfrage der Art Histogramme
• HEIGHT BALANCED: Aufteilung aller Werte in n-Gruppen
– Hier kann der Grenzwert der jeweiligen Grueppen abgefragt werden
• FREQUENCY: Auflistung der Menge pro vorkommenden Wert
SELECT column_name, num_distinct, num_buckets, histogram
FROM user_tab_col_statistics
WHERE table_name = 'F_UMSATZ';
COLUMN_NAME
NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- -ARTIKEL_ID
129
20 HEIGHT BALANCED
KUNDEN_ID
1031
20 HEIGHT BALANCED
ZEIT_ID
6001
20 HEIGHT BALANCED
REGION_ID
7020
20 HEIGHT BALANCED
KANAL_ID
7
7 FREQUENCY
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Histogramme machen Sinn wenn
• Histogramme machen Sinn wenn
– Wenn Spalten ungleichmäßig verteilte Werte haben und in der WHERE-Klausel
von Abfragen vorkommen
– Spalten die seltener abgefragt werden, und daher keine Indexe haben
• Histogramme nicht anlegen bei
– Gleich verteilten Spaltenwerten
– Nicht für alle Spalten einer Tabelle  zu viel Overhead
– PKs oder indizierten Spalten
– Spalten, die nicht abgefragt werden
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Dynamic
Sampling
• Zusätzliche Hilfen für
den Optimizer
– Werte von 1 – 10
(Default 2)
• Setting
– alter system set optimizer_dynamic_sampling=4;
– Manuelles Setzen ist sinnvoll wenn SQL seriell
– System wählt automatisch den Einstellwert bei Parallelisierung
• Testen
• Sinnvoll bei komplexen WHERE-Klauseln
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Inkrementelles Statistiksammeln
• Incremental Global Statistics
– Synapsis Struktur in SYSAUX Tablespace
– Sehr schnelles Erzeugen der globalen Statistiken ohne die komplette Tabelle zu
lesen
DBMS_STATS.SET_TABLE_PREFS(<OWNER>, <TABLE_NAME>, 'INCREMENTAL', TRUE);
• Inkrementelles Aktualisieren einschalten
DBMS_STATS.GATHER_TABLE_STATS(Ownname=><OWNER>, Tabname=><TABLE_NAME>,
DEGREE=><DESIRED_DEGREE>);
• Initiales einmaliges Sammeln
DBMS_STATS.GATHER_TABLE_STATS(Ownname=><OWNER>, Tabname=><TABLE_NAME>,
Partname=><SUBPARTITION_NAME>, GRANULARITY=>'SUBPARTITION', DEGREE=><DESIRED_DEGREE>);
• Inkrementelles Sammeln geschieht automatisch über
• EXEC DBMS_STATS.GATHER_TABLE_STATS(‚DWH1','UMSATZ');
90
Oracle Database Performance Tuning Guide 11g Release
2 / Chapter 13 - Managing Optimizer Statistics
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm
DATA WAREHOUSE
Anwendung im DWH bei partitionierten Tabellen
• Globale Statistiken regelmäßig
sammeln
Partition Tag 1
Partition Tag 2
– Z. B. einmal im Monat
• Einschalten des ‚Incremental‘- Modus für die
entsprechende Tabelle:
Partition Tag 3
Partition Tag 4
Partition Tag 5
Partition Tag 7
Globale
tatistiken
Partition Tag 8
ETL
91
– EXEC DBMS_STATS.SET_TABLE_PREFS(‚DWH',‘UMSATZ,
'INCREMENTAL','TRUE');
Partition Tag 9
• Nach jedem Laden einer neuen Partition, die
Statistiken aktualisieren:
Partition Tag 10
– EXEC DBMS_STATS.GATHER_TABLE_STATS('DWH','UMSATZ');
Partition Tag n
Neu hinzugefügte
Partiton verfälscht
Statistiken
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Empfehlungen
• Dynamic Sampling auf einen höheren Wert setzen (z. B. 4)
• Bei großen pratitionierten Tabellen mit
„Inkrementellem Statistik-Sammeln“ arbeiten.
• Histogramme gezielt für Spalten mit ungleich verteilten Werten
verwenden, wenn sie oft abgefragt werden.
• Große Tabellen in dem Kontext des ETL-Prozesses aktualisieren
-> ETL-Gesamt-Konzept
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Komprimierung im Data Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Bedingungen im Data Warehouse  Kompressionskonzept
• Komprimierung besonders wichtig, da große Datenmengen
• Besonders grasser Unterschied zwischen
– Daten, die häufig gelesen werden  Sehr wenige
– Daten, die selten bis kaum gelesen werden  Sehr viele
• Lese- und Schreib-Operationen sind bestimmbar
– Massen-Schreibaktionen können explizit auch zum Komprimieren genutzt
werden
– Updates kommen seltener vor
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
94
Das Datenwachstum beherrschen
Komprimieren: Verwaltung und Kosten reduzieren
Kompressions Typ:
Einsatz für:
Faktor
Basic Compression
Read only Tabellen und Partitionen in Data Warehouse
Umgebungen oder “inaktive” Daten-Partitionen in OLTP
Umgebungen.
Aktive Tabellen und Partitionen in OLTP und Data Warehouse
Umgebungen.
Non-relational Daten in OLTP und Data Warehouse Umgebungen.
2-4
Index Compression
Indizes auf Tabellen in OLTP und Data Warehouse Umgebungen.
2
Backup Compression
Alle Umgebungen.
2
Hybrid Columnar
Compression –
Data Warehousing
Read only Tabellen und Partitionen in Data Warehouse
Umgebungen.
8-12
“Inaktive” Daten Partitionen in OLTP und Data Warehousing
Umgebungen.
10-40
OLTP Compression
SecureFiles Compression
Hybrid Columnar
Compression – Archival
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
2-4
2-4
DATA WAREHOUSE
95
Anwendung für Komprimierung
• Nicht nur für
– Indizes
– Strukturierte Daten in Tabellen
(bzw. Partitionen) mit DIRECT Load
• Mit Advanced Compression auch für
–
–
–
–
Unstrukturierte Datentypen (SecureFiles)
Konventionelles DML (OLTP Compression)
DataPump Daten und RMAN
Redo Traffic mit Data Guard
Redo Logs
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Standby
DATA WAREHOUSE
Backups
96
Tabellen-Komprimierung in 11g
• Komprimierungseinstellung durch
– CREATE TABLE beim Neuanlegen
– ALTER TABLE MOVE COMPRESS bei existierenden Daten
– ALTER TABLE MOVE PARTITION COMPRESS bei Partitionen
• Beispiel – Syntax
CREATE TABLE sales_history(…) COMPRESS FOR BASIC | OLTP
• Im Enterprise Manager
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
97
Hybrid Columnar Compression (HCC)
• Neue Kombination der Anordnung nach Spalten und Zeilen
 höhere Compression Ratio möglich
• Verschiedene Level
• Designed für Daten, die nicht häufig verändert werden
• Designed für Umgebungen mit Low Concurrency
• Komprimierung nur während Bulk Loads!
• Verfügbar für Storage wie Exadata, ZFS oder Pillar
Logical Compression Unit (CU)
BLOCK HEADER
BLOCK HEADER
BLOCK HEADER
BLOCK HEADER
CU HEADER
C1
C2
C3
C4
C5
C5
C6
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
C7
C8
DATA WAREHOUSE
Compression Advisor
Welchen Komprimierungsfaktor kann ich erwarten?
• Einsatz des Package DBMS_COMPRESSION ab 11gR2
• Ohne zusätzliche Installation
• Unterstützt partitionierte/nicht partitionierte Tabellen
• Funktionen:
– Erstellt temporäre Objekte um Komprimierungsratio zu berechnen
– Analysiert Zeilen auf Komprimierungstyp
– Einsatz auch für HCC Komprimierung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Syntaxänderung in 12c
• Änderungen für Basic, OLTP und HCC Compression
• BASIC
CREATE TABLE sales_history(…)
ROW STORE COMPRESS BASIC;
• OLTP
CREATE TABLE sales_history(…)
ROW STORE COMPRESS ADVANCED;
• Beispiel für HCC
CREATE TABLE sales_history(…)
COLUMN STORE COMPRESS FOR QUERY HIGH;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Erweiterungen in 12c
• Aufhebung des 255 Spalten Limits
• DBMS_COMPRESSION Advisor jetzt auch für Securefile LOBs
begin
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
SCRATCHTBSNAME
=> 'USERS',
TABOWNER
=> 'SH',
TABNAME
=> 'BASIC_LOB',
LOBNAME
=> 'TEXT',
PARTNAME
=> '',
COMPTYPE
=> 128,
…
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Online Operationen in 12c
• Online Operationen für MOVE PARTITION
• Weniger Sperren
• Grundlage für ILM Operationen
• Beispiele
SQL> ALTER TABLE sales_big MOVE PARTITION sales_q4_2001
ROW STORE COMPRESS ADVANCED ONLINE;
SQL> ALTER TABLE sales_big MOVE PARTITION sales_q4_2001
TABLESPACE example ONLINE;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Zusammenfassung
• Anwendung der Komprimierung
– Bei Tabellen mit grösstem Speicherplatzverbrauch
• Speicherplatzeinsparung immer abhängig von
– den Daten und
– dem Ladevorgang
• Komprimierungsratio (Quotient aus unkomprimierten und komprimierten
Daten) variiert
• Bessere Ratio durch:
– Verwendung von größeren DB Blöcken
– Erhöhung der Daten-Redundanz z.B. durch Laden von sortierten Daten
• Daten werden erst in der SGA beim Zugriff entpackt -> Bessere
Ausnutzung von I/O
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Automatische Verwaltung von Daten im
Data Warehouse (Heat Map)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Automatic Data Optimization im Data Warehouse
Umsatzdaten
DISK
Sata/SAS
Juni 16
Flash / SSD
Juni 16
Mai 16
April 16
März 16
Februar 16
Januar 16
Dezember 15
In Memory
Mai 16
April 16
Eine
historisierte
Tabelle
verteilt sich
auf
März 16
Februar 16
Januar 16
Dezember 15
November 15
November 15
Oktober 15
Oktober 15
September 15
September 15
August 15
August 15
Juli15
Juli15
Juni 15
Juni 15
April 15
April 15
März 15
März 15
Februar 15
Februar 15
Automatisches
Verlagern über
die Zeit
Automatisches
Hervorholen
über die
Häufigkeit
der Verwendung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
105
Automatisiertes Information Life Cycle Management (ILM)
• Regelgesteuertes Verfahren zur physikalischen Datenverwaltung
• Automatisierte Heatmaps sammelt
– Änderungs- und Lesevorgänge
– Auf Block- und Segmentlevel
• Automatic Data Optimization (ADO)
– Bewegt und komprimiert auf der Basis von Regeln
– Steuerung entweder über Heatmap oder selbsterstellte Prozeduren
• Ausnutzen von Partitioning und Komprimierung
• Steuern über
SQL> ALTER SYSTEM SET heat_map = 'ON';
SQL> ALTER SESSION SET heat_map = 'ON';
SQL> ALTER SYSTEM SET heat_map = 'OFF';
SQL> ALTER SESSION SET heat_map = 'OFF';
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
106
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
107
Automatisches Komprimieren wenn x-Tage nicht genutzt
• Steuerung auf Segment und Block-Ebene
(Segmente: Tabellen, Partitionen, Materialized Views, Indexe)
• Automatisiertes Steuern von Komprimieren
– über die Heatmaps und zeitliche Verläufe
– über Füllgrade von Tablespaces
• Automatic Data Optimization (ADO)
– Bewegt und komprimiert auf der Basis von Regeln
– Steuerung entweder über Heatmap oder
selbsterstellte Prozeduren
ALTER TABLE orders ILM ADD POLICY
ROW STORE COMPRESS
ADVANCED SEGMENT
AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE orders ILM ADD POLICY
COLUMN STORE COMPRESS
FOR QUERY HIGH SEGMENT
AFTER 90 DAYS OF NO MODIFICATION;
• Ausnutzen von Partitioning und Komprimierung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Oracle Confidential
–
108
Automatisches Verschieben wenn Füllgrad erreicht
BEGIN
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED, 85):
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE, 25):
END;
Juni 16
?
Mai 16
April 16
März 16
Februar 16
Januar 16
ALTER TABLE orders ILM ADD POLICY
COLUMN STORE COMPRESS
FOR QUERY HIGH SEGMENT
AFTER 30 DAYS OF NO MODIFICATION;
Tablespace
(datafile)
COLD
Tablespace
(datafile)
NORMAL
Wenn Tablespace
NORMAL zu 85% gefüllt,
dann automatisches
Verschieben der
„kältesten“ Partition
nach COLD
ALTER TABLE orders ILM ADD POLICY
COLUMN STORE COMPRESS
FOR ARCHIVE HIGH SEGMENT
AFTER 90 DAYS OF NO MODIFICATION;
ALTER TABLE orders ILM ADD POLICY
tier to low_cost_store;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
109
Automatic Data Optimization und In-Memory
• Zeitabhängiges Automatisches Komprimieren
von Im-Memory-Tabellen
• Zeitabhängiges Entfernen von Tabellen aus dem
In-Memory-Column-Store
• Steuern der In-Memory-Fähigkeit
über eine programmierte
Funktion
ALTER TABLE sales ilm ADD policy
MODIFY INMEMORY memcompress
FOR query high AFTER 3 days OF
No modification
ALTER TABLE sales ilm ADD policy
NO INMEMORY SEGMENT
AFTER 30 days OF no ACCESS;
CREATE OR REPLACE FUNCTION custom_im_ado (objn IN NUMBER)
RETURN BOOLEAN ;
ALTER TABLE sales ilm ADD policy
NO INMEMORY SEGMENT ON
custom_im_ado;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
110
Heat Map für Tabellen und Partitionen
“Row” Level Tracking
Auf Block-Ebene
Abfrage über DBMS_HEAT_MAP
“Segment” Level Tracking
Auf Tabellen-Ebene.
Abfrage über
DBA_HEAT_MAP_SEG_HISTOGRAM
TABLESPACE
FNO
BLOCK_ID WRITETIME
---------- ---------- ---------- --------------USERS
6
347 25.06.2013 14:45
USERS
6
348 25.06.2013 14:45
USERS
6
349 25.06.2013 14:45
USERS
6
350 25.06.2013 14:45
USERS
6
351 25.06.2013 14:45
…
OWNER
---------SH
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
OBJECT_NAME
----------------------CUSTOMERS_PK
DEPT
EMP
EMP
EMP
PK_EMP
PK_EMP
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
TRACK_TIME
---------------25.06.2013 22:48
25.06.2013 12:48
26.06.2013 12:30
25.06.2013 12:48
24.06.2013 11:47
26.06.2013 22:30
25.06.2013 22:48
WRI
--NO
NO
YES
NO
NO
NO
NO
FUL
--NO
YES
YES
YES
YES
NO
NO
DATA WAREHOUSE
LOO
-YES
NO
NO
NO
NO
YES
YES
111
Parallelisierung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
112
Parallelisierung
• Verteilung von Lese-/Schreiboperationen auf mehrere Prozesse:
Parallel 2 -> i. d. R. Halbierung der Wartezeit…
Parallel 4 -> i. d. R. Drittelung der Wartezeit…
• Limitierende Faktoren
– IO-Leistung des Plattensystems
– Anzahl Cores in den CPUs
– Größe des Hauptspeichers
– Anzahl Benutzer an dem System
• Automatisierte Steuerung der Parallelisierung durch das System
• In-Memory nutzt die Parallelisierung besonders gut (keine IO-Limitierung)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA
DATAWAREHOUSE
WAREHOUSE
Parallele Ausführung
• Ressourcen
– Parallel Execution Query Coordinator (QC)
– Parallel Execution Server Pool (PS)
– Messages
• Einteilung der Arbeitsmenge in kleinere Einheiten (Granules)
• Partitionen von Tabellen oder Indizes können diese Granules bilden
• Erfahrungswerte:
– Datenobjekte < 200 MB sollten keine Parallelisierung nutzen
– Objekte < 200 MB > 5 GB = Parallelisierungsgrad (DOP) 4
– Objekte > 5 GB = DOP 32
 Angaben variieren nach Systemauslastung und Hardware Konfiguration
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
114
SQL Parallel Execution Plan
SELECT c.cust_name, s.purchase_date, s.amount
FROM sales s, customers c
WHERE s.cust_id = c.cust_id;
Query Coordinator
ID
Operation
0
SELECT STATEMENT
1
PX COORDINATOR
2
PX SEND QC {RANDOM}
3
4
Name
HASH JOIN
PX RECEIVE
TQ
IN-OUT
Q1,01
P->S
Q1,01
PCWP
Q1,01
PCWP
5
PX SEND BROADCAST
Q1,01
P->P
6
PX BLOCK ITERATOR
Q1,01
PCWP
Q1,01
PCWP
Q1,01
PCWP
Q1,01
PCWP
7
8
9
TABLE ACCESS FULL
CUSTOMERS
PX BLOCK ITERATOR
TABLE ACCESS FULL
SALES
PQ Distribution
BROADCAST
Parallel Server leisten die Hauptarbeit
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
115
Execution Plan
ID
Operation
0
SELECT STATEMENT
1
PX COORDINATOR
2
PX SEND QC (RANDOM)
“Partition Hash All” über dem Join &
Einfaches PQ Set bezeichnet Partition-wise Join
Name
Pstart
Pstop
:TQ10001
TQ
PQ Distrib
Q1,01
QC (RAND)
3
SORT GROUP BY
Q1,01
4
PX RECEIVE
Q1,01
5
PX SEND HASH
6
SORT GROUP BY
7
8
9
10
:TQ10000
Q1,00
HASH
Q1,00
PX PARTITION HASH ALL
1
128
HASH JOIN
Q1,00
Q1,00
TABLE ACCESS FULL
Customers
1
128
Q1,00
TABLE ACCESS FULL
Sales
1
128
Q1,00
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
116
Parallelisierung und Skalierung
• Parallelisierbare Operationen
serial
• Abfragen
–
–
–
–
100%
– CREATE TABLE/MV
– CREATE INDEX
– Online Index Rebuild
I/O
CPU
I/O
SQL
SELECT
Join Operationen
Sort Operationen
GROUP BY
• DDL
CPU
50%
parallel
100%
SQL
50%
• DML
– INSERT
– UPDATE / DELETE
– MOVE / SPLIT PARTITION
Ein SQL Statement wird vom Optimizer in kleinere
Arbeitsschritte aufgeteilt und läuft skalierbar ab
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
117
Steuerung der Parallelität mit “Degree of Parallelism” (DOP)
• Automatic Degree of Parallelism
– PARALLEL_DEGREE_POLICY = AUTO
• Degree of Parallelism manuell festlegen
– ALTER TABLE sales PARALLEL 8;
– ALTER TABLE customers PARALLEL 4;
• Default Parallelism
– ALTER TABLE sales PARALLEL;
SI : DOP =
PARALLEL_THREADS_PER_CPU x CPU_COUNT
RAC: DOP =
PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
119
Parallel Degree Policy
Ausführungsplan
---------------------------------------------------------Plan hash value: 4226669230
----------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
7 |
371 | 81975
(9)| 00:00:03 |
|
1 | VIEW
|
|
7 |
371 | 81975
(9)| 00:00:03 |
|
2 |
WINDOW SORT
|
|
7 |
196 | 81975
(9)| 00:00:03 |
|
3 |
HASH GROUP BY
|
|
7 |
196 | 81975
(9)| 00:00:03 |
|* 4 |
HASH JOIN
|
|
51M| 1367M| 76249
(2)| 00:00:03 |
|
5 |
TABLE ACCESS FULL| D_ARTIKEL |
129 | 2709 |
3
(0)| 00:00:01 |
|
6 |
TABLE ACCESS FULL| F_UMSATZ |
51M|
341M| 75998
(2)| 00:00:03 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------4 - access("U"."ARTIKEL_ID"="A"."ARTIKEL_ID")
Note
----- automatic DOP: Computed Degree of Parallelism is 1
Note
----- automatic DOP: skipped because of IO calibrate statistics are missing
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
120
Wie geht das System bei der Festlegung des automatischen
Parallelisierungsgrad vor
SQL
Statement
Statement wird geparsed
Optimizer ermittelt den
Execution Plan
Geschätzte Ausführung
dauert länger als
Schwellwert
Optimizer bestimmt den
idealen DOP
PARALLEL_MIN_TIME_THRESHOLD
(default =10s)
Tatsächlicher DOP = MIN(PARALLEL_DEGREE_LIMIT, idealer DOP)
Geschätzte
Ausführung dauert
nicht länger als
Schwellwert
Statement wird
seriell ausgeführt
PARALLEL_DEGREE_LIMIT
(default =CPU)
Statement wird parallel
ausgeführt
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
121
Wie funktioniert das “Parallel Statement Queuing”
SQL
Statements
Wenn zu wenig Parallel
Server vorhanden sind,
landet das
Statement wird geparsed
Oracle ermittelt automatisch
den DOP
64
32
64
16
32
128
16
FIFO Queue
Wenn wieder genügend
Parallel Server vorhanden
sind, wird erste Statement
aus der Queue geholt und
ausgeführt
Wenn genügend Parallel Server
vorhanden sind, wird das
Statement sofort ausgeführt
8
128
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
122
Parameter für Parallel Query
• Oracle V11.2.0.1
• Oracle V12.1.0.1
• Parameter
• Neue Parameter
– parallel_degree_limit = 'CPU'
– parallel_degree_policy = MANUAL
– parallel_force_local = FALSE
– parallel_min_time_threshold = AUTO
– parallel_servers_target = 8
(CPU|IO|integer)
(MANUAL|
LIMITED|AUTO)
(FALSE|TRUE)
(AUTO|integer)
(0 - max_servers)
• Parameter
–
–
–
–
–
–
–
parallel_adaptive_multi_user = TRUE
parallel_execution_message_size = 2148
parallel_instance_group = ' '
parallel_max_servers = 20
parallel_min_percent = 0
parallel_min_servers = 0
parallel_threads_per_cpu = 2
(TRUE|FALSE)
(2148 – 65535)
()
(0 - 3600) pro Instanz
(1 - 100) %
(0 - max_servers)
(1 - 4|8) pro core
• Veraltete Parameter
– parallel_automatic_tuning = FALSE
– parallel_io_cap_enabled = FALSE
– parallel_degree_limit = 'CPU‚
– parallel_degree_policy = MANUAL
–
–
–
–
–
–
–
–
–
–
parallel_force_local = FALSE
parallel_min_time_threshold = AUTO
parallel_servers_target = 8
parallel_adaptive_multi_user = TRUE
parallel_execution_message_size = 2148
parallel_instance_group = ' '
parallel_max_servers = 20
parallel_min_percent = 0
parallel_min_servers = 0
parallel_threads_per_cpu = 2
(CPU|IO|integer)
(MANUAL|LIMITED|
AUTO|ADAPTIVE)
(FALSE|TRUE)
(AUTO|integer)
(0 - max_servers)
(TRUE|FALSE)
(2148 – 65535)
()
(0 - 3600) pro Instanz
(1 - 100) %
0 - max_servers)
(1 - 4|8) pro core
• Veraltete Parameter
– parallel_automatic_tuning = FALSE
– parallel_io_cap_enabled = FALSE
(FALSE|TRUE)
(FALSE|TRUE)
(FALSE|TRUE)
(FALSE|TRUE)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
123
Query Result Cache
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
124
Konzept und Einsatz des Result Cache
• Eigener Cache im Shared Pool
• Keine Installation notwendig
• Automatischer Refresh bei Datenänderungen
• Einfaches Setup und Monitoring der Cache-Nutzung
• Der Query Result Cache ist anwendbar für
– SQL-Abfragen
– PL/SQL-Funktionen
125
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Implementierung und Nutzung
• Anwendung steuerbar über Initialisierungsparameter
RESULT_CACHE_MODE
• Falls RESULT_CACHE_MODE=MANUAL gesetzt ist, dann einen Hint im
Statement einfügen wie z.B.
SELECT /*+ result_cache */ count(*) FROM sales
• Falls RESULT_CACHE_MODE=FORCE gesetzt ist, dann erfolgt ein
automatisches Einfügen des Hints im Root-SELECT
SELECT count(*) FROM sales
126
...
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Parameter zum Result Cache
RESULT_CACHE_MAX_RESULT
RESULT_CACHE_MAX_SIZE
RESULT_CACHE_MODE
RESULT_CACHE_REMOTE_EXPIRATION
5 (%)
abhängig vom OS
MANUAL/FORCE
0 (min)
• RESULT_CACHE_MAX_SIZE: Gesamtgröße des reservierten Bereichs für den Result Cache im Shared Pool
• RESULT_CACHE_MAX_RESULT: Prozentualer Anteil am gesamten Result Cache für die einzelnen
Ergebnisse
• RESULT_CACHE_REMOTE_EXPIRATION: Zeitdauer bei Remote Objekt-Nutzung, wie lange das Resultat in
Minuten im Cache verbleibt
127
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beispiel mit Hints
SQL> SELECT /*+ result_cache */ COUNT(*), SUM(salary)
FROM hr.bigemp group by department_id
ORDER BY department_id;
...
------------------------------------------------------------------------------------------------| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
| 91myw5c1bud0mcn64g3d0ykdhm |
|
2 |
|
3 |
RESULT CACHE
SORT GROUP BY
|
TABLE ACCESS FULL| BIGEMP
|
11 |
55 |
2229
(2)| 00:00:34
|
|
|
11 |
55 |
2229
(2)| 00:00:34
|
|
876K|
4280K|
2201
(1)| 00:00:34
Statistics
----------------------------------------------------------------------------0
recursive calls
0
db block gets
0
consistent gets
0
physical reads
0
redo size
696
bytes sent via SQL*Net to client
419
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
12
128
rows processed
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
V$RESULT_CACHE_OBJECTS
SQL> SELECT name, type, row_count, invalidations, scan_count
FROM v$result_cache_objects;
NAME
TYPE
ROW_COUNT INVALIDATIONS SCAN_COUNT
-------------------- ---------- ---------- ------------- ---------HR.GET_DATUM
Dependency
0
0
0
SCOTT.EMP
Dependency
0
0
0
HR.BIGEMP
Dependency
0
1
0
1
0
1
12
0
4
"HR"."GET_DATUM"::8. Result
"GET_DATUM"#27dda668
fe0cf492 #1
SELECT /*+ result_ca Result
che */ COUNT(*),
SUM(salary)
FROM hr.bigemp group
by department_id OR
DER BY department
129
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Flexibilisierung und Effizienz
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
130
Wo Flexibilität fehlt: Fälle aus der alltäglichen Praxis
• Schichtenmodell ist Blaupause für
Verantwortlichkeiten -> Hoheitsgebiete
Integration
Layer
– Teure Abstimmprozesse und Formalismus
– Fehlende Spontanität für pragmatische Lösungen
– Zu starr und langsam bei der Umsetzung neuer
Informations-Bedarfe
Enterprise Layer
Core - DWH / Info Pool
DWHAdmin
User View
Layer
User View
Layer
User View
Layer
Fach
Anwender
• Fehlendes zentrale Schicht
– Viele Redundanzen, wenig Wiederverwendung
– fehlende Synchronisation bei zusammenhängenden
Themen -> nicht stimmige Daten
– Fehlende sachgebietsübergreifende
Breite beim Informationsangebot
– Daten müssen immer wieder aus den Vorsystemen
geholt werden -> Zeitaufwand
Integration
Warehouse
DWHAdmin
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
User View
Layer
?
User View
Layer
User View
Layer
Fach
Anwender
DATA WAREHOUSE
Möglichkeiten / Maßnahmen zur Flexibilisierung
• Architekturen
– Virtualisierung der Data Marts
– Fließende Übergänge zwischen Enterprise- und User View-Layer
– Wegfall von separaten BI-Schichten, außerhalb des Data Warehouse
– Standardisierte Kennzahlen-Festlegung bereits im Enterprise-Layer
• Organisatorisch
– Zugriff von Endbenutzern auf zentrale Schicht
– Regelmäßige Informationsbedarfserhebung und Abstimmung zwischen IT und
Benutzergruppen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
132
Analysen vorwegnehmen / Kennzahlen standardisieren
• 50 – 80% aller Benutzerabfragen, Analysen, Kennzahlen sind vorhersehbar
• Vorhersehbare Informationsbedarfe sollten mittels eines Konzeptes
vorbereitet werden
• Dazu sind keine weiteren Tools nötig
• Hilfsmittel
– Analytische Funktionen
– Pattern-Analyse
– Kennzahlensysteme / Materialized Views
– R-Analysen / Mining-Modelle
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Oracle Confidential
–
133
Betriebliche Kennzahlen...
• ...messen betriebliche Vorgänge
• ...beurteilen Sachverhalte
• ...reduzieren Komplexität durch kurze
und prägnante Darstellung
• ...können Ziele festlegen
Kennzahlen verdichten Informationen.
Sie machen Sachverhalte sichtbar, die
In den üblichen Betriebsdaten
schwer erkennbar sind
• ...legen kritische Erfolgsfaktoren fest
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
134
Konzepte rund um Kennzahlen
• Abstimmung mit Fachanwendern
• Publizierbare Definition
• Metadatenartige Beschreibung von Kennzahlen
• Im Schichten Kennzahlen so früh wie möglich berechnen
• Keine Abhängigkeiten von flüchtigen Strukturen schaffen
– Z. B Auswertemodelle, die einem permanenten Wechsel unterliegen
• Auf Standardisierung achten
• Auf Wiederverwendung achten
• Kennzahlen in granularisierbare Teilinformationen zergliedern
– Granulare Teile hierarchisieren
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beschreibungsmerkmale „Kennzahl“
• Name
• Synonym
• Messwertvariante
– Absolute Größe
– Referenz -> KENNZAHL
– Verhältniswert
– Referenz -> GLOSSAR
– Gliederungszahl (Bsp. Eigenkapital ist Teil von Gesamtkapital)
– Beziehungszahl (Verhältnis von 2 Werten untereinander)
– Indexzahl (Gleichartige Größen in zeitlich oder räumlich
getrennten Bereichen. Z. B. Veränderung im Vergleich zum Vorjahr)
• Bereich: Standardwerte z. B.
- Finanzielle Ergebnisse
- Qualität von etwas (Produkt/Service)
- Ablauf, Fortschritt von etwas (Prozess)
- Leistungsfähigkeit von etwas
• Geschäftsprozess
– Richtwert / Orientierungsgröße
•
Quelldaten (Text, Ursprung der Daten)
•
Verwendete Größen
- Referenz -> PROZESS
•
•
•
•
•
•
•
Definition (Text)
Beschreibung (Text)
Ziel / Zweck (Text)
Zielgruppe [Abteilung, MA, Rolle]
Aktualisierungsfrequenz (Zeitangabe)
Maßeinheit [%, Wertgröße, Zeit]
Rechen-/Herleitungsregel
Referenz -> COLUMN/ATTRIBUT
•
Regel / Referenz -> RULE
•
Org-Level [Top, Mittel, operativ]
•
Zeitmaß [Jahr , Quartal, Monat, Woche, Tag]
• Historie [Vergangenheit, Aktuell, Zukunft]
• Hierarchieposition
- Referenz SUB_von -> KENNZAHL
- Referenz TOP_von -> KENNZAHL
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
136
Wo wird „vorbereitet“
Service
Integration
Layer
Servicekunde
Logistik
Logistikaufwand
Profitabilität
Produkte&Trends
Vertrieb
Kundenhistorie
Marketing
Marketingsicht
R S S S
B
T
T
B
Einkauf
Core - DWH / Info Pool
R
T
Controlling
Enterprise Layer
B
B
B
D
B
Strategische
Daten
• Kennzahlen so
früh wie möglich
User View
Layer
D
F
D
D
F
D
D
D
F
D
Taktische
Daten
Kennzahlen
Vorberechnungen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
• Keine Abhängigkeiten von
flüchtigen
Strukturen
schaffen
• Auf
Standardisierung
achten
• Auf Wiederverwendung
achten
DATA WAREHOUSE
Dimensions-Level bereits im Kernmodell vorsehen
Konzeptionelles
Sicht
Produktart
Alle Produkte
Alle Produkte
Liefereinheit
Liefereinheit
Verladeeinheit
Artikelsparten
Verladeeinheit
Vermittlungsart
Segment
Produktart
Artikelgruppen
Artikelsparten
Gebinde
Vermittlungsart
Artikel
Segment
Modellsicht Enterprise Layer
Artikelgruppen
Gebinde
Materialized
View
Create Materialized View …. AS SELECT
a.artikel_name Artikel,
sum(u.umsatz) umsatz_pro_Artikel,
FROM f_Umsatz_2014 U, D_artikel a
WHERE U.artikel_id = a.artikel_id
group by a.artikel_name
Artikel
Umsatz
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Der Anwender fragt
immer anders, als man
denkt.
Macht aber nichts.
Die Wünsche werden
trotzdem erfüllt.
Select
sum(UMSATZ) ,
Produktart
from
F_UMSATZ, D_Artikel
Group by
Produktart;
DATA WAREHOUSE
138
Mv_EA_Finanz_Kum_Gruppe_Monat
Level 4
Produktgruppen-Sicht
Hierarchisierung
von Kenzahlen
Finanz-Sicht / Berechnungen
Level 3
LFD_Bestands_Wert / Produkt / Monat
LFD_Saldo / Produkt / Monat
Kumulierter EK / Produkt
Kumulierter VK / Produkt
Kumuliertes Saldo
Mv_EA_Finanz_Kum_Monat
Jahres-Sicht
Bestands-/Lager-Sicht / Berechnungen
Mav_Einkauf_Verkauf_Diff_Jahr
LFD_Bestands_Menge / Produkt / Monat
VK_Menge / Produkt / Monat
EK_Menge / Produkt / Monat
Kumulierte EK Menge / Produkt
Kumulierte VK Menge / Produkt
Mv_EA_Menge_Kum_Monat
Level 2
Mav_Produkt_Monat_einkaeufe
F_EINKAEUFE
Level 1
Mav_Produkt_Monat_Verkaeufe
F_POSITION
F_KAUF
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
EA: Einkauf/Verkauf
Kum: kumuliert
DATA WAREHOUSE
Der Weg in die BI-Tools
So viel wie möglich in der DB
vorbereiten
•
Wiederverwenden von
bereits berechneten
Kennzahlen
•
Verhindert unnötiges
Kopieren
•
Keine Verlagerung von
Pseudo-ETL in die BI-Tools
•
Standardisierte Kennzahlen
•
Abfragen werden gruppiert
User View Layer
So
Millionen von Sätzen
User View Layer
Oder so
BI-Tool Server + Caches
•
WenigeSätze
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Materialized Views und Kennzahlenkonzepte
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
141
Materialized Views optimieren Zugriffe
D_REGION
(Dimension)
F_UMSATZ
(Fakten)
KANAL_ID
FK
KUNDEN_ID FK
ZEIT_ID
FK
REGION_ID
FK
ARTIKEL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑹𝒆𝒈𝒊𝒐𝒏
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑳𝒂𝒏𝒅
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑲𝒓𝒆𝒊𝒔
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑶𝒓𝒕
REGIONNR
REGION
LANDNR
LAND
KREISNR
KREIS
ORTNR
ORT
REGION_ID
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑹𝒆𝒈𝒊𝒐𝒏 𝒑𝒓𝒐 𝑨𝒓𝒕𝒊𝒌𝒆𝒍
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑳𝒂𝒏𝒅 𝒑𝒓𝒐 𝑨𝒓𝒕𝒊𝒌𝒆𝒍
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑹𝒆𝒈𝒊𝒐𝒏 𝒑𝒓𝒐 𝑮𝒓𝒖𝒑𝒑𝒆
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑲𝒓𝒆𝒊𝒔 𝒑𝒓𝒐 𝑨𝒓𝒕𝒊𝒌𝒆𝒍
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑳𝒂𝒏𝒅 𝒑𝒓𝒐 Gruppe
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐
𝑶𝒓𝒕 𝒑𝒓𝒐
𝑼𝒎𝒔𝒂𝒕𝒛
𝒑𝒓𝒐𝑨𝒓𝒕𝒊𝒌𝒆𝒍
𝑹𝒆𝒈𝒊𝒐𝒏 𝒑𝒓𝒐 𝑺𝒑𝒂𝒓𝒕𝒆
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑲𝒓𝒆𝒊𝒔 𝒑𝒓𝒐 𝑮𝒓𝒖𝒑𝒑𝒆
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑳𝒂𝒏𝒅 𝒑𝒓𝒐 Sparte
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑶𝒓𝒕 𝒑𝒓𝒐 𝑮𝒓𝒖𝒑𝒑𝒆
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑲𝒓𝒆𝒊𝒔 𝒑𝒓𝒐 𝑺𝒑𝒂𝒓𝒕𝒆
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑶𝒓𝒕 𝒑𝒓𝒐 𝑺𝒑𝒂𝒓𝒕𝒆
MAV
D_ARTIKEL
(Dimension)
ARTIKEL_ID
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
MAV
MAV
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑨𝒓𝒕𝒊𝒌𝒆𝒍
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑮𝒓𝒖𝒑𝒑𝒆
𝑼𝒎𝒔𝒂𝒕𝒛 𝒑𝒓𝒐 𝑺𝒑𝒂𝒓𝒕𝒆
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Zugriffspfade planen
• Abdecken von allen benötigten Join-Optionen
• Immer nur zwischen den untersten Leveln
einer Dimension und der Fakten-Tabellen
MAV
MAV
MAV
D
D
MAV
MAV
MAV
F
MAV
D
MAV
MAV
MAV
Sprechende Name
143
MAV
D
MAV
Übersichtliche Darstellung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Prinzip und Aufgabenstellung - Summentabellen
Basistabelle
Summentabelle
Complete Refresh
Incremental Refresh
?
Änderungen
144
stale
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Aufgaben der Materialized Views (MAVs)
• Erleichtern das Management von Summentabellen
– Wegfall von Erstellungsprozeduren
– Einfache Steuerung des Zeitpunktes zur Aktualisierung
– Eventuell Beschleunigung der Aktualisierung
(inkrementelles Refresh)
• Abfrage-Performance optimieren
• Variable Kennzahlensysteme aufbauen
– Mehrstufige MAVs
• Abfragegruppen zusammenfassen (Kategorisierung)
– Geschäftsobjekt-bezogene MAVs
145
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
MAV-Erstellung und erstmaliges Füllen
• Parameter des create Befehles I/II:
• BUILD IMMEDIATE (direkt bei der Erstellung, default)
– Problematisch bei großen Basistabellen und im Rahmen von Entwicklung / Test
• BUILD DEFERRED (Erstellung beim ersten Refresh)
– Sinnvoll bei erster Überführung neuer MAV-Definitionen in die
Produktionsumgebung
• ON PREBUILD
– Sinnvoll, wenn es separate Erstellungroutinen gibt, die ihr Ergebnis nur in einer
Tabelle ablegen können, man aber die Rewrite-Vorteile der MAVs nutzen will
– Kopie von normalen Views (analog zum vorigen Punkt)
146
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Refresh-Funktionen
• Parameter des create Befehles II/II:
• Refresh wie:
• COMPLETE
– Immer vollständiges Neuladen aus den
Basistabellen
• FAST (inkrementell)
– Nur bei vorhandenem MAV Log auf der
Basistabelle
• FORCE (inkrementell oder komplett,
default)
• NEVER
– Vorhalten historischer Bestände
oder bei separater Prozedur
• Refresh wann :
• ON COMMIT (oft bei OLTP)
– Commit einer Transaktion auf der
Basistabelle
• ON DEMAND (sinnvoll im DWH,
default)
– Je nach der zu erwartenden Refresh-Dauer
147
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Star Schema zum Testen
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
D_REGION
REGION_ID
ORT_ID
ORT_NAME
KREIS_ID
KREIS_NUMMER
KREIS_NAME
LAND_NAME
LAND_ID
LAND_NUMMER
REGION_NAME
REGION_NUMMER
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
D_KUNDE
KUNDEN_ID
VORNAME
NACHNAME
GEBDAT
BRANCHE
WOHNART
KUNDENART
BILDUNG
EINKOMMENSGRUPPE
ORTNR
BERUFSGRUPPE
STATUS
BERUFSGRUPPEN_NR
BILDUNGS_NR
EINKOMMENS_NR
WOHNART_NR
PLZ
ORT
DATA WAREHOUSE
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
148
Beispiel einer Materialized View
CREATE MATERIALIZED VIEW MV_Standard
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer
Jahr,
z.monat_desc
Monat,
sum(u.umsatz)
Summe,
a.artikel_id
ID,
count(u.umsatz)
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc, a.artikel_id;
149
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Data Dictionary Views für MAVs
• Weitreichende Informationen über Zustand der MAVs und ihrer
dazugehörigen Basistabellen
– ALL_MVIEWS
– DBA_MVIEWS
– USER_MVIEWS
– USER_MVIEW_DETAIL_RELATIONS
– USER_MVIEW_DETAIL_SUBPARTITION
• Mit 11g wurde der Detailgrad in diesen Views erhöht, vor allem bei
partitionierten Tabellen (Staleness etc.)
150
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Testen und Ablaufbedingungen für MAV
set autotrace on;
-- Anzeige des Ausführungsplans
show parameter query
query_rewrite_enabled
query_rewrite_integrity
TRUE
STALE_TOLERATED
-----
erlaubt das Query Rewrite
erlaubt Query Rewrite, auch wenn
die Daten in der Basistabelle
nicht mehr aktuell sind
query_rewrite_integrity
TRUSTED
-----
auch deklarierte
Basis-Informantionen gelten
als korrekt (z. B. Views oder
prebuild)
query_rewrite_integrity
ENFORCED
-- Daten müssen stimmen
-- Ändern der Parameter mit
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET query_rewrite_enabled=FALSE;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Automatische Aktualisierung DBMS_MVIEW (Refresh-Funktionen)
Aufeinander
Aufbauende
M-Views
• Refresh-Funktionen
– DBMS_MVIEW.REFRESH()
Umsatz Prod. Gr und Jahr
– DBMS_MVIEW.REFRESH_ALL_MVIEW()
SUM/Jahr
U Prod.A
U Prod B
SUM/Monat
JOIN
BasisTabellen
– DBMS_MVIEW.REFRESH_DEPENDENT()
D_Zeit
FAKT
D_PROD
• Refresh-Methoden (optional)
• COMPLETE (C)
• FAST
(F)
• FORCE (default)
(?)
• PARTITIONED(P)
• Transaktionsverhalten (optional)
• ATOMIC_REFRESH
• REFRESH_AFTER_ERRORS
• NESTED
Bsp.: EXECUTE DBMS_MVIEW.REFRESH('MV_STANDARD‘,'C');
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Automatische Aktualisierung DBMS_MVIEW (Refresh-Funktionen)
• ATOMIC_REFRESH => TRUE | FALSE
– Refresh vollzieht sich in einer Transaktion
– Im Fehlerfall wird die Transaktion zurückgerollt
• REFRESH_AFTER_ERRORS => TRUE | FALSE
– Refresh von mehreren Materialized Views läuft weiter bzw. bricht ab, wenn bei einer MAV ein
Fehler aufgetreten ist
• NESTED
– Eine Materialized View und alle von ihr abhängigen MAVs werden aktualisiert
EXECUTE DBMS_MVIEW.REFRESH('MV_STANDARD‘,'C');
EXECUTE DBMS_MVIEW.REFRESH('MV_STANDARD',atomic_refresh=>TRUE);
EXECUTE DBMS_MVIEW.REFRESH('MV_STANDARD',atomic_refresh=>TRUE,
nested => TRUE);
EXECUTE DBMS_MVIEW.REFRESH('MV_STANDARD',nested=>TRUE);
153
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Out-of-place Refresh
• Refresh wird in separater Tabelle / Partition durchgeführt und nach Abschluss
„geswitcht“
• Bessere Online-Verfügbarkeit
• Höhere Refresh-Performance
• Ist für alle Refresh-Varianten möglich
•
•
•
•
COMPLETE
FAST
FORCE (default)
PARTITIONED
(C)
(F)
(?)
(P)
DBMS_MVIEW.REFRESH(‘MV_MONATS_KALULATION', method => '?',
atomic_refresh => FALSE, out_of_place => TRUE);
154
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Fast Refresh-Varianten
Aktualisierung über MAV Logs
MAV1
MAV2
Aktualisierung über Partition Change Tracking
(PCT)
MAV3
komplett
Partition 1
MAV1
Partition 2
MAV2
Partition 3
Partition 4
inkrementell
Partition 5
Partition 6
MAV Log
155
Basistabelle
Basistabelle
• WITH ROWID
• Join Dependency Expression
• SEQUENCE
• Partition Key
• INCLUDING NEW VALUES
• Partition Marker
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Fast Refresh mit MAV Log
--- MAV Log auf Tabelle D_Artikel
DROP MATERIALIZED VIEW LOG ON d_artikel;
CREATE MATERIALIZED VIEW LOG ON d_artikel
WITH ROWID, SEQUENCE
(dimension_key, nummer, artikel_name, artikel_id, gruppe_nr, gruppe_name,
sparte_name, sparte_nr)
INCLUDING NEW VALUES;
--- MAV Log auf Tabelle D_Zeit
DROP MATERIALIZED VIEW LOG ON d_zeit;
CREATE MATERIALIZED VIEW LOG ON d_zeit
WITH ROWID, SEQUENCE
(datum_id, datum_desc, tag_des_monats, tag_des_jahres, woche_des_jahres, monats_nummer,
monat_desc, quartals_nummer, jahr_nummer)
INCLUDING NEW VALUES;
--- MAV Log auf Tabelle F_Umsatz
DROP MATERIALIZED VIEW LOG ON f_umsatz;
CREATE MATERIALIZED VIEW LOG ON f_umsatz
WITH ROWID, SEQUENCE
(umsatz, menge, umsatz_nach_rabatt, rabatt_wert_firmenkunde,
Rabatt_wert_privatkunde, bestell_datum, artikel_id, kunde_id, region_id, zeit_id)
INCLUDING NEW VALUES;
156
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Fast Refresh mit MAV Log
CREATE MATERIALIZED VIEW MV_Standard_Fast_Refresh
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr, z.monat_desc Monat, sum(u.umsatz) Summe,
a.artikel_id ID, count(u.umsatz)
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
SQL> SELECT mview_name, update_log, stale_since, staleness
GROUP BY
2 FROM user_mviews where mview_name = 'MV_STANDARD_FAST_REFRESH';
z.jahr_nummer,
MVIEW_NAME
UPDATE_LOG
STALE_SI
STALENESS
z.monat_desc,
------------------------------ ------------------------------ ------------a.artikel_id;
-MV_STANDARD_FAST_REFRESH
FRESH
157
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
PCT Refresh mit Partition Key
• Der Partitioning Key der Basistabelle ist in dem SELECT- und eventuell in dem
GROUP BY-Teil, aber nicht in der WHERE-Klausel der MAV enthalten
• Führt zur Aggregierung
auf der Ebene des
Partitioning Keys
• Höhere Datenmenge als
ohne PCT
SQL> SELECT count(*)
2 FROM MV_Standard_PCT_Richtig;
COUNT(*)
---------41492
158
CREATE MATERIALIZED VIEW
MV_Standard_PCT_Richtig
AS SELECT
u.bestell_datum,
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz_Par u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
u.bestell_datum, z.jahr_nummer,
z.monat_desc, a.artikel_id;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
PCT Refresh Join Dependency Expression
• Partitioning Key kommt in der Join Condition vor und eine Spalte der
Basistabelle im SELECT-Teil der MAV
CREATE MATERIALIZED VIEW
MV_Standard_PCT_Falsch
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz_Par u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id and
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id;
159
CREATE MATERIALIZED VIEW
MV_Standard_PCT_Richtig
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz_Par u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id and
u.bestell_datum = z.datum_desc
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
PCT Refresh mit Partition Marker
• Die Partitionmarker-Funktion liefert pro Partition einen Wert, der beliebige Level für
die Aggregationen in der MAV erlaubt
SQL> SELECT count(*) FROM
MV_Standard_PCT_Par_MARKER;
COUNT(*)
---------2869
160
CREATE MATERIALIZED VIEW MV_Standard_PCT_Par_MARKER
AS SELECT
dbms_mview.pmarker(u.rowid) AS pmark,
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz_Par u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id and
u.bestell_datum = z.datum_desc
GROUP BY
z.jahr_nummer,z.monat_desc,a.artikel_id,
dbms_mview.pmarker(u.rowid) ;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
PCT Refresh und MAV Log Refresh
• Fast Refresh über MAV Logs funktioniert nicht, wenn Partitionen
hinzukommen oder gelöscht werden
• Für bestimmte MAVs funktioniert das Log-Verfahren nicht, z.B.
Verwendung der RANK-Funktion
• PCT Refresh ist schneller, wenn viele Änderungen pro Partition gemacht
wurden
• PCT Refresh lässt sich parallelisieren
• MAVs können bei PCT trotz “Staleness” mancher Partionen für Query
Rewrite genutzt werden
161
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Refresh Performance Tipps
• Optimizer-Statistiken immer aktuell halten
– Nach jedem Laden aktualisieren
– DBMS_STATS auch über MAVs laufen lassen
• ATOMIC_REFRESH auf FALSE setzen
• PCT nutzen, wo es geht
– Auch die MAV kann partitioniert werden (bringt zusätzliche Performance beim Refresh)
• Parallele Ausführung verwenden
• Mit REFRESH_ALL arbeiten
– DB organisiert sich die Abarbeitung selbst und parallelisiert automatisch bei Bedarf
– Berücksichtigt auch Abhängigkeiten zwischen einzelnen MAVs
162
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Nutzenpotenziale durch MAV heben
• Wartbarkeit und Wiederverwendbarkeit
• Konzept erstellen als Grundlage für MAVs
• Komplexität reduzierne der einzelnen SQL
• Keine MAV für einzelne Abfrage
• Einfache Pflege
• Aufeinander Aufbauende MAV
• Einsatz der Automatismen prüfen (refresh)
• Verwendung der MAV mit System Views prüfen
• Performance im ETL
• Trennung von unterschiedlichen Aufgaben (Join, Aggregation)
• Abhängige Elemente nutzen
• Effiziente Nutzung
• Unterstützende Elemente nutzen (Dimensionen)
• Partitionierung prüfen
163
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Nested Materialized Views
Umsatz aggregiert auf Jahreslevel
Umsatz aggregiert auf Monatslevel
sum / count
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
164
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA WAREHOUSE
Nested Materialized Views
CREATE MATERIALIZED VIEW MV_Umsatz_Monat
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,a.artikel_id;
165
CREATE MATERIALIZED VIEW MV_Umsatz_Jahr
ENABLE QUERY REWRITE
AS SELECT
Jahr,
sum(summe) Summe,
ID artikel_id,
count(summe)
FROM
MV_Umsatz_Monat
GROUP BY
jahr,ID;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Minimierung von Komplexität mit Nested Materialized Views
“Liefere die Artikel mit dem größten Umsatz im November 2006”
CREATE MATERIALIZED VIEW MV_Umsatz_Komplex
ENABLE QUERY REWRITE
AS SELECT
a.artikel_name, sum(u.umsatz)
FROM
f_umsatz u, d_artikel a
WHERE
a.artikel_id = u.artikel_id
GROUP BY
a.artikel_name
HAVING sum(u.umsatz)
--> kein Fast Refresh möglich
IN (SELECT max(u.umsatz)
FROM f_umsatz u, d_zeit z
WHERE u.zeit_id = z.datum_id AND
z.monat_desc = 'November' AND
z.jahr_nummer = '2006'
GROUP BY u.artikel_id);
166
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Minimierung von Komplexität mit Nested Materialized Views
“Liefere die Artikel mit dem größten Umsatz im November 2006”
CREATE MATERIALIZED VIEW MV_Umsatz_KOMBINATION
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT artikel, umsatz_summe FROM MV_Umsatz_Komplex_SUB1
WHERE umsatz_summe IN (SELECT umsatz_max FROM MV_Umsatz_Komplex_SUB2);
CREATE MATERIALIZED VIEW
MV_Umsatz_Komplex_SUB1
ENABLE QUERY REWRITE
AS
SELECT a.artikel_name Artikel,
sum(u.umsatz)umsatz_summe,
count(u.umsatz), count(*)
FROM f_umsatz u, d_artikel a
WHERE a.artikel_id = u.artikel_id
GROUP BY a.artikel_name;
167
CREATE MATERIALIZED VIEW
MV_Umsatz_Komplex_SUB2
ENABLE QUERY REWRITE
AS
SELECT u.artikel_id,
max(u.umsatz) umsatz_max
FROM f_umsatz u, d_zeit z
WHERE u.zeit_id = z.datum_id AND
z.monat_desc = 'November' AND
z.jahr_nummer = '2006'
GROUP BY u.artikel_id;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Automatisches “Query Rewrite” mit
Materialized Views
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
168
Rewrite Prüfung – Abarbeitungsreihenfolge
• Textvergleich der SELECT-Liste
– Reihenfolge spielt dabei keine Rolle
– Auflösung von möglichen Berechnungen
• Vergleich der Join-Bedingung
• Vergleich der GROUP BY-Klausel
169
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beispiel-MAV für die folgenden Abfragen
CREATE MATERIALIZED VIEW MV_UMS_ART_Zeit
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz)Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc, a.artikel_id;
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
170
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA
DATAWAREHOUSE
WAREHOUSE
Exaktes Text-Matching
• Umstellen der Spalten und avg() anstelle von sum()
SELECT
z.jahr_nummer Jahr,
sum(u.umsatz)Summe,
a.artikel_id ID,
z.monat_desc Monat
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id;
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
sum / count
SELECT
z.jahr_nummer Jahr,
avg(u.umsatz) Schnitt,
a.artikel_id ID,
z.monat_desc Monat
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id;
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA WAREHOUSE
Prinzip Aggregate Rollup
SELECT
z.jahr_nummer Jahr, --> Bezugsgröße in MAV ist Monat
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, a.artikel_id;
• Abfragen lässt sich alles, was in der GROUP BY-Klausel der MAV zu finden ist
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
172
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA WAREHOUSE
Join Back-Methode
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
a.artikel_name Artikel,
sum(u.umsatz) Summe
Ist nicht in der
MAV-Definition
enthalten
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc, a.artikel_Name;
• Join Back-Tabelle muss einen Primary Key nutzen
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
173
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA WAREHOUSE
Ausführungsplan Join Back-Methode
Ohne Join Back
---------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
| 3511 |
246K|
172
(5)| 00:00:03 |
|
1 | HASH GROUP BY
|
| 3511 |
246K|
172
(5)| 00:00:03 |
|* 2 |
HASH JOIN
|
|
100K| 7031K|
168
(3)| 00:00:03 |
|
3 |
TABLE ACCESS FULL | D_ZEIT
| 3074 | 55332 |
8
(0)| 00:00:01 |
|* 4 |
HASH JOIN
|
|
100K| 5273K|
159
(2)| 00:00:02 |
|
5 |
TABLE ACCESS FULL| D_ARTIKEL |
65 | 2860 |
3
(0)| 00:00:01 |
|
6 |
TABLE ACCESS FULL| F_UMSATZ |
100K|
976K|
155
(2)| 00:00:02 |
----------------------------------------------------------------------------------
Mit Join Back
-------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
65 | 5785 |
15 (14)| 00:00:01 |
|
1 | HASH GROUP BY
|
|
65 | 5785 |
15 (14)| 00:00:01 |
|* 2 |
HASH JOIN
|
| 6363 |
553K|
14
(8)| 00:00:01 |
|
3 |
TABLE ACCESS FULL
| D_ARTIKEL
|
65 | 2860 |
3
(0)| 00:00:01 |
|
4 |
MAT_VIEW REWRITE ACCESS FULL| MV_UMS_ART_ZEIT | 6363 |
279K|
10
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
174
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Einfache Join-Bedingungen in MAVs Grundlage für
flexiblere Abfragen
Einfache Join-Bedingung
CREATE MATERIALIZED VIEW MV_UMS_ART_Zeit_Join
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
a.artikel_id ID,
u.umsatz Umsatz
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id;
175
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id AND
z.jahr_nummer = '2017'
GROUP BY
z.jahr_nummer, z.monat_desc;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Materialized Views und Hierarchisierung von
Dimensionen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
176
Dimensionale Tabelle als Optimizer-Hilfe
• Abfragen über alle Spalten der Dimensionstabelle
• Eine Definition für Dimensionen festlegen
CREATE DIMENSION d_artikel
LEVEL artikel IS d_artikel.artikel_id
LEVEL gruppe IS d_artikel.gruppe_nr
LEVEL sparte IS d_artikel.sparte_nr
HIERARCHY h_art
(artikel CHILD OF gruppe CHILD OF
sparte)
ATTRIBUTE att_artikel LEVEL artikel
DETERMINES d_artikel.artikel_name
ATTRIBUTE att_gruppe LEVEL gruppe
DETERMINES d_artikel.gruppe_name
ATTRIBUTE att_sparte LEVEL sparte
DETERMINES d_artikel.sparte_name;
CREATE TABLE d_artikel
(
dimension_key NUMBER(3) NOT NULL,
nummer
NUMBER(8),
artikel_name VARCHAR2(50),
artikel_nummer NUMBER(3),
gruppe_nr
NUMBER(3),
gruppe_name
VARCHAR2(50),
sparte_name
VARCHAR2(50),
sparte_nr
NUMBER(3));
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
177
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA WAREHOUSE
Dimensionale Tabelle als Optimizer-Hilfe
Abfrage auf Spartenebene
Definition auf Artikelebene
CREATE MATERIALIZED VIEW MV_UMS_ART_Dim
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT
a.artikel_id ID,
sum(u.umsatz) Umsatz
FROM
f_umsatz u, d_artikel a
WHERE
a.artikel_id = u.artikel_id
GROUP BY
a.artikel_id;
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
178
sum / count
SELECT
a.sparte_name Sparte,
sum(u.umsatz) Summe
FROM
f_umsatz u, d_artikel a
WHERE
a.artikel_id = u.artikel_id
GROUP BY
a.sparte_name;
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
DATA WAREHOUSE
Dimensionale Tabelle als Optimizer-Hilfe
Ohne Rewrite auf Basis einer dimensionalen Tabelle
--------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
28 |
163
(5)| 00:00:02 |
|
1 | HASH GROUP BY
|
|
1 |
28 |
163
(5)| 00:00:02 |
|* 2 |
HASH JOIN
|
|
100K| 2734K|
158
(2)| 00:00:02 |
|
3 |
TABLE ACCESS FULL| D_ARTIKEL |
65 | 1430 |
3
(0)| 00:00:01 |
|
4 |
TABLE ACCESS FULL| F_UMSATZ |
100K|
585K|
154
(1)| 00:00:02 |
---------------------------------------------------------------------------------
Mit Rewrite auf Basis einer dimensionalen Tabelle
-------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
48 |
7 (29)| 00:00:01 |
|
1 | HASH GROUP BY
|
|
1 |
48 |
7 (29)| 00:00:01 |
|
2 |
MERGE JOIN
|
|
63 | 3024 |
6 (17)| 00:00:01 |
|
3 |
TABLE ACCESS BY INDEX ROWID | D_ARTIKEL
|
65 | 1430 |
2
(0)| 00:00:01 |
|
4 |
INDEX FULL SCAN
| PK_ART_ID
|
65 |
|
1
(0)| 00:00:01 |
|* 5 |
SORT JOIN
|
|
63 | 1638 |
4 (25)| 00:00:01 |
|
6 |
MAT_VIEW REWRITE ACCESS FULL| MV_UMS_ART_DIM |
63 | 1638 |
3
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
179
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Prüfen der Stimmigkeit einer Dimension
ATTRIBUTE-Klausel
HIERARCHY-Klausel
SPARTE_NAME
SPARTE_NR
funktionale
Abhängigkeit
GRUPPE_NAME
GRUPPE_NR
funktionale
Abhängigkeit
ARTIKEL_NAME
ARTIKEL_ID
funktionale
Abhängigkeit
1 : n-Beziehung
1 : n-Beziehung
180
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Prüfen der Stimmigkeit einer Dimension
\ora-home\RDBMS\ADMIN\utldim.sql
SQL> desc dimension_exceptions
Name
------------------------------STATEMENT_ID
OWNER
TABLE_NAME
DIMENSION_NAME
RELATIONSHIP
BAD_ROWID
 Legt Tabelle DIMENSION_EXCEPTIONS an
DBMS_OLAP.VALIDATE_DIMENSION
-- Prüfen der Dimension mit:
variable stmt_id varchar2(30);
execute :stmt_id := 'CUST_DIM_VAL';
execute dbms_dimension.validate_dimension ('MAV.D_ARTIKEL',FALSE,TRUE,:stmt_id );
-- Fehlermeldungen abfragen mit:
SELECT distinct owner, table_name, dimension_name, relationship
FROM dimension_exceptions
WHERE statement_id = :stmt_id;
181
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
EXPLAIN_MVIEW – Auswertung
start D:\O11\db11\RDBMS\ADMIN\utlxmv.sql
SQL> desc MV_CAPABILITIES_TABLE;
Name
Null?
----------------------------------------- -------STATEMENT_ID
MVOWNER
MVNAME
CAPABILITY_NAME
POSSIBLE
RELATED_TEXT
RELATED_NUM
MSGNO
MSGTXT
SEQ
182
Typ
--------------------VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
CHAR(1)
VARCHAR2(2000)
NUMBER
NUMBER(38)
VARCHAR2(2000)
NUMBER
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
EXPLAIN_MVIEW-Routine
• Zeigt auf, welche Funktionen für die jeweilige MAV genutzt werden
kann
EXECUTE dbms_mview.explain_mview(_
'SELECT sum(u.umsatz),a.artikel_name _
FROM f_umsatz u, d_artikel a _
WHERE a.artikel_id = u.artikel_id _
GROUP BY a.artikel_name');
183
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
EXPLAIN_MVIEW-Routine
SELECT capability_name, possible p, substr(related_text,1,20) obj, substr(msgtxt,1,100)
erklaerung FROM mv_capabilities_table;
CAPABILITY_NAME
-----------------------------PCT
REFRESH_COMPLETE
REFRESH_FAST
REWRITE
PCT_TABLE
PCT_TABLE
REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ONETAB_DML
P
N
Y
N
Y
N
N
N
N
N
N
N
CAPABILITY_NAME
-----------------------------REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ANY_DML
REFRESH_FAST_PCT
P OBJ
ERKLAERUNG
- --------------------------------------------------------------------N
SUM(expr) ohne COUNT(expr)
N
Siehe Grund, warum REFRESH_FAST_AFTER_ONETAB_DML deaktiviert ist
N
PCT bei keiner der Detail-Tabellen in der Materialized
View m÷glich
Y
Y
Y
N
Allgemeines Neuschreiben nicht m÷glich oder PCT bei keiner
der Detail-Tabellen m÷glich
N F_UMSATZ
Relation ist keine partitionierte Tabelle
N D_ARTIKEL
Relation ist keine partitionierte Tabelle
REWRITE_FULL_TEXT_MATCH
REWRITE_PARTIAL_TEXT_MATCH
REWRITE_GENERAL
REWRITE_PCT
PCT_TABLE_REWRITE
PCT_TABLE_REWRITE
184
OBJ
ERKLAERUNG
------------------------------------------------------------------------
F_UMSATZ
D_ARTIKEL
MAV.F_UMSATZ
MAV.D_ARTIKEL
SUM(U.UMSATZ)
Relation ist keine partitionierte Tabelle
Relation ist keine partitionierte Tabelle
Detail-Tabelle enthΣlt kein Materialized View-Log
Detail-Tabelle enthΣlt kein Materialized View-Log
SUM(expr) ohne COUNT(expr)
Siehe Grund, warum REFRESH_FAST_AFTER_INSERT deaktiviert ist
COUNT(*) ist in SELECT-Liste nicht vorhanden
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
EXPLAIN_REWRITE-Routine
Angabe der Bedingungen für Query Rewrite
@D:\app\aths\product\11.1.0\db_1\RDBMS\ADMIN\utlxrw.sql
SQL> desc rewrite_table
Name
Null?
----------------------------------------- -------STATEMENT_ID
MV_OWNER
MV_NAME
SEQUENCE
QUERY
QUERY_BLOCK_NO
REWRITTEN_TXT
MESSAGE
PASS
MV_IN_MSG
MEASURE_IN_MSG
JOIN_BACK_TBL
JOIN_BACK_COL
ORIGINAL_COST
REWRITTEN_COST
FLAGS
RESERVED1
RESERVED2
185
Typ
--------------VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
NUMBER(38)
VARCHAR2(4000)
NUMBER(38)
VARCHAR2(4000)
VARCHAR2(512)
VARCHAR2(3)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(4000)
VARCHAR2(4000)
NUMBER(38)
NUMBER(38)
NUMBER(38)
NUMBER(38)
VARCHAR2(10)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
EXPLAIN_REWRITE-Routine
MAV-Definition
CREATE MATERIALIZED VIEW MV_UMS_ART_Zeit_Join
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
a.artikel_id ID,
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id;
select mv_name, message from rewrite_table;
MV_UMS_ART_ZEIT_JOIN
QSM-01150: Abfrage wurde nicht umgeschrieben
DBMS_MVIEW.EXPLAIN_REWRITE
begin
dbms_mview.explain_rewrite('
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id', 'MV_UMS_ART_Zeit_Join');
end;
MV_UMS_ART_ZEIT_JOIN
QSM-01082: Materialized View, MV_UMS_ART_ZEIT_JOIN, kann nicht mit Tabelle, F_UMSATZ,
verknpft werden
MV_UMS_ART_ZEIT_JOIN
QSM-01102: Materialized View, MV_UMS_ART_ZEIT_JOIN, erfordert Join zurck zu Tabelle, F_UMSATZ,
in Spalte, UMSATZ
186
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Analytische SQL-Funktionen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
187
Analytische Funktionen
• Einsatz bei dem Aufbau von festen, bekannten Kennzahlen
• Ideal in dem Zusammenspiel mit Materialized Views
• Lösung könnte auch mit reinem SQL erfolgen
aber
– Analytische Funktionen machen die Abfrage schlanker
– Sie sind in der Regel schneller, weil man Mehrfachlesen von Tabellen verhindert
– Sie liefern mehr Flexibilität weil mit Gruppierungen gezielter umgegangen werden kann
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Gruppierungen auf unterschiedlichen Leveln
Over Partition By
• Allgemeines Format
Function(arg1,..., argn) OVER ( [PARTITION BY <...>]
[ORDER BY <....>] [<window_clause>] )
• Beispiel
Beispiel:
sum(wert) over (partition by Artikelgruppe) Gruppengesamtwert
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Einfaches Beispiel
Mit nur einer Tabelle
select
Artikelname,
Artikelgruppe,
Wert,
sum(wert) over (partition by Artikelgruppe) Gruppengesamtwert
from Artikel ;
Beispieltabelle:
create table Artikel (
Artikelname
Artikelgruppe
wert
varchar2(10),
varchar2(10),
number);
insert into Artikel values('Schraube','Beschlag',1);
insert into Artikel values('Winkel','Beschlag',2);
insert into Artikel values('Mutter','Beschlag',1);
insert into Artikel values('Kabel','Elektro',6);
insert into Artikel values('Lampe','Elektro',5);
insert into Artikel values('Klemme','Elektro',2);
....................
ARTIKELNAM
---------Schraube
Winkel
Mutter
Winkel
Schraube
Mutter
Winkel
Schraube
Mutter
Kabel
Kabel
Lampe
Klemme
Klemme
Lampe
Kabel
Lampe
Klemme
ARTIKELGRU WERT GRUPPENGESAMTWERT
---------- ---- ----------------Beschlag
2
12
Beschlag
2
12
Beschlag
1
12
Beschlag
2
12
Beschlag
1
12
Beschlag
1
12
Beschlag
2
12
Beschlag
1
12
Beschlag
1
12
Elektro
6
39
Elektro
6
39
Elektro
5
39
Elektro
2
39
Elektro
2
39
Elektro
5
39
Elektro
6
39
Elektro
5
39
Elektro
2
39
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Abgrenzung gegenüber GROUP BY / Aggregationen
• Bei der GROUP BY – Lösung müssen aller Felder in dem SELECT-Teil auch unter
GROUP BY aufgelistet werden
SQL> select sum(wert),
artikelname,
artikelgruppe
from
artikel
group by
artikelname;
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Verhindern von Mehrfachlesen
Mit analytischen Funktionen
select Artikelname,
Artikelgruppe,
Wert,
sum(wert) over (partition by Artikelname) Artikelgesamtwert,
sum(wert) over (partition by Artikelgruppe) Gruppengesamtwert,
round(((sum(wert) over (partition by Artikelname))/(sum(wert) over (partition by
Artikelgruppe))*100),0) Prozent from Artikel ;
Ohne analytische Funktionen
select
wert_art,
art.artikelname,
wert_gr,
art.artikelgruppe,
round((art.wert_art/gr.wert_gr*100),0) Prozent
from
(select sum(wert) wert_art, artikelname,ARTIKELGRUPPE from artikel group by
artikelname,ARTIKELGRUPPE) art,
(select sum(wert) wert_gr, ARTIKELGRUPPE from artikel group by ARTIKELGRUPPE) gr
where
art.ARTIKELGRUPPE = gr.ARTIKELGRUPPE
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Mit analytischen Funktionen
Ohne analytische Funktionen
ARTIKELNAM
---------Kabel
Kabel
Kabel
Klemme
Klemme
Klemme
Lampe
Lampe
Lampe
Mutter
Mutter
Mutter
Schraube
Schraube
Schraube
Winkel
Winkel
Winkel
ARTIKELNAM
---------Kabel
Kabel
Kabel
Klemme
Klemme
Klemme
Lampe
Lampe
Lampe
Mutter
Mutter
Mutter
Schraube
Schraube
Schraube
Winkel
Winkel
Winkel
ARTIKELGRU
WERT ARTIKELGESAMTWERT GRUPPENGESAMTWERT
PROZENT
---------- ---------- ----------------- ----------------- ---------Elektro
6
18
39
46
Elektro
6
18
39
46
Elektro
6
18
39
46
Elektro
2
6
39
15
Elektro
2
6
39
15
Elektro
2
6
39
15
Elektro
5
15
39
38
Elektro
5
15
39
38
Elektro
5
15
39
38
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
2
6
12
50
Beschlag
2
6
12
50
Beschlag
2
6
12
50
ARTIKELGRU
WERT ARTIKELGESAMTWERT GRUPPENGESAMTWERT
PROZENT
---------- ---------- ----------------- ----------------- ---------Elektro
6
18
39
46
Elektro
6
18
39
46
Elektro
6
18
39
46
Elektro
2
6
39
15
Elektro
2
6
39
15
Elektro
2
6
39
15
Elektro
5
15
39
38
Elektro
5
15
39
38
Elektro
5
15
39
38
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
1
3
12
25
Beschlag
2
6
12
50
Beschlag
2
6
12
50
Beschlag
2
6
12
50
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Verhindern von Doppellesen
Mit analytischen Funktionen
------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
18 |
342 |
5 (40)| 00:00:01 |
|
1 | WINDOW SORT
|
|
18 |
342 |
5 (40)| 00:00:01 |
|
2 |
WINDOW SORT
|
|
18 |
342 |
5 (40)| 00:00:01 |
|
3 |
TABLE ACCESS FULL| ARTIKEL |
18 |
342 |
3
(0)| 00:00:01 |
-------------------------------------------------------------------------------
Ohne analytische Funktionen
-------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
9 |
423 |
9 (34)| 00:00:01 |
|* 1 | HASH JOIN
|
|
9 |
423 |
9 (34)| 00:00:01 |
|
2 |
VIEW
|
|
2 |
40 |
4 (25)| 00:00:01 |
|
3 |
HASH GROUP BY
|
|
2 |
24 |
4 (25)| 00:00:01 |
|
4 |
TABLE ACCESS FULL| ARTIKEL |
18 |
216 |
3
(0)| 00:00:01 |
|
5 |
VIEW
|
|
9 |
243 |
4 (25)| 00:00:01 |
|
6 |
HASH GROUP BY
|
|
9 |
171 |
4 (25)| 00:00:01 |
|
7 |
TABLE ACCESS FULL| ARTIKEL |
18 |
342 |
3
(0)| 00:00:01 |
-------------------------------------------------------------------------------Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beispiel
Mit mehreren Tabellen
D_ARTIKEL
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
ARTIKEL_ID
PK
Wieviel Prozent machen der
Gesamtumsatzes pro Artikel an dem Gesamtumsatz der zugehörigen
Gruppe aus?
F_UMSATZ
FK
ARTIKEL_ID
FK
KUNDEN_ID
ZEIT_ID
FK
FK
REGION_ID
KANAL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
Gesamtumsatz Einzelartikel im Vergleich zu
durchschnittlichem Artikelumsatz pro Gruppe
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lösung 1 – ohne analytische Funktion
select Artikel, Artikel_Gesamt, Gruppe_Gesamt,
round((Artikel_Gesamt/Gruppe_Gesamt*100),0) Prozent from
(
with artikel_summe AS
(SELECT a.artikel_name, sum(u.umsatz) Wert_p_Artikel
FROM
f_Umsatz_2014 U,
D_artikel a
WHERE
U.artikel_id
= a.artikel_id
group by a.artikel_name)
SELECT distinct a.artikel_name Artikel,
s.Wert_p_Artikel Artikel_Gesamt,
sum(u.umsatz) over (partition by a.GRUPPE_NAME) Gruppe_Gesamt
FROM
f_Umsatz_2014 U,
D_artikel a,
artikel_summe s
WHERE
U.artikel_id
= a.artikel_id and
a.ARTIKEL_NAME = s.ARTIKEL_NAME);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Antwortzeit:
129 rows selected.
Elapsed: 00:01:07.29
DATA WAREHOUSE
Lösung 2 – mit sum() OVER (PARTITION...)
select artikel,
umsatz_pro_Artikel,
gruppe,
sum(umsatz_pro_Artikel) over (partition by Gruppe) Gruppe_Gesamt,
round(umsatz_pro_Artikel/(sum(umsatz_pro_Artikel) over (partition by Gruppe))*100,0)
Prozent
from
(
SELECT a.artikel_name Artikel,
a.GRUPPE_NAME Gruppe,
sum(u.umsatz) umsatz_pro_Artikel
FROM
f_Umsatz_2014 U,
D_artikel a
WHERE
U.artikel_id
= a.artikel_id
group by a.artikel_name, a.GRUPPE_NAME
)
Sub-Select liefert nach Gruppe
gruppierte Artikel-Aggregationen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Antwortzeit:
129 rows selected.
Elapsed: 00:00:03.49
DATA WAREHOUSE
Lösung 3 – kompakte Form
SELECT
a.artikel_name Artikel,
a.GRUPPE_NAME Gruppe,
sum(u.umsatz) umsatz_pro_Artikel,
sum(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME) Gesamt_Gruppen_Umsatz,
round(((sum(u.umsatz))/(sum(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME))*100),2) Prozent
FROM f_Umsatz_2014 U, D_artikel a
WHERE U.artikel_id
= a.artikel_id
group by a.GRUPPE_NAME, a.artikel_name
Liefert nach Gruppe
gruppierte Artikel-Aggregationen
Antwortzeit:
129 rows selected.
Elapsed: 00:00:03.83
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lösung 3 – ausführliches Beispiel
select * from
(
Gesamtsumme
Gesamtsumme
SELECT
pro Artikel_Gruppe
pro
Artikel
a.artikel_name Artikel,
Aufsteigende Artikelsummenposition
a.GRUPPE_NAME Gruppe,
pro Artikel_Gruppe
sum(u.umsatz) umsatz_pro_Artikel,
Anzahl Artikelsummen pro Gruppe
sum(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME) Gesamt,
Durchschnittliche
rank() over (PARTITION BY a.GRUPPE_NAME order by a.artikel_name ) lfd_nr,
Anzahlsumme
count(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME) Anz_Art_pro_Gruppe,
innerhalb einer
round(avg(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME),2) Schnitt_pro_Gruppe,
Artikelgruppe
sum(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME ORDER BY a.artikel_name) Kumuliert,
round(((sum(u.umsatz))/(sum(sum(u.umsatz)) OVER (PARTITION BY a.GRUPPE_NAME))*100),2) Prozent
Kumulierte
FROM f_Umsatz_2014 U, D_artikel a
Artikelsummen
WHERE U.artikel_id
= a.artikel_id
pro Gruppe
Prozentanteil
group by a.GRUPPE_NAME, a.artikel_name
Liefert nach Gruppe
)
Artikelsumme an
gruppierte Artikel-Aggregationen
where lfd_nr < 4
Gesamtgruppenwert
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Analog: Vergleiche mit Durchschnittsmengen
Gesamtumsatz Einzelartikel im Vergleich zu
select artikel,
durchschnittlichem Artikelumsatz pro Gruppe
umsatz_pro_Artikel,
gruppe,
avg(umsatz_pro_Artikel) over (partition by Gruppe) Gruppe_Gesamt,
round(umsatz_pro_Artikel/(avg(umsatz_pro_Artikel) over (partition by Gruppe))*100,0) Prozent
from
(
SELECT a.artikel_name Artikel,
a.GRUPPE_NAME Gruppe,
sum(u.umsatz) umsatz_pro_Artikel
FROM
f_Umsatz U,
D_artikel a
WHERE
U.artikel_id
= a.artikel_id
group by a.artikel_name, a.GRUPPE_NAME
)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Year-To-Date Analysen
select
- Auflistung von Umsatz pro Monat und Jahr
z.Jahr_nummer Jahr,
- Kumulierung der Monatsumsätze von Jahresbeginn an
z.MONAT_DESC Monat,
sum(u.umsatz) Umsatz,
sum(sum(u.umsatz)) over (PARTITION by z.Jahr_nummer order by z.MONATS_NUMMER) year_to_date
from
D_ZEIT
F_UMSATZ
f_umsatz_2014 u,
DATUM_ID
ARTIKEL_ID
d_zeit z
TAG_DES_MONATS
KUNDEN_ID
TAG_DES_JAHRES
ZEIT_ID
where
WOCHE_DES_JAHRES
REGION_ID
z.zeit_id = u.zeit_id
MONATS_NUMMER
KANAL_ID
MONAT_DESC
UMSATZ
group by
QUARTALS_NUMMER
MENGE
z.Jahr_nummer,
JAHR_NUMMER
UMSATZ_GESAMT
ZEIT_ID
z.MONATS_NUMMER,
z.MONAT_DESC
------------------------------------------------------------------------------|Id | Operation
| Name
|Rows | Bytes | Cost (%CPU)| Time
|
/
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
| 1153 | 42661 |
190
(4)| 00:00:03 |
| 1 | WINDOW BUFFER
|
| 1153 | 42661 |
190
(4)| 00:00:03 |
| 2 |
SORT GROUP BY
|
| 1153 | 42661 |
190
(4)| 00:00:03 |
|* 3 |
HASH JOIN
|
|97384 | 3518K|
186
(2)| 00:00:03 |
| 4 |
TABLE ACCESS FULL| D_ZEIT | 5844 |
142K|
13
(0)| 00:00:01 |
| 5 |
TABLE ACCESS FULL| F_UMSATZ| 100K| 1171K|
171
(1)| 00:00:03 |
-------------------------------------------------------------------------------Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Rank() Over Partition
Ranking / Reihenfolgen
Was ist das umsatzstärkste Quartal pro Jahr
select * from
(select sum(u.umsatz) Umsatz,
z.Jahr_nummer Jahr
,z.Quartals_nummer "Top-Quartal",
RANK() OVER (PARTITION by z.Jahr_nummer ORDER BY sum(U.umsatz) DESC ) AS Rangfolge
D_ZEIT
from
DATUM_ID
f_umsatz u,
TAG_DES_MONATS
d_zeit z
TAG_DES_JAHRES
WOCHE_DES_JAHRES
where
MONATS_NUMMER
F_UMSATZ
z.zeit_id = u.zeit_id
MONAT_DESC
QUARTALS_NUMMER
ARTIKEL_ID
-- and
JAHR_NUMMER
KUNDEN_ID
-- z.Jahr_nummer between 2006 and 2011
ZEIT_ID
ZEIT_ID
REGION_ID
group by z.Jahr_nummer,z.Quartals_nummer)
KANAL_ID
where Rangfolge = 1
UMSATZ
MENGE
order by Jahr;
UMSATZ_GESAMT
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Ranking ohne Partition
Die 10 umsatzstärksten Artikel
SELECT *
FROM
(SELECT
D_ARTIKEL
Artikel_Name as Artikel,
ARTIKEL_NAME
sum(U.umsatz) AS Umsatz,
GRUPPE_NR
RANK() OVER (ORDER BY sum(U.umsatz) DESC ) AS Rangfolge
GRUPPE_NAME
SPARTE_NAME
from
SPARTE_NR
F_umsatz U,
ARTIKEL_ID
PK
D_Artikel A
WHERE U.artikel_id = a.artikel_id
F_UMSATZ
group by a.artikel_name)
Rangfolge-Feld
ARTIKEL_ID
WHERE rownum < 11;
wird mitgeliefert
ARTIKEL
UMSATZ RANGFOLGE
--------------------------------------- ---------Wandspiegel 50x60
1723790
1
Kehrschaufel
1703263
2
Waschbecken 40x60
1697755
3
Badewannenfaltwand 50x50x50
1694115
4
Duschbecken 70
1687618
5
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
FK
FK
KUNDEN_ID
ZEIT_ID
FK
FK
REGION_ID
KANAL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
DATA WAREHOUSE
Alternative Version für Ranking ohne analytische Funktion
select * from
(SELECT *
FROM
(SELECT
Artikel_Name as Artikel,
sum(U.umsatz) AS Umsatz
from
F_umsatz U,
D_Artikel A
WHERE U.artikel_id = a.artikel_id
group by a.artikel_name)
order by Umsatz desc)
where rownum < 11 ;
2 geschachtelte Sub-Selects
weil die Klausel
„where rownum < 11“
erst nach der
Klausel
„ORDER BY UMSATZ“
wirken soll.
Es fehlt das Rangfolgenfeld.
Das müßte man jetzt zusätzlich noch
Konstruieren.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Ranking
Die 5 umsatzstärksten Bundesländer
• RANK
– Plätze werden aufsteigend vergeben
– Bei 2 gleichen Position bleibt der darauf folgende frei
• 1 2 3 3 5 6 7 8 8 10
• DENSRANK
– Plätze werden aufsteigend vergeben
– Bei 2 gleichen Position wird die darauf folgende Position belegt
• 1 2 3 3 4 5 6 7 8 8 9
D_REGION
REGION_ID
ORTNR
ORT
KREISNR
KREIS
LANDNR
LAND
REGIONNR
REGION
F_UMSATZ
ARTIKEL_ID
KUNDEN_ID
ZEIT_ID
REGION_ID
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
SELECT *
FROM
(SELECT
R.land as
Land,
sum(U.umsatz) AS Umsatz,
RANK() OVER (ORDER BY sum(U.umsatz) DESC ) AS
Rangfolge
from
F_umsatz U,
D_REGION R
WHERE U.REGION_ID = r.region_id
group by r.land)
WHERE rownum < 6;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Gruppierungen auf unterschiedlichen Leveln und Ranking auf Level-Ebene
Partition By
- Sortierung der Umsätze nach Top Artikel pro
Artikelgruppe
- Bilden der Rangfolge innerhalb einer Gruppe
(SELECT artikel_name Artikel,
gruppe_name Prod_Grp,
sum(U.Umsatz) AS Umsatz,
RANK() OVER (PARTITION by a.gruppe_name ORDER BY sum(U.umsatz) DESC )
AS Rangfolge
FROM f_umsatz U, d_artikel A
D_ARTIKEL
WHERE U.artikel_id
= a.artikel_id
ARTIKEL_NAME
GROUP by a.gruppe_name,a.artikel_name
GRUPPE_NR
GRUPPE_NAME
ORDER by a.gruppe_name)
SPARTE_NAME
SPARTE_NR
ARTIKEL_ID
PK
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
F_UMSATZ
FK
ARTIKEL_ID
FK
KUNDEN_ID
ZEIT_ID
FK
FK
REGION_ID
KANAL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
DATA WAREHOUSE
Gruppierungen auf unterschiedlichen Leveln und Ranking auf Level-Ebene
Partition By
- Sortierung der Umsätze nach Top 3 Artikel pro
Artikelgruppe
-Bilden der Rangfolge innerhalb einer Gruppe
SELECT * FROM
(SELECT artikel_name Artikel,
gruppe_name Prod_Grp,
sum(U.Umsatz) AS Umsatz,
RANK() OVER (PARTITION by a.gruppe_name ORDER BY sum(U.umsatz) DESC )
AS Rangfolge
FROM f_umsatz U, d_artikel A
F_UMSATZ
WHERE U.artikel_id
= a.artikel_id
D_ARTIKEL
ARTIKEL_ID
GROUP by a.gruppe_name,a.artikel_name
ARTIKEL_NAME
KUNDEN_ID
GRUPPE_NR
ORDER by a.gruppe_name)
ZEIT_ID
FK
GRUPPE_NAME
REGION_ID
WHERE Rangfolge < 4;
SPARTE_NAME
SPARTE_NR
PK
ARTIKEL_ID
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
FK
FK
FK
FK
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
DATA WAREHOUSE
(Zeit-) Reihenvergleiche
- Sortierung der Umsätze nach Monaten
LAG()
- Anzeigen des Vorjahresmonats
select
z.Jahr_nummer Jahr,
z.MONAT_DESC
Monat,
sum(u.umsatz) Umsatz,
LAG(sum(u.umsatz), 12) OVER (ORDER BY z.MONAT_DESC) Vorjahresmonat,
from
f_umsatz u,
d_zeit z
where
D_ZEIT
F_UMSATZ
z.zeit_id = u.zeit_id
DATUM_ID
ARTIKEL_ID
TAG_DES_MONATS
group by
TAG_DES_JAHRES
z.Jahr_nummer,z.MONATS_NUMMER,z.MONAT_DESC KUNDEN_ID
ZEIT_ID
WOCHE_DES_JAHRES
REGION_ID
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
DATA WAREHOUSE
Vergleich / Steigerung zum Vormonat
select
z.Jahr_nummer Jahr,
z.MONAT_DESC Monat,
sum(u.umsatz) Umsatz,
LAG(sum(u.umsatz)) OVER (ORDER BY z.MONATS_NUMMER) Vormonat,
round(((sum(u.umsatz))/ NVL((LAG(sum(u.umsatz)) OVER (ORDER BY z.MONATS_NUMMER)),NULL)),2) Steigerung,
sum(sum(u.umsatz)) over (PARTITION by z.Jahr_nummer order by z.MONATS_NUMMER) year_to_date
from
f_umsatz_2014 u,
d_zeit z
where
z.zeit_id = u.zeit_id
group by
z.Jahr_nummer,
z.MONATS_NUMMER,
z.MONAT_DESC
/
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Quartalsvergleiche
select
z.Jahr_nummer Jahr,
z.Quartals_Nummer Quartal,
sum(u.umsatz) Umsatz_Quartal,
lag(sum(u.umsatz)) over (ORDER BY z.Quartals_nummer) Vor_Quartal,
(sum(u.umsatz))/(lag(sum(u.umsatz)) over (ORDER BY z.Quartals_nummer)) Steigerung
from
f_umsatz_2014 u,
d_zeit z
where
z.zeit_id = u.zeit_id
group by
z.Jahr_nummer,
z.Quartals_nummer
/
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Quartalsübersichten
Sortiert nach Umsatzstärke der Quartale
Anteil eines Quartals am Jahresumsatz
Vorquartal
Steigerung zu Vorquartal
select
z.Jahr_nummer Jahr,
z.Quartals_Nummer Quartal,
sum(u.umsatz) Umsatz_Quartal,
RANK() OVER (ORDER BY sum(U.umsatz) DESC ) AS Rangfolge,
sum(sum(u.umsatz)) over (PARTITION by z.Jahr_nummer ) Jahres_Umsatz,
round(sum(u.umsatz)/( sum(sum(u.umsatz)) over (PARTITION by z.Jahr_nummer ) ) * 100,2)
Prozentualer_Jahresanteil,
lag(sum(u.umsatz)) over (ORDER BY z.Quartals_nummer) Vor_Quartal,
round((sum(u.umsatz))/(lag(sum(u.umsatz)) over (ORDER BY z.Quartals_nummer)),2) Steigerung
from
f_umsatz_2014 u,
d_zeit z
where
z.zeit_id = u.zeit_id
group by
z.Jahr_nummer,
z.Quartals_nummer
/
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Summenzeilen ausgeben
GROUP BY ROLLUP / CUBE
SELECT
artikel_name Artikel,
gruppe_name Prod_Grp,
sum(U.Umsatz) AS Umsatz
FROM
f_umsatz U, d_artikel A
WHERE
U.artikel_id = a.artikel_id
GROUP by ROLLUP
(a.gruppe_name,a.artikel_name)
ORDER by
a.gruppe_name
• ROLLUP
– Summenzeilen entsprechend
der Felder unter GROUP BY
SchraubenschluesselSet
Stichsaege
Wasserwaage_1m
Abschleppseil
Aufkleber_D
Autatlas
ErsteHilfekoffer
Fahrradhalter
Felgenkappen
Gluebirnen_Set
Hydraulik_Wagenheber
KaelteSet
Oelfilter
Poliermittel
Reinigungsfilter
Reinigungstuecher
Sitzauflage
Universal_Wagenheber
Zusatzlicht_Front
Heimwerker
Heimwerker
Heimwerker
Heimwerker
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
KFZ-Zubehoer
1559999
1519724
1586078
24997230
1611334
1581215
1577793
1468262
1606904
1654885
1609519
1491170
1490092
1582112
1561724
1640785
1495886
1539511
1511483
1552300
24974975
199802537
137 rows selected.
• CUBE
– Summenzeilen für alle
Kombinationen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Ausgeben zusätzlicher „Steuerinformation“ für „programmiertes“ Auslesen des
Ergebnisses
SELECT
artikel_name
AS
Artikel,
gruppe_name
AS
Gruppe ,
sparte_name
AS
Sparte ,
sum(U.Umsatz) AS
Umsatz ,
GROUPING(Artikel_name) AS
Art_g ,
GROUPING(gruppe_name) AS Grp_g ,
GROUPING(Sparte_name) AS Spr_g ,
GROUPING_ID(artikel_name ,gruppe_name,sparte_name ) AS grouping_id
FROM
f_umsatz U, d_artikel A
WHERE
U.artikel_id = a.artikel_id
GROUP by CUBE
(a.sparte_name, a.gruppe_name,a.artikel_name)
HAVING
GROUPING(Artikel_name) = 1 OR
GROUPING(gruppe_name) = 1 OR
GROUPING(Sparte_name) = 1
ORDER by
GROUPING(Artikel_name),GROUPING(gruppe_name),GROUPING(Sparte_name)
/
ART_G
GRP_G
SPR_G G
ROUPING_ID
---------- ---------- ---------- ----------0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
0
1
0
2
• Kombinationen von 0 und 1 werden genutzt um entsprechende Summenwerte anzuzeigen
• Grouping_ID sortiert die Kombinationsvarianten durch
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
In-Memory Database im Data Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
214
Datenbank wahlweise
im Hauptspeicher
Keine Änderung der
Anwendung
Spaltenorientiert
Komprimiert
2-20 fach
Parallelisierung
Nutzt bestehende
DB-Architektur und
Funktionalität
Auf jeder Hardware
möglich
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Was macht In-Memory Database-Option so schnell
Buffer-Pool
Column-Store
•
In-Memory Speicherung
•
Spaltenorientierte Speicherung
• Dynamischer Column-Index
• Komprimierung
F_UMSATZ
F_UMSATZ
Row
Format
Column
Format
• SIMD-Verarbeitung
• Bessere Parallelisierung
• Bloom-Filter
• In-Memory-Aggregation
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
216
Weniger Schichten in der Architektur mit In-Memory
Integration
Layer
Enterprise Layer
Core - DWH / Info Pool
User View
Layer
1:1 Kopie
BI Plattform
Cache
Früher
Analytische Datenbank
1:1 Kopie
In Memory
Separate Hardware +
Maintenance
Integration
Layer
Enterprise Layer
Core - DWH / Info Pool
User View
Layer
BI Plattform
Heute
Virtuelle
Strukturen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
217
Es ist nur ein Schalter!! – Kein Migrationsprojekt
D_KUNDE
D_ARTIKEL
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
PK
D_REGION
REGION_ID
PK
ORTNR
ORT
KREISNR
KREIS LANDNR
LAND
REGIONNR
REGION
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
ARTIKEL_ID
F_UMSATZ
ARTIKEL_ID
KUNDEN_ID
ZEIT_ID
REGION_ID
FK
KANAL_ID
FK
UMSATZ
FK
MENGE
FK
UMSATZ_GESAMT
FK
UMSATZ_GESAMT
VERTRIEBS_KZ
STEUER
VERPACKUNGSART
HANDELSKLASSE
KOMMISSIONSWARE
LAGERWARE
BESCHREIBUNG
LIEFER_DATUM
RECHNUNGS_DATUM
ZAHLUNGS_ZIEL
20 Mio Sätze
~ 3,1 GB
PK
KUNDEN_ID
KUNDENNR
GESCHLECHT
VORNAME
NACHNAME
TITEL
ANREDE
GEBDAT
BRANCHE
WOHNART
KUNDENART
BILDUNG
ANZ_KINDER
EINKOMMENSGRUPPE
ORTNR NUMBER,
BERUFSGRUPPE
STATUS
STRASSE
TELEFON
TELEFAX
KONTAKTPERSON
FIRMENRABATT
BERUFSGRUPPEN_NR
BILDUNGS_NR
EINKOMMENS_NR
WOHNART_NR
HAUSNUMMER
PLZ
ORT
KUNDENKARTE
ZAHLUNGSZIEL_TAGE
TOTAL
TOTAL_NR
• Laden in den InMemory-Speicher:
Einfaches Markieren
• Danach einmal anfassen
Alter
Alter
Alter
Alter
Alter
Alter
table
table
table
table
table
table
F_UMSATZ INMEMORY;
D_ZEIT
INMEMORY;
D_VERTRIEBSKANAL INMEMORY;
D_REGION INMEMORY;
D_KUNDE INMEMORY;
D_ARTIKEL INMEMORY;
CREATE TABLE F_UMSATZ ……
PARTITION BY RANGE ……
(PARTITION p1 ……
INMEMORY NO MEMCOMPRESS
PARTITION p2 ……
INMEMORY MEMCOMPRESS FOR DML,
PARTITION p3 ……
INMEMORY MEMCOMPRESS FOR QUERY,
:
PARTITION p200 ……
INMEMORY MEMCOMPRESS FOR CAPACITY
);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Der Blick in den Column-Store
1
Alter table F_UMSATZ_BREIT inmemory
2
alter table f_umsatz_breit INMEMORY
INMEMORY MEMCOMPRESS FOR QUERY HIGH (ARTIKEL_ID,KUNDEN_ID,ZEIT_ID,REGION_ID,KANAL_ID,UMSATZ,MENGE)
NO INMEMORY (UMSATZ_GESAMT,VERTRIEBS_KZ,STEUER,VERPACKUNGSART,HANDELSKLASSE,KOMMISSIONSWARE,
LAGERWARE,BESCHREIBUNG, LIEFER_DATUM,RECHNUNGS_DATUM,ZAHLUNGS_ZIEL);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
219
Was macht In-Memory so schnell?
Der Aufwand für die Abarbeitung analytischer Abfragen lässt sich aufteilen in
20%
40%
40%
Data Access
Joins
Aggregation
• In-Memory Scan
• SIMD Enhanced
Bloom Filters
• In-Memory
Aggregation
Column-oriented
Compression
Parallel Query
Sum / avg / group by....
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
220
DATA WAREHOUSE
> 7 Milliarden Werte
pro Sekunde pro CPU Core
In-Memory Aggregieren
Products
Bloom Filter
LINEORDER
Amount
DateKey
DateKey
Date
Memory
Datekey is
24122013
REGION
Type=d.d_date='December 24, 2013'
Footwear
Stores
Outlets
Outlets
Footwear
DATE_DIM
Vektor Scans (SIMD)
Sales
In-Memory
Report Outline
$
$$
$$$
$
Sales
CPU
Load
multiple
region
values
Vector Register
CA
CA
CA
Sum
Vector
Compare
all values
an 1 cycle
CA
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
221
In-Memory zusätzlich schnell machen
Join-Group-Objekte
Price *XTax
Price ++ Price
Price
Tax
Tax
Price
Sales
NAME
Vehicle
Sales
NAME
is join
column
VEHICLE NAME
In-Memory-Expression
Net = Price + Price * Tax
CREATE TABLE SALES (
PRICE NUMBER, TAX NUMBER, …,
NET AS (PRICE + PRICE * TAX))
INMEMORY;
Sammelt
CREATE INMEMORY JOIN GROUP V_name_jg
(VEHICLES(NAME),SALES(NAME));
automatisch die
Top 20 analytischen Abfragen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Oracle Confidential
–
222
In-Memory im Data Warehouse
• Komplexe Abfragen auf viele Daten
–
–
–
–
mit mehreren Joins
Sub-Selects
Groupings
Analytische Funktionen
Keine repräsentative Hardware: Laptop mit Intel i5-3320M 2,6 GHz CPU und 4 GB In-Memory-Storage
Beispielabfrage
Subselect
Ohne In-Memory
Analytic
Grouping
Functions
P1
P4
Mit In-Memory
P1
P4
#Joins
#Where
Condition
Umsatz pro Region, Zeit,
Vertriebskanal, , Berufsgruppe
Sortiert nach Kriterien (Abf. 2)
5
5
-
1
1
14,35
27,00
1,10
0,53
Stärkster Umsatzmonat pro
Bundesland in 2010 (Abf. 8)
3
2
1
3
2
26,66
35,03
1,00
0,54
Top 10 Artikel bezogen auf
Umsatz in einem Jahr pro
Bundesland in 2010 (Abf. 10)
4
3
1
4
3
23,14
32,03
3,02
1,55
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partitionierung und In-Memory-Kompression
Partitionierte
Tabelle
Juni 14
MEMCOMPRESS FOR DML
ständige Updates
Mai 14
April 14
März 14
MEMCOMPRESS FOR QUERY LOW
ausschließlich Lesen
Februar 14
Januar 14
Dezember 13
MEMCOMPRESS FOR CAPACITY LOW
gelegentliches Lesen
November 13
Oktober 13
September 13
August 13
Juli13
MEMCOMPRESS FOR CAPACITY HIGH
kaum Leseaktivitäten
Juni 13
April 13
März 13
Februar 13
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Oracle In-Memory Advisor
• New In-Memory Advisor
• Analysiert bestehenden DB
Workload über AWR & ASH
Repository
• Liefert eine Liste von Objekten,
die am meisten von InMemory-Colume-Storage
profitieren
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Mögliches Vorgehen : Virtualisierung User View Layer
Enterprise Layer
• Im Enterprise Layer müssen
– Dimensionsschlüssel müssen bereits
existieren
– Historisierung
• Historisierung
• Kaum physikalische Persistenz
auf den Festplatten
• Zugewinn
– Flexiblere und schneller Bereitstellung
multidimensionaler Strukturen
– Weniger Plattenplatz weil weniger
Redundanz
im Schichtenmodell
– Mehr Performance
REGION
REGIONNR
REGIONNR
LAND
LANDNR
LANDNR
KREISNR
KREIS
User View Layer
REGION
REGIONNR
LAND
LANDNR
KREISNR
KREIS
ORTNR
ORT
REGION_ID
Dimension als View auf
In-Memory
Stammdaten
Mav
KZ
VW_ORT
KREISNR
ORTNR
ORT
REGION_ID
ARTIKEL_ID
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NR
GRUPPE_NAME
SPARTE_NR
ARTIKEL_ID
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NR
SPARTE_NAME
VW_ARTIKEL
SPARTE_NR
SPARTE_NAME
ZEIT_ID
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
Kleine Dimensionen
terden persistiert
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
226
Komplexität von Joins im virtuellen Data Mart ist
überschaubar
ARTIKEL_ID
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SEGMENT_NR
SEGMENT_NAME
SPARTE_NR
SPARTE_NAME
View
D_ARTIKEL
Data Mart
User View Layer
In-Memory-Load
(Nur 15 von 60
Partitionen und
8 von 25 Spalten,
Datenreduzierung
> 90%)
A l l e
100000
Einzelartikel
ARTIKEL_ID
ARTIKEL_NAME
GRUPPE_NR
Transaktionsdaten
500 Millionen Sätze
T a b e l l e n
1000
Artikelgruppen
GRUPPE_NR
GRUPPE_NAME
SEGMENT_NR
I n - M e m o r y
100
Artikelsegmente
SEGMENT_NR
Segment_NAME
SPARTE_NR
10
Artikelsparten
SPARTE_NR
SPARTE_NAME
Core
Warehouse
Enterprise Layer
Die Anzahl Zeilen in den von dem Join betroffenen
Tabellen nimmt in einer Dimension sehr schnell ab
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
227
Effekte des Virtualisierungs-Szenarios
Klassische
Lösung
Virtualisierung
Data Marts
Antwortzeit
Beispielabfrage
Indexe
30- 50 Sek. (Platte)
3- 5 Sek. (Cache)
0.5 – 1 Sek.
Bitmap-Indexe
keine
Plattenplatz
Redundanzen zwischen
Kern-DWH und Data Marts
Minus 20 – 50 %
Wegfall Redundanz + Indexe
ETL-Aufwand
Aufbau Data Marts
~ minus 20%
(einfache ETL-Strecken fallen weg)
Anzahl
Tabellen
100 %
~ minus 10% Gesamt-Anzahl
~ minus 50 % der großen Tabellen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
228
Ist In Memory nur etwas für große Systeme?
• Nein
• Vor allem auch kleinere
Umgebungen profitieren
Ohne Inmemory
Mit Inmemory
Potenzielles Leistungsvermögen
200 MB / Sec / Core = 1,6 GB / Sec
PARALLEL 4 optimal
Potenzielles Leistungsvermögen
200 MB / Sec / Core = 1,6 GB / Sec
PARALLEL 16 optimal
• Sie haben oft kein eigenes
Storage-System, sondern
hängen am SAN und
konkurrieren mit vielen OLTPAnwendungen
• Das SAN wirkt oft als
„IO-Bremse“
• Mit In-Memory machen sich
kleinere Systeme
IO-unabhängig!
> 5 GB / Sec
In Memory
Column Store
700 MB / Sec
700 MB / Sec
Andere
OLTP
Anwendungen
SAN
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Andere
OLTP
Anwendungen
SAN
DATA WAREHOUSE
229
Laden und Updaten im Data Warehouse mit
Bordmitteln der Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
230
Ziele und Aufgaben
Was wird geladen
• Bereitstellen von Daten in adäquater
Weise
• Es sollte nur das geladen werden, was
wirklich gebraucht wird
– Zeitlich passend
– Richtige Form
– Passende Inhalte
• Gibt es einen Auftrag für das Laden
bestimmter Daten?
– Wer braucht die Daten?
– Welche Daten werden gebraucht?
• Daten so ablegen, dass man sie
wiederfindet
– Dokumentation
• Daten Ressourcen-ökonomisch speichern
– Berücksichtigung von Plattenplatz
• Sind die zu ladenden Daten in einem
brauchbaren Zustand?
– Welche Anforderungen sind an Quelldaten zu
stellen?
– Wer definiert die Anforderungen?
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
231
Schichtenmodell zur Organisation des ETL-Prozesses nutzen
Data Integration Layer
User View Layer
Enterprise Information Layer
Repository
(Glossar, alle Objekte)
Richtig
selektieren
Die Masse
aller
Prüfungen
StammReferenzdaten
aktualisieren
R
R
T
S
T
S
S
B
T
B
B
ETL: Kosten
pro Kunde
Nur
denormalisierende
Joins
B
B
B
D
ETL: Kosten
pro Kunde
D
F
ETL: Kosten
pro Kunde
D
D
D
ETL: Kosten
pro Kunde
Möglichst viele
Kennzahlen in die
Datenbank
F
D
F
D
D
Die frühest mögliche Stelle für Transformationen finden
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
232
Verfahren für schnelles ETL in der Datenbank
Data Integration
Layer
Selektieren
Statt
kopieren
Direct Path
in temporäre
Tabellen
Contraintfreies
Prüfen mit
Mengenbasiertem
SQL
User View
Layer
Enterprise Information
Layer
Partition
Exchange
&LOAD
in partit.
Tabellen
(PEL)
Unveränderte
Bewegungsdaten liegen
lassen
Große
Fakten-Tab.
über PEL.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Kennzahlen ausschließlich
über
MAVRefresh
Bekannte
Kennzahlen
in die DB
weniger
Koipien in
BI-Tools
DATA WAREHOUSE
Direct Path / Convential Path
Convential Path
• Reuse Free Space in Blöcken
• Constraint Checks
• Undo Data / Logging
• Daten zunächst immer in SGA Buffer
Direct Path
• Schreiben oberhalb der High Water Marks
• Keine Constraint Checks
• Kein Logging
• Daten nicht in SGA Buffer
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Convential und Direct Path Load
Convential Path
Direct Path
• Konkurriert mit allen anderen Prozessen im BufferManagement
• Baut Column-Array-Struktur auf und gibt
diese direkt an die Load Engine der DB
• Baut SQL-Struktur auf, die dann zu analysieren ist.
• Sucht nach nicht vollständig gefüllten Blöcken und
füllt diese
• Sinnvoll bei:
– Beschreiben von indizierten Tabellen
– Parallel zu anderen Loadvorgängen stattfindenden
Loads
(kein exklusiver Zugriff möglich)
– Bei dem Laden von kleinen Datenmengen in Tabellen
mit großen Indizes
– Bei Tabellen mit Constraints
236
– Ohne zuvor in Buffer zu schreiben
– Bildet neue Blöcke und schreibt diese direkt
weg
• Sinnvoll bei:
– Nicht indizierten Tabellen
(Index würde bei dem Zusammenführen mit
neuen Einträgen kopiert werden. Das kostet
Zeit)
– Nicht mit Constraints (PK/FK) belegten Tabellen
(Constraints werden zunächst ausgeschaltet
und nachträglich wieder aktiviert. Das kann
insgesamt länger dauern)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Hinweise zum Direct Path Load
create table tab (a integer);
insert /*+ append */ into tab select rownum from dual;
select * from tab;
FEHLER in Zeile 1:
ORA-12838: Objekt kann nach paralleler Änderung nicht gelesen/geändert
werden
• Zieltabelle ist exklusiv nur für den Ladelauf geöffnet
• Schreibt immer oberhalb der High Watermark
– Bei der Definition der Tabellen PCTFREE klein wählen (abhängig von späteren UPDATEVorgängen)
– Bei der Definition der Tabellen PCTUSED klein wählen
(lässt die Blöcke möglichst voll werden)
237
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Table Functions – Pipeline-Verfahren
Parallelisierung trotz aufwendiger Programmierlogik
tf1
tf2
Quelle
Ziel
tf3
Stage_tabelle
INSERT INTO Ziel SELECT * FROM TABLE(tf2(SELECT * FROM
TABLE(tf1(SELECT
INSERT INTO Ziel SELECT
238
* FROM Quelle))))
* FROM tf( SELECT * FROM (Stage_tabelle))
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Mengenbasierte Verarbeitung
Trotz Programmierung
INSERT INTO Table
SELECT Feld1, Feld2
Table_Function(
FROM
)
Funktion
Variante 1
Variante 2
Cursor
Fetch
Loop
If a = b...
Update...
Case...
pipe
row(record Type)
• Schnelle Verarbeitung (Pipelined)
• Objekttechnik
• Parallelisierung
• Mehrere Rückgabewerte
und Einzelrückgaben
• Cursor als Input
• Schachtelbar
Return Table
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
239
Partition Exchange Loading (PEL)
Temporäre Tabelle
Financial
Production
Neuer Monat
Human Res.
P1
P2
P3
P4
Store
Supplier
Marketing
Service
4
4
9
8
Zeit
Monat 13
Parallel Direct Path INSERT
(Set Based)
CREATE TABLE AS SELECT
(CTAS)
CREATE Indizes / Statistiken anlegen
EXCHANGE Tabelle
• Unvergleichbar schnell!
243
Z1
Z2
Z3
Z4
Monat 12
Monat 11
Monat 10
DROP
PARTITION
Region
Faktentabelle
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Partition Exchange Loading (PEL)
-- Leere Partition an Zieltabelle hinzufügen
ALTER TABLE Bestellung ADD PARTITION "Nov08"
VALUES LESS THAN (to_date('30-Nov-2008','dd-mon-yyyy'));
-- Neue leere temporäre Tabelle erstellen
CREATE TABLE Bestellung_temp AS
SELECT * FROM Bestellung WHERE ROWNUM < 1;
1
2
-- Inhalte laden
INSERT INTO "PART"."BESTELLUNG_TEMP"
(BESTELLNR, KUNDENCODE, BESTELLDATUM, LIEFERDATUM, BESTELL_TOTAL, AUFTRAGSART, VERTRIEBSKANAL)
VALUES ('2', '3', TO_DATE('23.Nov.2008', 'DD-MON-RR'),
3
to_date('23.Nov.2008', 'DD-MON-RR'), '44', 'Service', '6');
Commit;
-- Erstellen Index auf temporäre Tabelle
CREATE INDEX Ind_Best_Dat_Nov ON Bestellung_temp ("BESTELLNR") NOLOGGING PARALLEL;
4
-- Temporäre Tabelle an die Zieltabelle anhängen
ALTER TABLE Bestellung EXCHANGE PARTITION "Nov08“ WITH TABLE Bestellung_temp
INCLUDING INDEXES WITHOUT VALIDATION;
244
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
5
Exchange Partition
Data Integration
Layer
Enterprise Information
Layer
Älteste
Checks
CTAS
Tmp_table
Direct Path
User View
Layer
Archivieren
(drop partition)
Mai
Juni
Juli
August
September
Oktober
November
Alter table
add partition
Alter table exchange partition
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Aufbau Fakten-Tabellen
Data Integration
Layer
Archivieren
(drop partition)
Älteste
Checks
CTAS
Tmp_table
Direct Path
User View
Layer
Enterprise Information
Layer
Fact-Table
Juni
Juli
August
September
Mai
Juni
Juli
August
September
Oktober
November
Oktober
November
Tmp_table
CTAS
Tmp_table
Alter table
exchange partition
Alter table exchange partition
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Set-Based ETL-Prüf- und –Transformations
Techniken in der Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
247
Umgang mit SQL und PL/SQL im DB-ETL
So nicht ...
Aber z. B. so ...
Create or replace procedure Proc_A
V1
number;
V2
number;
V3
varchar2;
V4
varchar2;
....
Cursor CS as select s1,s2 from tab_src;
Begin
open CS;
loop
fetch CS into v1,v2,...;
select f1 into v3 from tab1;
select f1 into v4 from tab2;
insert into Ziel _tab s1,s2,s3,s4
values(v1,v2,v3,v4);
end;
end;
insert into ziel
select f1, f2, f3, f4 from
(with CS as
select s1 v1,s2 v2 from tab_src
Select tab1.f1 f1 ,tab2.f2 f2, CS.s1 f3,CS.s2 f4
from tab1,tab2,CS
Where...
);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Es gibt 6 Prüf-Kategorien
Tabellen-über
greifende Regeln
Attribut-bezogene Regeln
1.
2.
Not Null / Pflichtfelder
Formatangaben
numeric
Alphanumerisch
Date
Masken
a)
b)
c)
d)
A
3.
4.
9.
D
B
Abhängigkeiten von Werten in anderen
Attributen desselben Satzes
C
8.
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
E
12.
13.
14.
Referenz-Zusammenhänge
Verweise auf Sätze einer anderen Tabelle (Relation)
Zeitinvariante Inhalte (z. B. Anz. Bundesländer)
Zeitabhängige Veränderungen
Über die Zeit mit anderen Daten korrelierende Feldinhalte
Verteilungs-/Mengen-bezogene Regeln
F
15.
Verteilung
a)
b)
Rekursive Zusammenhänge
Verweise auf andere Sätze derselben Tabelle (Relation)
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Zeit-/ Zusammenhang-bezogene Regeln
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
Aggregat – Bedingungen
–
Satz-übergreifende Regeln
6.
7.
10.
11.
Ober-/Untergrenzen / Wertelisten
Satz-bezogene Regeln
5.
Child-Parent (Orphan)
Parent-Child
a)
b)
a)
b)
Div. Check Constraint
Wertbereiche
–
Foreign Key
16.
Arithmetische Mittel
Varianz / Standardabweichungen
Qualitätsmerkmale und Mengen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Mengen-basierte Prüfungen mit SQL
Attribut-bezogene Regeln
1.
2.
numeric
Alphanumerisch
Date
Masken
a)
b)
c)
d)
A
3.
4.
B
Not Null / Pflichtfelder
Formatangaben
Div. Check Constraint
Wertbereiche (Ober-/Untergrenzen / Wertelisten)
Satz-bezogene Regeln
5.
Abhängigkeiten von Werten in anderen Attributen desselben
Satzes
Satz-/Tabellen-übergreifende Regeln
6.
7.
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
C,D
8.
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Rekursive Zusammenhänge
Verweise auf andere Sätze derselben Tabelle (Relation)
9.
Foreign Key
Child-Parent (Orphan)
Parent-Child
a)
b)
10.
Aggregat – Bedingungen
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
a)
b)
11.
select
bestellnr,
case
when -- wenn Feld BESTELLNR nicht numerisch
REGEXP_LIKE(BESTELLNR, '[^[:digit:]]')
then 1
else 0
End Num_Check_bestellnr
select
from bestellung;
CASE
WHEN (F1 = 3 and F2 = F3 + F4)
then 1
ELSE 0
end
from fx
insert /*+ APPEND */ into err_non_unique_bestellung
select bestellnr from
(select
count(bestellnr) n,
bestellnr
from bestellung
group by bestellnr)
where n > 1;
Referenz-Zusammenhänge
–
Verweise auf Sätze einer anderen Tabelle (Relation)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Abarbeitungslogik für Einzelfeldprüfung mit CASE
• Temporäre Tabelle ist optional
– Ist wesentlich übersichtlicher
– Erlaubt Kombination von unterschiedlichen Prüfkriterien
Gepruefte_Daten
Kopieren
Stage-Tabelle
Varchar2()
Feld1
Feld2
Feld3
INSERT INTO
temp_table
SELECT
CASE
....
FROM
Stage_Table
Temp-Tabelle
Varchar2()
Feld1
Feld2
Feld3
Feld1_is_null
Feld1_is_numeric
Feld2_is_numeric
INSERT ALL
WHEN
Feld_1_is_null =1
into
Error_Daten
WHEN
Feld_1_is_null=0
into
Gepruefte_Daten
Date
Number
Varchar2()
Error_Daten
Date
Number
Varchar2()
251
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Error Logging
• Constraints
– Unique Key / Primary Key
– Foreign Key
– NOT NULL
– Check Constraint
Kunde
INSERT INTO Kunde
VALUES (......)
LOG ERRORS INTO
kunde_err('load_2004
0802')
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
Kunde_err
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$
252
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Check Constraint mit Regular Expressions
CREATE TABLE Check_KUNDE (
KUNDENNR
NUMBER,
GESCHLECHT
NUMBER,
VORNAME
VARCHAR2(50),
NACHNAME
VARCHAR2(50),
ANREDE
VARCHAR2(10),
GEBDAT
DATE,
ORTNR
NUMBER,
STRASSE
VARCHAR2(50),
TELEFON
VARCHAR2(30)
);
Regel:
Im Kundennamen müssen Buchstaben
vorkommen und
keine reine Zahlenkolonne
ALTER TABLE check_kunde
ADD CONSTRAINT Ch_KD_Name
CHECK(REGEXP_LIKE(NACHNAME, '[^[:digit:]]'));
INSERT INTO check_kunde (Kundennr, Geschlecht, Vorname, Nachname, Anrede, Gebdat,
Ortnr, Strasse, Telefon)
VALUES (9,1,'Klaus','123','Herr','01.01.60',2,'Haupstr.',08923456);
FEHLER in Zeile 1:
ORA-02290: CHECK-Constraint (DWH.CH_KD_NAME) verletzt
• Verwendung von Regular Expressions steigert die Performance bei
Prüfungen
253
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Dimension
Umschlüsseln mit Key Lookup
Künstl. Schlüssel (Primary Key)
Log.Business Schlüssel (Alternate Unique)
6
5
4
3
2
1
KD_66
KD_55
KD_44
KD_33
KD_22
KD_11
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Join Lookup
Log.Business Schlüssel
Satz12
AA
34
dddf
KD_11
1
DFG
64
dloidf
Satz13
DFG
64
dloidf
KD_22
2
DFG
64
dloidf
Satz14
erf
78
ghzf
KD_33
3
erf
78
ghzf
Satz15
sdfg
4456
llkof
KD_44
4
sdfg
4456
llkof
Fakten
Bewegungsdaten
254
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lookup-Verfahren mit Aktualisierung (Stammdaten)
Join
Bewegungssätze
Zielsätze
Anti –
Join
Referenzdaten
Tmp Table
alle Sätze ohne
Referenz
Insert mit
Dummy –
Schlüssel
Protokoll
255
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Künstl. Schlüssel (Primary Key)
Log.Business Schlüssel (Alternate Unique)
Sequenz
Next Val 7
1. Schritt
7
KD_99
Dummy
Dummy
Dummy
6
5
4
3
2
1
KD_66
KD_55
KD_44
KD_33
KD_22
KD_11
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Stamm Info
Anti - Join
Wenn nicht in Dimension
enthalten dann
Dimension
2. Schritt
Join Lookup
INSERT INTO Dim
SELECT .... FROM Bew, Dim
WHERE Log Key NOT IN Dim
DATA WAREHOUSE
Log.Business Schlüssel
Satz12
XX
567
ddwer
KD_99
7
XX
567
ddwer
34
dddf
KD_11
1
DFG
64
dloidf
Satz12
AA
Satz13
DFG
64
dloidf
KD_22
2
DFG
64
dloidf
Satz14
erf
78
ghzf
KD_33
3
erf
78
ghzf
Satz15
sdfg
4456
llkof
KD_44
4
sdfg
4456
llkof
Bewegungsdaten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Fakten
DATA WAREHOUSE
256
Native Support für Pivot und Unpivot
Sinnvoller Einsatz im Rahmen
des ETL-Prozesses
SALESREP
Q1
Q2
Q3
Q4
---------- ----- ----- ----- ----100
230
240
260
300
101
200
220
250
260
102
260
280
265
310
257
SALESREP
---------100
100
100
100
101
101
101
101
102
102
102
102
QU
REVENUE
-- ---------Q1
230
Q2
240
Q3
260
Q4
300
Q1
200
Q2
220
Q3
250
Q4
260
Q1
260
Q2
280
Q3
265
Q4
310
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Native Support für Pivot und Unpivot
Sinnvoller Einsatz im Rahmen
des ETL-Prozesses
QUARTERLY_SALES
SALESREP
Q1
Q2
Q3
Q4
---------- ----- ----- ----- ----100
230
240
260
300
101
200
220
250
260
102
260
280
265
310
SALESREP
---------100
100
100
100
101
101
101
101
102
102
102
102
QU
REVENUE
-- ---------Q1
230
Q2
240
Q3
260
Q4
300
Q1
200
Q2
220
Q3
250
Q4
260
Q1
260
Q2
280
Q3
265
Q4
310
select * from quarterly_sales
unpivot include nulls
(revenue for quarter in (q1,q2,q3,q4))
order by salesrep, quarter ;
258
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Native Support für Pivot und Unpivot
Sinnvoller Einsatz im Rahmen
des ETL-Prozesses
SALESREP 'Q1' 'Q2' 'Q3' 'Q4'
---------- ----- ----- ----- ----100
230
240
260
300
101
200
220
250
260
102
260
280
265
310
SALES_BY_QUARTER
SALESREP
---------100
100
100
100
100
100
100
101
101
101
101
102
QU
REVENUE
-- ---------Q1
230
Q2
240
Q3
160
Q4
90
Q3
100
Q4
140
Q4
70
Q1
200
Q2
220
Q3
250
Q4
260
Q1
260
select * from sales_by_quarter
pivot (sum(revenue)
for quarter in ('Q1','Q2','Q3','Q4'))
order by salesrep ;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
259
Aggregate bilden
• Bereitstellung in Form von Materialized Views
– Spart einen separaten ETL-Lauf
– Flexibler, weil nur 1 SQL-Kommando nötig
– Geht zu Lasten der Dokumentation
• Mit Partition Change Tracking die Aktualisierung der MAVs steuern
– Auch andere Refresh-Mechanismen nutzen
260
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Multiple
Inserts
261
INSERT ALL
WHEN STATUS = 'P'‚
THEN INTO WH_TRANS_PRIVAT
(BESTELLMENGE,KUNDENCODE,BESTELL_TOTAL,STATUS)
VALUES
(BESTELLMENGE$1,KUNDENCODE$1,BESTELL_TOTAL$1,STATUS)
WHEN STATUS = 'F'‚
THEN INTO WH_TRANS_FIRMA
(BESTELLMENGE,KUNDENCODE,BESTELL_TOTAL,STATUS)
VALUES
(BESTELLMENGE$1,KUNDENCODE$1,BESTELL_TOTAL$1,STATUS)
SELECT WH_TRANSAKTIONEN.BESTELLMENGE BESTELLMENGE$1,
WH_TRANSAKTIONEN.KUNDENCODE KUNDENCODE$1,
WH_TRANSAKTIONEN.BESTELL_TOTAL BESTELL_TOTAL$1,
WH_TRANSAKTIONEN.STATUS STATUS
FROM
WH_TRANSAKTIONEN WH_TRANSAKTIONEN
WHERE
(WH_TRANSAKTIONEN.STATUS = 'P‚ /*SPLITTER.PRIVATKUNDEN*/)
OR
(WH_TRANSAKTIONEN.STATUS = 'F‚ /*SPLITTER.FIRMENKUNDEN*/);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
MERGE-Funktion
• Funktion MERGE dient dem
gleichzeitigen INSERT und UPDATE
• Basierend auf dem Matching des
definierten Schlüssels (ON-Klausel)
• Auch DELETE-Operationen möglich
262
MERGE INTO "Kunde_TGT" USING
(SELECT
"KUNDEN_STAMM"."KUNDENNR" "KUNDENNR",
"KUNDEN_STAMM"."VORNAME" "VORNAME",
"KUNDEN_STAMM"."NACHNAME" "NACHNAME",
"KUNDEN_STAMM"."STATUS" "STATUS",
"KUNDEN_STAMM"."STRASSE" "STRASSE",
"KUNDEN_STAMM"."TELEFON" "TELEFON",
"KUNDEN_STAMM"."TELEFAX" "TELEFAX„
FROM "KUNDEN_STAMM" "KUNDEN_STAMM") MERGE_SUBQUERY
ON ( "Kunde_TGT"."KUNDENNR" = "MERGE_SUBQUERY"."KUNDENNR")
WHEN NOT MATCHED THEN
INSERT
("Kunde_TGT"."KUNDENNR",
"Kunde_TGT"."VORNAME",
"Kunde_TGT"."NACHNAME",
"Kunde_TGT"."STATUS",
"Kunde_TGT"."STRASSE",
"Kunde_TGT"."TELEFON",
"Kunde_TGT"."TELEFAX")
VALUES
("MERGE_SUBQUERY"."KUNDENNR",
"MERGE_SUBQUERY"."VORNAME",
"MERGE_SUBQUERY"."NACHNAME",
"MERGE_SUBQUERY"."STATUS",
"MERGE_SUBQUERY"."STRASSE",
"MERGE_SUBQUERY"."TELEFON",
"MERGE_SUBQUERY"."TELEFAX")
WHEN MATCHED THEN UPDATE
SET
"VORNAME" = "MERGE_SUBQUERY"."VORNAME",
"NACHNAME" = "MERGE_SUBQUERY"."NACHNAME",
"STATUS" = "MERGE_SUBQUERY"."STATUS",
"STRASSE" = "MERGE_SUBQUERY"."STRASSE",
"TELEFON" = "MERGE_SUBQUERY"."TELEFON",
"TELEFAX" = "MERGE_SUBQUERY"."TELEFAX";
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lade-Transaktionssteuerung innerhalb der
Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
263
Aufgabenstellung der Lade-Transaktion
• Betrachten des kompletten Ladelaufs als eine zusammenhängende
Transaktion
– Entweder alle Sätze oder keine geladen
• Wie können abgebrochene Ladeläufe wieder rückgängig gemacht
werden?
264
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Transaktionssteuerung / -rücksetzung
• Markieren von Sätzen eines Ladelaufs in zusätzlichen Feldern
– Ladelauf-Nummer, Ladelauf-Datum, ...
– Zurückrollen durch langsames Einzel-DELETE
3
• Arbeiten mit Partitioning
– Aufbau einer neuen Partition unabhängig von der Zieltabelle
– Schnelles DROP PARTITION im Fehlerfall
– Einfachste und schnellste Variante
2
• Flashback Database / Table / Query
– Transaktions-genaues Zurückrollen
– Flashback DB benötigt zusätzlichen Plattenplatz
265
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
1
DATA WAREHOUSE
Flashback
• Steuerung über
– SCN ( Sequence Change Number / Log Archiving)
– Zeit (Timestamp)
– Restore Point
ETL
Flashback Recovery Area
Zeit
Log
SCN
Restore Point
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
266
Flashback Technologie in der Datenbank
Flashback table x to scn 16552768;
• Flashback Table
• Flashback Drop
• Flashback Query
• Flashback Versions Query
• Flashback Transaction Query
• Flashback Database
• Flashback Data Archive
SELECT * FROM employees AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2008-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2008-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE
first_name = 'John';
SELECT xid, operation, start_scn,
commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
Flashback Database to scn 16552768;
Flashback Database AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beispiel Flashback Table
CREATE TABLE x (Nummer number);
ALTER TABLE x ENABLE ROW MOVEMENT;
INSERT INTO X VALUES (1);
INSERT INTO X VALUES (1);
INSERT INTO X VALUES (1);
COMMIT;
Jetzt erst wird eine SCN erzeugt
Abfrage u. Flashback der letzten Änderungs-SCN
SELECT ora_rowscn FROM x;
SELECT * FROM x AS OF SCN 12555060;
SELECT * FROM x AS OF TIMESTAMP to_timestamp('2012-02-15 10:15:00', 'YYYY-MM-DD HH:MI:SS');
Zurücksetzen
Flashback table x to scn 16552768;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
268
Flashback Database
• Flashback Database
–
–
–
–
–
Erstellen der Fast (Flash) Recovery Area
Restart Database ( mount exclusive, wenn DB <11.2)
SQL> ALTER DATASE FLASHBACK ON;
SQL> ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
SQL> ALTER DATABASE OPEN;
• Restore Points (ab 11.2 im laufenden Betrieb)
– create restore point PRE_LOAD;
– create restore point PRE_LOAD guarantee flashback database;
(impliziert das Anlegen von Flashback Logs)
– drop restore point PRE_LOAD;
• Anwendung eines Restores nur im DB Mount-Status
– flashback database to restore point PRE_LOAD;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Security und Mandantensteuerung im Data
Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
270
Sicherheit in der Datenbank
Service
Servicekunde
Integration
Layer
BI-Anwendungen werden im Schnitt alle 4 Jahre ausgetauscht
Oft laufen mehrere BI-Anwendungen parallel
Enterprise Layer
Core - DWH / Info Pool
User View
Layer
BI Plattform
Referenzdaten (extern) (10%)
Stammdaten (10%)
Bewegungsdaten / Transaktionen (80%)
Logistik
Logistikaufwand
Controlling
Marketingsicht
Sozialsituation
Kundenhistorie
Profitabilität
ODBC
Vorberechnete
Kennzahlen
Profitabilität
C
Einkauf
Q
Kunde
A
MJ
Produkte&Trends
Vertrieb
Kundenhistorie
A
L
Logistikaufwand
für einen Kunden
Produkte&Trends
Data Mining
Statistikdaten
Servicekunde
Oracle R
Marketing
Marketingsicht
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Zwei Level des Zugriffs-Monitoring
1) Standard Auditing
2) Fine Grained Auditing
Statement
level. all
statements
in a sessions
any objects
Granting
Priviliges
activities
Object
level
(update, insert
delete, select)
By session
X
X
X
By access
X
Monitoring of all activities by
User by session, by object.
- Action based auditing
(e. g. analysing the predicate
example: value > 100000)
- all detailed commands
- Timestemps
-trials and successes
- Additional triggers
- notifications and other actions
DATA WAREHOUSE
Audit select on F_UMSATZ;
X
X
EXECUTE DBMS_FGA.ADD_POLICY(
object_schema => 'DWH‘
,
object_name
=> 'F_UMSATZ’,
policy_name
=> 'Chk_F_Umsatz', statement_types=> 'SELECT’
, enable
=> TRUE);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
272
sondern so...
Nicht so...
Mandant 1
Mandant 2
Mandant 3
Mandant4
Mandant 5
Mandant 6
Channels
Kunde
Channels
Kunde
Channels
Kunde
Channels
Kunde
Umsatz
Channels
Kunde
Umsatz
Channels
Kunde
Umsatz
Umsatz
Zeit
Produkte
Umsatz
Zeit
Produkte
Umsatz
Zeit
Produkte
Zeit
Produkte
Zeit
Produkte
Zeit
Produkte
Channels
Kunde
Alle Mandanten
in einer Tabelle
Umsatz
Zeit
Produkte
DATA WAREHOUSE
Nur einmal pflegen und verwalten!
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
273
Fine Grained Security on Row level
Zu lesenden Objekte
Zugreifende Users
User: DataMart1
User_Table
Context
User 2
User 3
Set User Context
Set User Context
select
sum(UMSATZ)
from umsatz;
select
sum(UMSATZ)
from umsatz;
Build Predicate Procedure
Partner
Catalog
Channels
Internet
Direct Sales
Kunde
Umsatz
Zeit
Predicate
Add / DROP Policy
Sieht nur
Partner-Umsatz
Sieht nur
Katalogwaren-Umsatz
Produkte
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA
DATAWAREHOUSE
WAREHOUSE
274
Auf der Seite des zu lesenden Objektes
Auf der Seite des zugreifenden Users
Definieren der Policy
Logon-Trigger
create or replace package channel_security as
function channelnum_sec (A1 VARCHAR2, A2 varchar2)
return varchar2;
end;
/
create or replace package body channel_security as
function channelnum_sec (A1 VARCHAR2, A2 varchar2)
return varchar2
is
d_predicate varchar2(2000);
v_channel_id number;
begin
select sys_context('channel_info','channel_num') into v_channel_id from DUAL;
d_predicate := 'SALES.CHANNEL_ID = '||v_channel_id||' or '||v_channel_id||' = 10';
return d_predicate;
end;
end;
/
Aktivieren der Policy
exec dbms_rls.drop_policy('AREA1','SALES','CHANNEL_POLICY');
exec dbms_rls.add_policy('AREA1','SALES','CHANNEL_POLICY','AREA1',
'channel_security.channelnum_sec','SELECT');
CREATE OR REPLACE TRIGGER vpd_init_trig AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'DROP CONTEXT channel_infostr';
EXECUTE IMMEDIATE 'CREATE CONTEXT channel_info using user_context';
system.User_context.select_user_Channel;
EXCEPTION
WHEN NO_DATA_FOUND THEN null;
end;
Erstellen des Kontextes
create or replace package User_context as
Procedure select_user_Channel ;
end;
/
Create or replace Package Body User_context as
Procedure select_user_Channel is
Channelnum number;
begin
select channel_id into Channelnum from area1.user_table where
upper(User_name) = sys_context('USERENV', 'SESSION_USER');
dbms_session.set_context('channel_info', 'channel_num',Channelnum);
end;
end;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA
DATAWAREHOUSE
WAREHOUSE
275
Label Security
• Regelgestützter Zugriffsschutz auf Record-Ebene
Label Security Policies
Data Label Components
User Labels
Maximum / Minumum / Default /
Row Level
Read / Write Compartments
Bis zu 9999
Bis zu 9999
Bis zu 9999
Read / Write Groups
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
276
Beispiel über die Wirkungsweise
Sätze einer Tabelle werden „gelabelt“
Benutzer erhalten „User Labels“
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
277
Redaction und Encryption
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Oracle Confidential
–
278
Encryption
• Schutz vor nicht autorisiertem Lesen von Datenträgern, Tablespaces, Files etc.
• Findet im Verlauf der DB-Installation statt
• Online – Encryption von Tablespaces
• Ausnutzen von CPU-basierter Hardware (Intel + SPARC Plattform)
– Performance
• Ausnutzen der Smart-Scan-Prozessen in Storage-Servern (Exadata)
• Separates Vorhalten von Encryption Schlüssel
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
279
Redaction (Maskierung)
• Selektives Maskierung von sensiblen Daten als Schutz vor
unberechtigtem Lesen
• SQL-Leseoperation und die gespeicherten Daten bleiben unverändert
– Daten werden erst nach dem Lesevorgang und unmittelbar vor der Anzeige der
Daten für den Benutzer „überschrieben“
– -> optimal für Performance
• Varianten
– Full
– Partial (*)
– Regular Expressions
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
280
Redacting Data - Beispiel
Die Daten selbst werden nicht verändert.
Die Maskierung erfolgt erst unmittelbar vor der Anzeige.
In diesem Bsp. Werde die ersten 7 Zeichen
der Column ‘CUSTID‘ mit der Ziffer ‘9‘ maskiert.
Die Spalte LAST_NAME aus der customerTabelle wurde ab dem 3. Zeichen mit ‘*‘ maskiert.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
281
Multi-strukturierte Daten mit JSON
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
JSON (Java Script Object Notation)
• Standardisiertes Format
– ECMA-404 (JSON Data Interchange Format)
– ECMA-262 (ECMAScript Language Specification, third edition)
• Leichteres Format als XML
– Weniger Datenmenge /Overhead
– Einfacher Umgang
• Besonders beliebt bei noSQL-DB- Java – Entwicklern
• Im Hadoop-Umfeld weit verbreitet
• Ab Oracle 12.1.0.2 direkt in der Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
283
Multi-Strukturierte Informationen
INSERT INTO Web_Log_array
VALUES (SYS_GUID(),
SYSTIMESTAMP,
'{"wl_rec_id" : 1600,
"wl_ip_Adr" : "168.192.1.10",
"wl_dns"
: "MP-AM5643",
"wl_start_date" : "11-08-2014:23:21",
"wl_end_date" : "11-08-2014:25:18",
"wl_ses_id" : "77763576423",
"wl_Ses_anz_sec" : "10",
"wl_status" : "ACK"}');
1
2
INSERT INTO Web_Log_array
VALUES (SYS_GUID(),
SYSTIMESTAMP,
'{"wl_rec_id" : 1600,
"wl_ip_Adr" : ["168.192.1.10","168.192.1.11"],
"wl_dns"
: "MP-AM5643",
"wl_start_date" : "11-08-2014:23:21",
"wl_end_date" : "11-08-2014:25:18",
"wl_ses_id" : "77763576423",
"wl_Ses_anz_sec" : "10",
"wl_status" : "ACK"}');
3
INSERT INTO Web_Log_array
VALUES (SYS_GUID(),
SYSTIMESTAMP,
'{"wl_rec_id" : 1600,
"wl_ip_Adr" : {"ip" : "168.192.1.10", "sub" : "255.255.255.0"},
"wl_dns"
: "MP-AM5643",
"wl_start_date" : "11-08-2014:23:21",
"wl_end_date" : "11-08-2014:25:18",
"wl_ses_id" : "77763576423",
"wl_Ses_anz_sec" : "10",
"wl_status" : "ACK"}');
SQL> select wa.Log_Record.wl_ip_Adr from Web_Log_array wa;
WL_IP_ADR
-----------------------------------------------------------------"168.192.1.10"
["168.192.1.10","168.192.1.11"]
{"ip":"168.192.1.10","sub":"255.255.255.0"}
SQL> select wa.Log_Record.wl_ip_Adr.sub from Web_Log_array wa;
WL_IP_ADR
--------------------------------------------------------------"255.255.255.0"
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
284
An welcher Stelle passen JSON-Objekte in das
Data Warehouse ?
• Daten für Auswertungen?
Integration
Layer
User View
Layer
Enterprise Layer
Core - DWH / Info Pool
• Daten zur Aufbewahrung?
• JSON – Format als Vorstufe vor
Einführung von Big Data
JSON
HDFS / NoSQL
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JSON
JSON
Unstructured Data
DATA WAREHOUSE
Die optimale Hardware für das
Data Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
286
Optimale („Balanced“) Konfiguration
• Anzahl CPU‘s
• ~200 MB Datendurchsatz pro CPU
• Anzahl CPU = Max. Durchsatz in MB/s / 200
• Größe des Speichers in GB = 2 * Anz. CPUs
• Größe des
Hauptspeichers
• Anzahl Platten
• Trennung von Storage für
OLTP und DWH-Systeme !!
• Schnelle Platten nutzen (15000 U/min)
• Eher mehr, kleine Platten nutzen,
als wenige große Platten nutzen
• Flash-Speicher in Betracht ziehen
• ASM in Betracht ziehen
•
Einfaches und DB-optimiertes Verwalten
Anzahl Disk Controller =
Max. Durchsatz in MB/s
Controllerdurchsatz in MB
Controllerdurchsatz in MB =
70% * Herstellerangaben in Gbit/s
8
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
287
Die Hardware Umgebung – Storage
• Trennung von Storage für OLTP und DWH-Systeme
• Schnelle Platten nutzen (15000 U/min)
• Eher mehr, kleine Platten nutzen,
als wenige große Platten nutzen
• Flash-Speicher in Betracht ziehen
• ASM in Betracht ziehen
– Einfaches und DB-optimiertes Verwalten
288
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Messung von IO-Durchsatz
• Einfache Schätzmethode
• Calibrate_IO
– Read-only Test
– Wenige Test-Optionen -> leicht anwendbar
– > 11g
• Orion (ORacle IO Numbers)
– Read / Write – Tests (Achtung schreibt auf Platten)
– Viele Test-Optionen
– OLTP + DWH Workload
– Ab 11.2 im BIN-Verzeichnis der DB
– www.oracle.com/technology/software/tech/orion/index.html
289
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Einfache Schätzmethode zur Lesegeschwindigkeit
Blockgröße
feststellen
select tablespace_name, block_size from dba_tablespaces;
Anzahl Blöcke/
Anzahl Bytes
SELECT table_name, num_rows, blocks,
blocks*8 KB,blocks*8/1000 MB,blocks*8/1000000 GB FROM user_tables;
Messen der
Lesegeschwindigkeit
Berechnung des
Durchsatzes
select count(*)
from bestellung_part_Range_4; -- liest komplette Tabelle
COUNT(*)
TABLESPACE_NAME
BLOCK_SIZE
------------------------------ ---------MON_G
8192
MON
8192
MON_D
8192
MON_E
8192
MON_F
8192
TABLE_NAME
NUM_ROWS
BLOCKS
KB
MB
GB
------------------------------ ---------- ---------- ---------- ---------- ---------BESTELLUNG_PART_RANGE
163840000
962355
7698840
7698,84
7,69884
BESTELLUNG_PART_RANGE_4
163840000
962355
7698840
7698,84
7,69884
---------163840000
Abgelaufen: 00:00:31.32
select 7.7/31 from dual;
SQL>
Ergibt ~0,25 GB pro Sekunde Lesegeschwindigkeit
(Achtung Blöcke eventuell nicht voll, daher geringer )
7.7/31
---------,248387097
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
290
Calibrate_IO
• Einfaches Tool
• Bestandteil des
Ressourcen
Managers
• Wird aus der DB
heraus aufgerufen
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>,
iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/
max_iops = 73
latency = 12
max_mbps = 20
291
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
ASM
• Verwalten ganzer Gruppen von Platten
– Keine Einzelaktionen
• DBA übernimmt die Storage-Verwaltung
– Gewohnte Kommandos… SQL Create…
• SAME in der DB
– Verlagern des Striping and Mirroring Everything in die Verantwortung der Datenbank
• Automatische Verteilung der Daten über alle Platten
– Verhindert von Hotspots
– Messung von IO-Zugriffen über DB-Statistiken (ist klassischen RAID-Verfahren überlegen)
• Bequemes Hinzufügen /Wegnehmen von Platten
• Verhindert Fragmentierung der Platten
• Einführung von ASM kann bis 25% verbessertes IO-Verhalten liefern
• Performance kommt an Raw Devices heran
292
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
ASM Architektur
• ASM Disks
– Partitionen oder LUNs, die über das Betriebssystem bereitgestellt werden
•
Ab 11 sind einfache Partitionen, RAW Devices oder auch NFS-Dateien möglich
• ASM Disk Groups
• ASM Files
– Files, die in den Disk Groups abgelegt sind, ohne
dass man deren physischen Ort bestimmt
– Die ASM-Files entsprechen den sonst üblichen
Datenbank-Files (1:1 Mapping)
– Eine oder mehrere ASM Disks
– ASM verteilt diese Files über mehrere physische Bereiche (Platten)
– Logical Volumes – logische Einheit von Speicherplatz
– Die logischen Konzepte wie Extents, Segmente
oder Tablespaces bleiben erhalten
– Eine DB kann mehrere Disk Groups haben
• ASM Failure Groups
– Ensemble von ASM Disk Groups, die als 2 oder 3-Wege-Spiegel
arbeiten
• ASM instance
ASM Disk
ASM Disk
ASM Disk
ASM Disk
ASM Disk
– Ähnlich einer DB-Instanz aber ohne datafiles
– Muss hochgefahren und auch über eine SID ansprechbar sein
293
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Intelligent Data Placement
• Intelligent Placement
• Häufig genutzte Daten werden automatisch auf die äußeren Spuren der Platten gelegt
• Weniger häufige auf die inneren
• Minimiert Verwaltungsaufwand und steigert die Performance um bis zu 50 %
• Bereitstellung der Plattenkapazität
• nicht an dem benötigten Volumen messen sondern an der Performance
• Platten müssen
nicht komplett
Mehr Daten erreichbar bei
gefüllt werden
gleicher Drehzahl
-> häufig genutzte Daten
Weniger Daten erreichbar
bei gleicher Drehzahl
-> seltener genutzte Daten
294
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Options: RAC
Der physische Aufbau einer RAC-Umgebung
Öffentliches Netzwerk
Privates Netzwerk
(Interconnect)
CPU
CPU
CPU
Knoten 1
CPU
CPU
Knoten 2
CPU
CPU
Instanz 1
CPU
Instanz 2
Speichernetzwerk
Daten
295
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Options: RAC
Oracle Cluster-Umgebung –
Real Application Clusters und Automatic Storage Management
Öffentliches Netzwerk
RAC
Privates Netzwerk
(Interconnect)
CPU
CPU
Knoten 1
CPU
Instanz 1
Oracle
Clusterware
CPU
CPU
Knoten 2
CPU
CPU
CPU
Instanz 2
Speichernetzwerk
ASM
296
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Architektonische Vorteile RAC und ETL
• Voraussetzung ETL in der Datenbank
– Nur dieses bringt Last auf die RAC-Knoten
• Verteilung der Datenbank-basierten ETL-Jobs auf unterschiedliche Knoten
• Laufen keine ETL-Jobs
– Knoten frei für andere Datenbank-Aufgaben
• Geringere Hardware-Anschaffungskosten
• Wegfall Backup-Rechner
• Wegfall Netzlast
– Direkter ETL-Zugriff auf Daten der eigenen Datenbank und über schnelle Leitungen
297
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Allgemeine Aufbauempfehlungen - RAC aus ETL-Sicht
• Die Knoten nicht zu klein wählen
– Sollten so stark sein, dass sie zusammenhängende ETL-Jobs auch alleine bewältigen können. (Z. B. 4
CPUs pro Knoten)
• RAC und ETL
– Das System sollte nicht darauf angewiesen sein, über die Knoten hinweg parallelisieren zu müssen, um
zu skalieren.
– Skalierung gelingt über die gezielte Steuerung zusammenhängender Lade-Jobs auf die
unterschiedlichen Knoten.
• Durchsatz für Interconnect 1-2 Gbit / Sec
• Hauptspeicher 4 GB pro CPU
• Durchsatz für das Speichernetzwerk:
pro CPU mindestens 100 MB/Sec
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
298
Monitoring Data Warehouse
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
299
Umgang mit Alerts
• Unvorhergesehene Vorgänge
– Statefull Alerts:
Entstehen durch
Überschreiten von Schwellwerten
– Stateless Alerts:
Unvorhergesehene Vorgänge
select metrics_name,
warning_value,
critical_value,
object_type,
object_name,
status
From dba_thresholds
• Z. B. zu wenig Recovery Area Space
300
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Mit OEM
301
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Automatic Database Diagnostic Monitor (ADDM) und AWR
Statistics_level
TYPICAL -> ON
BASIC -> OFF
1
AWR-Report
stündlich
AWR
ADDM
use
MMONProcess
User 1
sysaux
User 2
Findings
1……nn%
2……nn%
3……nn%
…….
2
Recommendations
3
Action
- Hardware
- Init-Parameter
- Space Konfig.
- Performance
Advisor
8 Tage lang
Undo Advisor
302
OEM
Addmrpt.sql
DBMS_ADVISOR Package
SQL Tuning Advisor
Segement Advisor
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
4
Rationale
DATA WAREHOUSE
AWR (Analytic Workload Repository)
• Regelmäßiges Sammeln von einer Vielzahl von System-generierten Statistiken
– Mit Hintergrundprozessen (MMON)
– Gespeicherte Statistiken des MMON in SYSAUX Tablespace
– Vorkonfiguriert generiert AWR alle 60 Minuten Snapshots
• Parameter STATISTICS_LEVEL (Basic/Typical/All)
– Basic schaltet das Sammeln aus
– Retention-Time (Default 8 Tage)
– DBA_HIST_* - Views zur Auswertung
• Manuell starten
– execute dbms_workload_repository.create_snapshot(‘ALL‘);
• Auswerten mit Awrrpt.sql OEM
303
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Verwaltung von AWR
• Prozeduren
– MODIFY_SNAPSHOT_SETTINGS
• Rentention / Vorhaltezeit der Snapshots
• Interval / Zeitabstand zwischen den Snapshots
• Topnsql / Menge der erfassten SQL-Statements (Default 30/Typical)
• Feststellen der eingestellten Intervalle
– Select * from dba_hist_wr_control;
• Feststellen Platzverbrauch
– @/Ora-home/Rdbms/admin/awrinfo.sql
– Oder V$SYSAUX_OCCUPANTS abfragen
• Select occupant_name, space_usage_kbytes from V$SYSAUX_OCCUPANTS where
occupant_name = 'SM/AWR‚
• Auflistung der bestehenden Snapshots
•
304
Select SNAP_ID, STARTUP_TIME, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, FLUSH_ELAPSED,SNAP_LEVEL from dba_hist_snapshot;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
ADDM (Automatic Database Diagnostic Monitor)
• Automatic Database Diagnostic Monitor (ADDM)
– Gezielte Auswertung von AWR Daten
– Liefert Informationen zu
•
•
•
•
•
Besonders teuere SQL-Statements
I/O – Performance
Locking-Situationen
Ressourcen-Engpässe (Speicher, CPU bottlenecks)
Exzessive Logon/Logoff-Aktivitäten
– Manuelle Berichtserstellung: ADDMRPT.SQL
305
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Art der Information
• „Intelligente“ selbständige Analyse von Zuständen und Vorkommnissen in der
DB
• „Findings“
– Basierend auf Erfahrungswerte und Best-Practises
– Sortiert nach der Schwere und dem Grad der Beeinflussung
• „Recommendations“
– Allgemeine Empfehlung mit einer Abschätzung über die prozentuale Gewichtung der
Verbesserung der Situation
(nn% benefits)
– Konkreter „Action“-Vorschlag
– „Rationale“ Vorschlag: Sonstige, damit in Verbindung stehende Massnahmen.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
306
DWH-bezogene Monitoring-Aktivitäten
• ASH-Report
• SQL-Monitoring (OEM)
• Informationsbedarf Endanwender
• Messung Platzverbrauch
• Lesestatistiken über tatsächlich genutzte Daten
• Ressourcen-Manager
• ETL-Monitoring
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
307
Beobachten des Informationsbedarfs
• Regelmäßige Teilnahme an Gremien
– Abstimmung / Feedback / Planung mit Fachabteilungen und DWH-Nutzern
• Statistiken über DWH-Nutzung
– Benutzerzahlen / Session-Statistik
– Datenmengen / Platzverbrauch
– Segment-Reads
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
308
Welche Daten werden wirklich genutzt?
10 – 50 Tabellen
500 – 1000 Tabellen
Große Tabellen
Partitioniert
Namentlich bekannt
> 70 % des Datenvolumens
309
KleineTabellen
Nicht Partitioniert
Unkenntliche Masse
< 30 % des Datenvolumens
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lesestatistiken für die wichtigsten Tabellen anlegen
• dba_hist…. - Views zum Sammel der Lese-Zugriffe
– dba_hist_seg_stat
– dba_hist_seg_stat_obj
– dba_hist_snapshot
• dba_hist_sqlstat
• dba_hist_sqltext
• Achtung:
– Views werden nur aktualisiert wenn
• Auch tatsächlich gelesen wurde
• Ein AWR-Snapshot gezogen wurde
– Zähler fällt auf 0, wenn die DB durchgestartet wird
• Aufbau einer eigenen Statistik-Tabelle mit
– Tab-Name, Snap-ID, Datum/Uhrzeit, Physical Reads
– Aktualisieren immer nachdem ein AWR-Snapshot gezogen wurde
310
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Lesestatistiken für die wichtigsten Tabellen anlegen
Select distinct * from
(select
to_char(begin_interval_time,'dd.mm.yyyy:hh24:MI') Zeit,
logical_reads_total log_rd,
logical_reads_delta log_rd_delta,
physical_reads_total phy_rd,
physical_reads_delta phy_rd_delta
from
dba_hist_seg_stat
s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot
sn
where
o.owner = 'DWH1'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id and
object_name = 'UMSATZ')
order by zeit;
ZEIT
LOG_RD LOG_RD_DELTA
PHY_RD PHY_RD_DELTA
---------------- ---------- ------------ ---------- -----------06.09.2010:22:00
3357520
3357520
3355361
3355361
06.09.2010:23:00
4030816
673296
4028177
672816
07.09.2010:12:32
8060160
4029344
8054609
4026432
07.09.2010:15:50
688
688
1
1
311
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Zugriffsdaten auf Tabellen über SQL sammeln
• SQL-Statements pro User analysieren
– From-Klausel parsen
– Zugriffe auf Tabellen
• System-Zugriffe ausschließen
– Wegen der Menge
• Historien-Tabelle aufbauen
– Mit aus der FROM-Klausel herausgefilterten Tabellennamen
– Zuordnung zu USER, Zeit und SQL-Statement
312
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Beispielabfrage
select
to_char(s.begin_interval_time,'mm-dd hh24') c1,
sql.sql_id
c2,
t.SQL_TEXT
C9,
sql.executions_delta
c3,
sql.buffer_gets_delta
c4,
sql.disk_reads_delta
c5,
sql.iowait_delta
c6,
sql.apwait_delta
c7,
sql.ccwait_delta
c8
from
dba_hist_sqlstat
sql,
dba_hist_snapshot
s,
dba_hist_SQLTEXT
t
where
s.snap_id = sql.snap_id
and
sql.PARSING_SCHEMA_NAME = 'DWH1' and
t.SQL_ID = sql.SQL_ID
and
sql.sql_id = '01978kjxb5yd2' and
to_char(s.begin_interval_time,'mm-dd hh24') = '09-12 13'
order by c1, c2;
col
col
col
col
col
col
col
col
col
c1
c2
c3
c4
c5
c6
c7
c8
c9
heading
heading
heading
heading
heading
heading
heading
heading
heading
‘Begin|Interval|time’
‘SQL|ID’
‘Exec|Delta’
‘Buffer|Gets|Delta’
‘Disk|Reads|Delta’
‘IO Wait|Delta’
‘Application|Wait|Delta’
‘Concurrency|Wait|Delta’
'SQL-Text'
format
format
format
format
format
format
format
format
format
a8
a13
9,999
9,999
9,999
9,999
9,999
9,999
a50
break on c1
Auszug
`Begin
`Buffer `Disk
Interval `SQL
`Exec
Gets
time'
ID'
SQL-Text
Delta' Delta' Delta‘
-------- ------- ------------------------------------------- ------ ------- ------ ---------09-12 13 01978kj Select * from (select Produkt, sum(U.summe)... AS Wert, 1
8,573 8,390
7448344
----- ------------------------------------------------ ------ ------- ------ ---------- -------01978k2 Select * from (select Produkt, sum(U.summe) ... AS Wert, 1
8,573 8,390
7494081
313
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Verwendungsinformationen speichern
User
Tabelle DWH-Zugriffshistorie
Tabname
314
Gelesen_Von_User
Anzahl_Read_IO
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Lese_Datum
DATA WAREHOUSE
Daten-Owner und Verursachung von Aufwand
D_ARTIKEL SPARTEN_MANAGER
SPARTE_NAME
SPARTE_NR
GRUPPE_NR
GRUPPE_NAME
PK
ARTIKEL_NAME
ARTIKEL_ID
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
PK
D_REGION
REGION_ID
PK
ORTNR
ORT
KREISNR
KREIS LANDNR
LAND
REGIONNR
REGION
D_Org_Unit
Org_unit
Org_Unit_Nr
Mitarbeiter_Name
Mitarbeiter_Nr
F_UMSATZ
FK
ARTIKEL_ID
FK
KUNDEN_ID
ZEIT_ID
FK
REGION_ID
FK
KANAL_ID
FK
UMSATZ
MENGE
UMSATZ_GESAMT
D_VERTRIEBSKANAL
PK
KANAL_ID
VERTRIEBSKANAL
KANALBESCHREIBUNG
VERANTWORTLICH
KLASSE
PK: Btree Index
FK: Bitmap Index
F_Usage_Count
Summe
Spartensätze
pro Tag
Mitarbeiter_Nr
ZEIT_ID
Segment_Nr
Sparten_Owner
Channel_Owner
Region _Owner
Count_Record
Amount_Byte
D_ZEIT
DATUM_ID
TAG_DES_MONATS
TAG_DES_JAHRES
WOCHE_DES_JAHRES
MONATS_NUMMER
MONAT_DESC
QUARTALS_NUMMER
JAHR_NUMMER
ZEIT_ID
D_Table
Segment_Nr
Table_Name
Partition_Name
• Indirektes Dokumentieren von Verursachern für ein
bestimmtes Datenvolumen
• Kriterien aus den Stammdaten ableiten
• Tägliche Messung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
315
Datenwachstum dokumentieren
D_ZEIT
• Täglich Messung
• Wachstumskurve
• Prognosemöglichkeit
• Star Schema analoge
Darstellung
D_Segment
Tabellen_Name
Segment_Name
Segment_Type
Partition_Name
Segment_Nummer
Segment_Nr
Mess_Tag
Anzahl_Zeilen
Anzahl_Byte
Comp_Faktor
Index_Byte
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
WOCHE_DES_JAHRES
Wochen_Tag_Nr
DATUM_Tag
TAG_DES_MONATS
TAG_DES_JAHRES
Mess_Tag
DATA WAREHOUSE
Werden Tabellen und deren Daten genutzt
• Tägliche/stündliche
Messung
• Welche Segmente
(Partitionen) werden genutzt
• History und
Monitoring-Views*
- dba_hist_seg_stat
- dba_hist_seg_stat_obj
- dba_hist_snapshot
- v$sql_Monitor
D_ZEIT
D_Segment
Tabellen_Name
Segment_Name
Segment_Type
Partition_Name
Segment_Nummer
Segment_Nr
Tag_Stunde
D_Org_User
Org_Einheit
Org_Einheit_Nummer
Kostenstelle
User_Name
• Star Schema analoge Darstellung
User_Name
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
WOCHE_DES_JAHRES
Wochen_Tag_Nr
DATUM_Tag
TAG_DES_MONATS
TAG_DES_JAHRES
Tag_Stunde
Logical_Reads_Total
Physical_Reads_Total
Logical_Reads_Delta
Physical_Reads_Delta
• Alternative:
AUDIT select ON table-name
* Siehe passende Skripte in TiF-Skripte-Sammlung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
317
Aufgabenstellungen beim ETL-Monitoring
• Laufzeit-Kontrolle / ETL-Monitoring
– Gelesene/Geschriebene Sätze
– Ressource-Verbrauch (IO und Memory)
– Laufzeit
– Historischen Verlauf dokumentieren
– Trends ableiten
• Zuwachsmenge pro Tabelle
– Historischen Verlauf dokumentieren
• Mengen-Kontrolle
– Beobachtung des tatsächlichen Platzverbrauchs im DWH
– Alerts
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Verbrauchsdaten sammeln
• Mess-Aufruf in der
aktuellen
ETL-Job-Session
als letzten
Aufruf einbauen
• Ergebnis-Daten in
Historien-Tabelle
eintragen
SELECT
/*+ use_nl (e s) ordered */
s.sql_id, s.plan_hash_value,
to_char(s.hash_value),
rawtohex(s.address),
s.sql_text,
s.disk_reads,
s.buffer_gets,
s.executions,
s.sharable_mem,
s.parsing_user_id,
s.sorts,
s.parse_calls,
s.command_type,
s.child_number,
s.parsing_schema_id,
s.rows_processed,
e.username dbuser,
u.name parsing_user,
e.sid,
s.module,
s.action,
s.open_versions,
1 current_set
FROM
v$session e, v$sql s, sys.user$ u
WHERE s.address = e.sql_address AND
s.hash_value = e.sql_hash_value AND
s.child_number = e.sql_child_number AND
u.type# != 2 AND
s.parsing_user_id = u.user#
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
319
Oracle Business
Intelligence Suite
L e g a c y
Oracle Database 12c
Integrierte Modelle Granulares Level
Meist 3NF
Relational Database Oracle 12c
Harmonizing
Data Quality
Data Profiling
Data Governance
ER Modeler
Logical
/Physical
Access
Integriertes Row Level Security
All Data
Data Lake
Concept
Advanced / Predictive Analytics
R + Data Mining
Exadata
Large
Objects
N e w
Stream
Analytics
Human/Trends
Hadoop-Datastore
(HDFS/noSQL)
Iot
Data
Big Data Appliance
Ad-hoc
Analysis
S Q L
Log +
Produktions
Daten
Batch
Realime
Remote
Access
Dashboards
Business Logik
In Memory Oracle 12c
Metadata Repository
IoT
(Dashboards,
Reporting)
Interactive
User View
Published
Reporting
Office
Integration
Mobile
Consumption
Oracle BI Apps
A c c c e s s
D a t a
SAP R/3
SAP BW
Enterprise Layer
S i n g e l e
Alle
Unternehmens
Prozesse
Oracle
Data
Integrator
IoT
Service
Integration
M u l t y Te n a n c i e s
D a t a
Single-Enterprise Analyse-Plattform
Financial
Procurement&Spend
HR, SCM, & OM, PIM
Big Data
Discovery Studio
Find
Explore
In-Memory
Discovery Index
Cloudera Hadoop Distribution
Transform
Discover
Exalytics IM Machine
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
Regeln einer effizienten Data Warehouse Architektur
• Breite Abfragemodelle bereitstellen
• Verbund-Data Marts
• Über Data Mart-Grenzen hinweg gemeinsam
genutzte Berechnungen + Aggregationen usw.
so früh wie möglich umsetzen
• Kern-Warehouse-Schicht schon mit Blick auf
Auswertemodelle / Hierarchien modellieren
• Technisches und fachliches Monitoring von
Inhalten und System
• Kompaktes Schichtenmodell schaffen
– Data Marts so weit wie möglich virtualisieren
– Redundanzen in unterschiedlichen Schichten vermeiden
– Berechnungen / Aggregate so früh wie möglich durchführen
• Zusammenhängende Data Mart-Schicht
• Alle Schichten in einem DB-Raum
• In-Database-Aktivitäten (Prüfen/Laden)
• 1:1 Kopien verhindern
• Permanente Orientierung an den
Informationsbedürfnissen der Benutzer
– Mehrfachnutzung von Dimensionen / Conformed
Dimensions
– Geschickter Umgang mit sehr großen Faktentabellen /
Vermeiden unnötiger Kopien
– Eher granulare Informationen auch in den Fakten-Tabellen
• Dokumentation aller Informationen Im Warehouse
(Business Glossar)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
DATA WAREHOUSE
322