Nützliche Oracler 12c Features für Data Warehousing

Nützliche Oracle 12c Features
für Data Warehousing
DOAG BI, 8. Juni 2016
Dani Schnider, Trivadis AG
BASEL BERN BRUGG DÜSSELDORF
HAMBURG COPENHAGEN LAUSANNE
FRANKFURT A.M. FREIBURG I.BR. GENEVA
MUNICH STUTTGART VIENNA ZURICH
Dani Schnider
Principal Consultant und
DWH/BI Lead Architect
bei Trivadis in Zürich
  Kursleiter verschiedener
Trivadis-Kurse
  Co-Autor des Buches «Data
Warehousing mit Oracle –
Business Intelligence in der
Praxis»
2
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Oracle 12c bringt viele Neuerungen – auch für DWH
Data Redaction
In-Memory Option
APPROX_COUNT_DISTINCT
Temporal Validity
SQL Query Row Limits
Adaptive Query Optimization
UTL_CALL_STACK
IDENTITY Columns
Asynchronous Global Index Maintanance
SQL Pattern Matching
PL/SQL in SQL WITH Clause
Vector Transformation
08.06.2016
Multitenant Databases
Online Statistics Gathering
JSON Support
Partial Indexing
Information Lifecycle Management
Out-of-Place Materialized Views
3
Invisible Columns
Default Values Enhancements
Nützliche Oracle 12c Features für Data Warehousing
Online Statistics Gathering
4
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
ETL-Prozesse und Statistiken
DBMS_STATS.gather_table_stats
(ownname => 'STG'
,tabname => 'T1');
T1
2000
ETL-Mapping
T3
1500
T2
DBMS_STATS.gather_table_stats
(ownname => 'STG'
,tabname => 'T2');
3000
|
|
|
|
|
1
2
3
4
5
| INSERT STATEMENT
|
| 1500 |
| INSERT
| T3 | 1500 |
|
HASH JOIN
|
| 1500 |
|
TABLE ACCESS FULL| T1 | 2000 |
|
TABLE ACCESS FULL| T2 | 3000 |
Siehe Vortrag „So beschleunigen Sie Ihre ETL-Prozesse“ (DOAG BI 2015)
5
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Online Statistics Gathering
  Nach dem Laden einer Tabelle sollten immer Statistiken berechnet werden
  Ab Oracle 12c funktioniert dies automatisch in folgenden Fällen:
–  CREATE TABLE AS SELECT
CREATE TABLE T3 AS
SELECT ... FROM T1 JOIN T2 ON ...
–  Direct-Load INSERT in leere Tabelle (nach TRUNCATE)
INSERT /*+ append */ INTO T3
SELECT ...
FROM T1 JOIN T2 ON ...
6
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Online Statistics Gathering
Anwendungsfälle:
  Zwischentabellen in ETL-Ablauf (Staging Area, Cleansing Area)
  Hilfstabellen für Zwischenresultate von Ladeprozessen
Einschränkungen:
  Keine Index-Statistiken
  Keine Histogramme
  Keine Statistiken auf Partitionen und Subpartitionen
Siehe: https://danischnider.wordpress.com/2015/12/23/online-statistics-gathering-in-oracle-12c/
7
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Default Values
8
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Fehlende Attribute durch „Singletons“ ersetzen
STG_PRODUCTS
CLS_PRODUCTS
Edradour 10 years
Glenfarclas 105
Black Bowmore 1964
NULL
Laphroaig 15 years
Edradour 10 years
Glenfarclas 105
Black Bowmore 1964
Unknown
Laphroaig 15 years
INSERT
SELECT
,
FROM
INTO cls_products (product_code, product_desc)
product_code
NVL(product_desc, 'Unknown')
stg_products;
Siehe Vortrag „Fehlertolerante Ladeprozesse in Oracle“ (DOAG BI 2012)
9
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
DEFAULT Erweiterungen
  DEFAULT ON NULL wird verwendet, wenn NULL eingefügt wird
  DEFAULT kann eine Sequence sein (endlich!)
CREATE TABLE
(dwh_id
,whisky_code
,whisky_name
,price
,age
,distillery
,region
10
08.06.2016
dwh_whisky
NUMBER(8)
VARCHAR2(8)
VARCHAR2(40)
NUMBER (6,2)
VARCHAR2(3)
VARCHAR2(30)
VARCHAR2(30)
DEFAULT seq_whisky.NEXTVAL
NOT NULL
NOT NULL
DEFAULT ON NULL 0
DEFAULT ON NULL '< 7'
DEFAULT ON NULL 'Unknown Distillery'
DEFAULT ON NULL 'Unknown Region')
Nützliche Oracle 12c Features für Data Warehousing
IDENTITY Columns
  Automatische Vergabe von Sequenznummern („auto increment column“)
  Im Hintergrund wird eine Sequence erstellt (ISEQ$$_nnnnn)
CREATE TABLE
(dwh_id
,whisky_code
,whisky_name
,...)
dwh_whisky
NUMBER(8)
GENERATED BY DEFAULT AS IDENTITY
VARCHAR2(8) NOT NULL
VARCHAR2(40) NOT NULL
  GENERATED BY DEFAULT AS IDENTITY
  GENERATED BY DEFAULT ON NULL AS IDENTITY
  GENERATED ALWAYS AS IDENTITY
11
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
APPROX_COUNT_DISTINCT
12
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Funktion APPROX_COUNT_DISTINCT
Anzahl unterschiedliche Kunden in SALES Tabelle:
SELECT COUNT(DISTINCT cust_id) FROM sales
Ungefähre Anzahl unterschiedliche Kunden in SALES Tabelle:
SELECT APPROX_COUNT_DISTINCT(cust_id) FROM sales
Neue Funktion APPROX_COUNT_DISTINCT
  Gleicher Algorithmus wie bei AUTO_SAMPLE_SIZE in DBMS_STATS (Oracle 11g)
  Für grosse Datenmengen schneller als COUNT(DISTINCT)
  Resultat nur ungefähr (+/- 4%)
13
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
APPROX_COUNT_DISTINCT: Performance
Quelle: https://antognini.ch/2014/10/the-approx_count_distinct-function-a-test-case/
14
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
APPROX_COUNT_DISTINCT: Genauigkeit
Quelle: https://antognini.ch/2014/10/the-approx_count_distinct-function-a-test-case/
15
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Partial Indexing
16
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Partial Indexing – Anwendungsfälle
  Auf aktuellen Daten werden häufiger selektive Abfragen gemacht
  Index auf neuster Partition soll erst nach Abschluss des Ladens erstellt werden
  Partitionierung nach Statuswerten mit unterschiedlichen Abfragen
17
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Erstellen von Tabelle mit Partial Indexing
  Default auf Tabellenebene: INDEXING ON / OFF
  Kann pro Partition überschrieben werden
CREATE TABLE t_part (n NUMBER,
INDEXING OFF
PARTITION BY RANGE (n)
(PARTITION p1 VALUES LESS THAN
,PARTITION p2 VALUES LESS THAN
,PARTITION p3 VALUES LESS THAN
,PARTITION p4 VALUES LESS THAN
,PARTITION p5 VALUES LESS THAN
,PARTITION p6 VALUES LESS THAN
)
18
08.06.2016
name VARCHAR2(40))
(100)
(200) INDEXING OFF
(300)
(400)
(500) INDEXING ON
(600) INDEXING ON
Nützliche Oracle 12c Features für Data Warehousing
Erstellen von Partial Indexes
Partial Local Index
CREATE INDEX idx_part_local
ON t_part (name)
LOCAL INDEXING PARTIAL
Partial Global Index
CREATE INDEX idx_part_local
ON t_part (name)
[GLOBAL] INDEXING PARTIAL
19
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Partial Local Index
Index
Partition P1
Index
Partition P2
Index
Partition P3
Index
Partition P4
Index
Partition P5
Index
Partition P6
Table
Partition
P1
Table
Partitition
P2
Table
Partitition
P3
Table
Partitition
P4
Table
Partitition
P5
Table
Partitition
P6
INDEXING OFF
20
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
INDEXING ON
Partial Global Index
Global Index
Table
Partition
P1
Table
Partitition
P2
Table
Partitition
P3
Table
Partitition
P4
INDEXING OFF
21
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Table
Partitition
P5
Table
Partitition
P6
INDEXING ON
Asynchronous Global Index
Maintenance
22
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Globale Indizes im Data Warehouse
  DROP / TRUNCATE PARTITION setzt globale Indizes auf UNUSABLE
  Index Rebuild notwendig
  Problematisch bei rollenden Zeitfenstern (alte Partitionen werden gelöscht)
! Globale Indizes im DWH möglichst vermeiden
…
…
23
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Asynchrones Nachführen von globalen Indizes
In Oracle 12c können globale Indizes asynchron aktualisiert werden:
  Schritt 1: Partition löschen (DROP oder TRUNCATE PARTITION)
ALTER TABLE sales DROP PARTITION p_2016_02 UPDATE INDEXES
  Index bleibt gültig, enthält aber „Orphaned Entries“
SELECT index_name, status, orphaned_entries
WHERE index_name = 'SALES_PK'
INDEX_NAME
STATUS
ORPHANED_ENTRIES
-------------- -------- ---------------SALES_PK
VALID
YES
24
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Asynchrones Nachführen von globalen Indizes
  Schritt 2: Orphaned Entries aus Index löschen. Varianten:
–  Scheduler Job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
–  dbms_part.cleanup_gidx
–  ALTER INDEX REBUILD [PARTITION]
–  ALTER INDEX [PARTITION] COALESCE CLEANUP
dbms_part.cleanup_gidx(schema_name_in => USER,
table_name_in => 'SALES’)
25
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Müssen die Orphans überhaupt gelöscht werden?
  Ja
Orphans bleiben im Index und werden nicht überschrieben
  Index wächst, da Speicherplatz nicht freigegeben wird
  Ausnahme: Unique Indexes, falls gleicher Eintrag wieder eingefügt wird
Siehe Richard Foote‘s Oracle Blog
https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/
https://richardfoote.wordpress.com/2013/08/06/12c-asynchronous-global-index-maintenance-part-ii-the-space-between/
https://richardfoote.wordpress.com/category/asynchronous-global-index-maintenance/
26
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Vector Transformation
27
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Vector Transformation
  Phase 1 (für jede Dimension mit Filterkriterien)
1.  Scan auf Dimensionstabelle (inkl. Filterung der Daten)
2.  Ermittlung von Key Vector
3.  Aggregation der Daten (In-Memory Accumulator)
4.  Erstellen von temporärer Tabelle
  Phase 2
5.  Full Table Scan auf Faktentabelle, Filterung anhand von Key Vectors
6.  Aggregation mittels HASH GROUP BY / VECTOR GROUP BY
7.  Join auf temporäre Tabellen (Join Back)
8.  Ev. Join von weiteren Dimensionen (ohne Filterkriterien)
28
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
Vector Transformation
FACTS
DIM1
11
12
13
14
15
16
17
18
Alpha
Alpha
Beta
Beta
Beta
Gamma
Delta
Delta
DIM2
21
22
23
24
25
26
29
08.06.2016
X
X
Y
Y
Y
Z
green
blue
green
blue
red
red
11
11
12
12
12
13
14
14
14
14
15
15
15
16
16
17
17
18
18
18
Nützliche Oracle 12c Features für Data Warehousing
22
24
21
22
24
22
21
24
25
26
23
24
26
22
23
22
25
21
24
26
1000
1200
300
3200
700
1100
2000
800
1600
700
1100
1200
500
2400
800
1300
1100
900
2100
600
SELECT
,
FROM
JOIN
JOIN
WHERE
D1, D21, D22
SUM(FACTS.F)
FACTS
DIM1 ON (...)
DIM2 ON (...)
D1 IN ('Beta',
'Gamma')
AND D21 = 'Y'
GROUP BY D1, D21, D22
Vector Transformation
FACTS
DIM1
11
12
13
14
15
16
17
18
KV1
Alpha
Alpha
Beta
Beta
Beta
Gamma
Delta
Delta
DIM2
21
22
23
24
25
26
30
08.06.2016
X
X
Y
Y
Y
Z
0
0
1
1
1
2
0
0
KV2
green
blue
green
blue
red
red
0
0
1
2
3
0
TMP1
1
2
Beta
Gamma
TMP2
1 Y green
2 Y blue
3 Y red
11
11
12
12
12
13
14
14
14
14
15
15
15
16
16
17
17
18
18
18
Nützliche Oracle 12c Features für Data Warehousing
22
24
21
22
24
22
21
24
25
26
23
24
26
22
23
22
25
21
24
26
1000
1200
300
3200
700
1100
2000
800
1600
700
1100
1200
500
2400
800
1300
1100
900
2100
600
0
0
0
0
0
1
1
1
1
1
1
1
1
2
2
0
0
0
0
0
0
2
0
0
2
0
0
2
3
0
1
2
0
0
1
0
3
0
2
0
Beta
Beta
Beta
Gamma
Y
Y
Y
Y
green 1100
blue 2000
red
1600
green 800
------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 | TEMP TABLE TRANSFORMATION
|
|
|
2 |
LOAD AS SELECT
| SYS_TEMP_0FD9D662E_84B3F4 |
|
3 |
VECTOR GROUP BY
|
|
|
4 |
KEY VECTOR CREATE BUFFERED| :KV0000
|
|* 5 |
TABLE ACCESS FULL
| PRODUCTS
|
|
6 |
LOAD AS SELECT
| SYS_TEMP_0FD9D662F_84B3F4 |
|
7 |
VECTOR GROUP BY
|
|
|
8 |
KEY VECTOR CREATE BUFFERED| :KV0001
|
|* 9 |
TABLE ACCESS FULL
| CUSTOMERS
|
| 10 |
HASH GROUP BY
|
|
|* 11 |
HASH JOIN
|
|
| 12 |
MERGE JOIN CARTESIAN
|
|
| 13 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D662E_84B3F4 |
| 14 |
BUFFER SORT
|
|
| 15 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D662F_84B3F4 |
| 16 |
VIEW
| VW_VT_83032D7B
|
| 17 |
VECTOR GROUP BY
|
|
| 18 |
HASH GROUP BY
|
|
| 19 |
KEY VECTOR USE
| :KV0000
|
| 20 |
KEY VECTOR USE
| :KV0001
|
| 21 |
PARTITION RANGE ALL |
|
|* 22 |
TABLE ACCESS FULL
| SALES
|
------------------------------------------------------------------31
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
1
2
3
------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 | TEMP TABLE TRANSFORMATION
|
|
|
2 |
LOAD AS SELECT
| SYS_TEMP_0FD9D6696_84B3F4 |
|
3 |
VECTOR GROUP BY
|
|
|
4 |
KEY VECTOR CREATE BUFFERED
| :KV0000
|
|* 5 |
TABLE ACCESS INMEMORY FULL
| PRODUCTS
|
|
6 |
LOAD AS SELECT
| SYS_TEMP_0FD9D6697_84B3F4 |
|
7 |
VECTOR GROUP BY
|
|
|
8 |
KEY VECTOR CREATE BUFFERED
| :KV0001
|
|* 9 |
TABLE ACCESS INMEMORY FULL
| CUSTOMERS
|
| 10 |
HASH GROUP BY
|
|
|* 11 |
HASH JOIN
|
|
| 12 |
MERGE JOIN CARTESIAN
|
|
| 13 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6696_84B3F4 |
| 14 |
BUFFER SORT
|
|
| 15 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6697_84B3F4 |
| 16 |
VIEW
| VW_VT_83032D7B
|
| 17 |
VECTOR GROUP BY
|
|
| 18 |
HASH GROUP BY
|
|
| 19 |
KEY VECTOR USE
| :KV0000
|
| 20 |
KEY VECTOR USE
| :KV0001
|
| 21 |
PARTITION RANGE ALL
|
|
|* 22 |
TABLE ACCESS INMEMORY FULL| SALES
|
------------------------------------------------------------------------32
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing
1
2
3
Vielen Dank.
Dani Schnider
Principal Consultant
Tel. +41 58 459 50 81
[email protected]
33
08.06.2016
Nützliche Oracle 12c Features für Data Warehousing