|
Approximate Funktionen in der Oracle Datenbank
Datenanalyseanwendungen verwenden in hohem Maße aggregierte Funktions- und Analysefunktionsabfragen.
Aggregationsfunktionen und Analysefunktionen erfordern dabei das Sortieren großer Datenmengen, zudem erfordert die genaue Beantwortung von Anfragen viel Speicherplatz und
kann zeitaufwendig sein. Um die analytische Abfrage auf großen Datenmengen zu beschleunigen, sind daher spezielle SQL Funktionen - die sogenannten "Approximate" Funktionen -
eingeführt worden. Die "Approximate" Abfrageverarbeitung besteht aus einer Reihe von Optimierungstechniken, die analytische Abfragen beschleunigen, indem sie Ergebnisse innerhalb eines
akzeptablen Fehlerbereichs berechnen. Dabei werden die Ergebnisse extrem schnell mit einem hohen Maß an Genauigkeit geliefert ohne übermäßige Ressourcenverwendung.
Ein Beispiel stellt die in Oracle Database 12.2 eingeführte Funktion APPROX_COUNT_DISTINCT dar, die wie der Name schon vermuten lässt, eine Alternative zu COUNT(DISTINCT) ist.
Die Nutzung ohne Änderung des existierenden Codes zu ermöglichen, ist dabei immer ein wichtiges Ziel. So gibt es neue Session- bzw. System - Initialisierungsparameter, die
intern die exakte Funktion einfach durch die korrespondierende "Approximate" Funktion ersetzt - ohne manuelles Zutun.
In den folgenden Abschnitten werden die neuen "Approximate" Funktionalitäten am Beispiel der Funktion COUNT demonstriert.
COUNT (DISTINCT) oder APPROX_COUNT_DISTINCT
Schon seit Oracle Database Release 12.1 existiert die Funktion APRROX_COUNT_DISTINCT, die eine Alternative zu COUNT(DISTINCT) darstellt. APPROX_COUNT_DISTINCT verarbeitet große Datenmengen deutlich schneller als COUNT (DISTINCT) wie man bei der Analyse der beiden Ausführungspläne erkennen kann - mit vernachlässigbarer Abweichung vom exakten Ergebnis. Um die Funktionalität zu zeigen, legen wir eine einfache Hilfstabelle an und fügen Zeilen wie folgt ein.
SQL> create table approx_t ( t number, t1 varchar2(1));
Table created.
SQL> insert into approx_t
select rownum, 'x' from dual connect by level <1000000
union
select rownum, 'a' from dual connect by level <2000000
union
select rownum, 'b' from dual connect by level <3000000;
5999997 rows created.
Nun wird die Anzahl der unterschiedlichen Zahlen pro Wert der Spalte T1 (hier a,b und x) ausgegeben - einmal die exakte Berechnung und einmal der ungefähre Wert. Die Berechnung mit APPROXIMATE_COUNT_DISTINCT ist zwar etwas ungenauer, deshalb aber schneller.
SQL> select t1, count(distinct(t)) from approx_t group by t1; T COUNT(DISTINCT(T)) - ------------------ b 2999999 a 1999999 x 999999 Elapsed: 00:00:03.00 SQL> select t1, approx_count_distinct(t) from approx_t group by t1; T APPROX_COUNT_DISTINCT(T) - ------------------------ b 2944730 a 1939328 x 971092 Elapsed: 00:00:01.10
Um den Unterschied in den beiden Ausführungen zu erkennen, nutzen wir SQL Developer. Hier kann man im SQL Worksheet zwei Ausführungspläne einfach vergleichen. Der erste Ausführungsplan besteht nicht nur aus weniger Operationen sondern verwendet eine spezielle Operation HASH (GROUP BY APPROX), die extra für diese Art von Ausführung optimiert wurde.
Um den Einsatz auch ohne Änderung des existierenden Codes zu ermöglichen, gibt es mit 12.2 den Initialisierungsparameter APPROX_FOR_COUNT_DISTINCT. Wird dieser aktiviert, so wird die exakte Funktion einfach durch die korrespondierende "Approximate" Funktion ersetzt.
SQL> alter session set APPROX_FOR_COUNT_DISTINCT =true; Session altered. SQL> select t1, count(distinct(t)) from approx_t group by t1; T COUNT(DISTINCT(T)) - ------------------ b 2944730 a 1939328 x 971092
Approximate Count (Distinct) mit Aggregationen
Häufig gibt es aber auch die Anforderung, nicht nur die Anzahl zu berechnen, sondern verschiedene Aggregationen über Massendaten durchzuführen.
Hierzu sind in Oracle Database 12.2 die analytischen Funktionen APPROX_COUNT_DISTINCT_DETAIL, APPROX_COUNT_DISTINCT_AGG und TO_APPROX_COUNT_DISTINCT eingeführt worden.
Sie helfen dabei ressourcenintensive Näherungszahlberechnungen einmalig durchzuführen, die resultierenden Details zu speichern und anschließend effiziente
Aggregationen und Abfragen zu diesen Details durchzuführen.
APPROX_COUNT_DISTINCT_DETAIL(expr) dient hierbei als eine Art Helper Funktion, die Näherungswerte für expr berechnet und einen BLOB-Wert, das sogenannte "Detail", in
einem speziellen Format - eine Art Bitmap - zurückliefert. Dies könnten hochgranulare Informationen zu demographische Zählungen in Städten oder tägliche Verkaufszahlen sein.
Die Berechnung des Details speichert man dann am Besten in einer Ergebnistabelle oder (materialisierten) View, um in weiteren Aggregats Abfragen die Detail Berechnungen verwenden zu können und performante Ausführungen zu erhalten.
Im folgenden nutzen wir die Tabelle SH.SALES und SH.TIMES um an einem Beispiel die Funktionsweise zu demonstrieren. Die Aufgabe besteht darin, Aggregierungen nach Verkaufszahlen pro Jahr,
pro Monat oder pro Tag vornehmen zu können. Dazu erzeugen wir einen View, die die Detail-Informationen für jede Gruppierung (Jahr, Monat und Tag) enthält.
SQL> CREATE VIEW daily_prod_count AS
SELECT t.calendar_year year,
t.calendar_month_number month,
t.day_number_in_month day,
APPROX_COUNT_DISTINCT_DETAIL(s.prod_id) daily_detail
FROM sh.times t, sh.sales s
WHERE t.time_id = s.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
SQL> select * from daily_prod_count where rownum=1;
YEAR MONTH DAY
---------- ---------- ----------
DAILY_DETAIL
--------------------------------------------------------------------------------
2000 1 1
0D0C0F85000100000000000000010000000000000001000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
Um die Informationen aus der Detail Spalte lesbar zu machen, kann man die Funktion TO_APPROX_COUNT_DISTINCT verwenden. Zuvor verwenden wir noch zusätzlich die neue Aggregierungsfunktion APPROX_COUNT_DISTINCT_AGG um beispielsweise die Verkaufszahlen pro Jahr zu erhalten.
SQL> SELECT year,
TO_APPROX_COUNT_DISTINCT(APPROX_COUNT_DISTINCT_AGG(daily_detail)) yearly_detail
FROM daily_prod_count
GROUP BY year order by year;
YEAR YEARLY_DETAIL
---------- -------------
1998 253
1999 294
2000 320
Eine weitere Detail Abfrage, die damit durchgeführt werden könnte, ist die Ausgabe von Verkaufszahlen pro Monat oder pro Tag. Der Ausführungsplan weist auch hier eine entsprechende "Approximate" Optimierung aus.
SQL> SELECT year, month,
TO_APPROX_COUNT_DISTINCT(APPROX_COUNT_DISTINCT_AGG(daily_detail)) monthly_detail
FROM daily_prod_count
GROUP BY year, month order by year, month;
YEAR MONTH MONTHLY_DETAIL
---------- ---------- --------------
1998 1 19
1998 2 19
1998 3 24
1998 4 21
...
COUNT und Top N Abfragen
Die Idee weitere Näherungsfunktionen zur Verfügung zu stellen, um die Performance bei Berechnungen speziell im Massendatenumfeld zu erhöhen, wurde auch in 18c
weiterverfolgt. Neu in 18c ist eine analytische Funktion, die die (ungefähre) Anzahl eines Ausdrucks in einer Gruppe von Werten berechnet - nämlich APPROX_COUNT.
Die Idee ist dabei sich auf die Top n der häufigsten Werte zu beschränken. Dazu ist in 18c die Approximate Ranking Funktion APPROX_RANK eingeführt worden, die den Rang
in einer Gruppe von Werten ermittelt.
Nehmen wir als Beispiel die Top n Berechnung der Objekttypen von Oracle User. Die Frage, die beantwortet werden soll, ist folgende: Pro User werden in der Tabelle ALL_OBJECTS
die vorkommenden Objekttypen gezählt. Interessant sind dabei nur die Top 3 Objekttypen und ihre Anzahl pro User zu ermitteln. Wir beschränken uns aus Gründen der
Übersichtlichkeit auf eine Teilmenge der User. Um die Funktionsweise zu demonstrieren, erzeugen wir eine einfache Testtabelle.
SQL> create table test_objects as
select * from all_objects where owner like 'A%';
Table created.
Die Verwendung von APPROX_COUNT sieht dabei anders aus, als bei den zuvor beschriebenen Varianten. Um APPROX_COUNT zu verwenden, ist es unbedingt erforderlich einer bestimmten Syntax zu folgen,
ansonsten erhält man Fehlermeldungen. Die Syntax lautet dabei:
SELECT expr1[, expr2,... ], APPROX_COUNT(expr) , ...
FROM table_name WHERE ...
GROUP BY expr1[, expr_2,...]
HAVING APPROX_RANK(PARTITION BY partition_by_clause ORDER BY APPROX_COUNT(expr) DESC) <= N
Die Idee dabei ist, eine Einschränkung in der HAVING Klausel mit der APPROX_RANK Funktion vorzunehmen. Die Klausel partition_by_clause enthält eine Teilmenge der GROUP BY Spalten. Die Funktion APPROX_RANK gibt dann den Rang innerhalb dieser Gruppe zurück. Folgende Ausgabe zeigt das Ergebnis. Offensichtlich sind Indizes, Trigger und Tabellen die häufigsten Objekttypen im Schema APEX_050100.
SQL> set linesize window
SQL> col owner format a30
SQL> select owner, object_type, approx_count(*) , approx_rank(partition by owner order by approx_count(*) desc) rang
from test_objects
group by owner, object_type
having approx_rank(partition by owner order by approx_count(*) desc) <= 3
order by 1,3;
OWNER OBJECT_TYPE APPROX_COUNT(*) RANG
------------------------------ ----------------------- --------------- ----------
APEX_050100 TABLE 442 3
APEX_050100 TRIGGER 501 2
APEX_050100 INDEX 1337 1
APEX_LISTENER SYNONYM 22 1
APPQOSSYS SYNONYM 1 2
APPQOSSYS TABLE 5 1
AUDSYS VIEW 6 3
AUDSYS TABLE PARTITION 8 2
AUDSYS INDEX PARTITION 24 1
9 rows selected.
Die exakte Berechnung ohne Einschränkung auf die Top 3 ergibt folgendes Resultat.
SQL> select owner, object_type, count(*) zahl
from test_objects
group by owner, object_type
order by 1,3;
OWNER OBJECT_TYPE ZAHL
------------------------------ ----------------------- ----------
APEX_050100 SEQUENCE 3
APEX_050100 SYNONYM 3
APEX_050100 JOB 4
APEX_050100 TYPE BODY 5
APEX_050100 FUNCTION 8
APEX_050100 PROCEDURE 13
APEX_050100 TYPE 14
APEX_050100 PACKAGE BODY 272
APEX_050100 PACKAGE 279
APEX_050100 VIEW 283
APEX_050100 TABLE 442
APEX_050100 TRIGGER 501
APEX_050100 INDEX 1337
APEX_LISTENER SYNONYM 22
APPQOSSYS SYNONYM 1
APPQOSSYS TABLE 5
AUDSYS PACKAGE 1
AUDSYS PACKAGE BODY 1
AUDSYS LIBRARY 1
AUDSYS TABLE 1
AUDSYS VIEW 6
AUDSYS TABLE PARTITION 8
AUDSYS INDEX PARTITION 24
23 rows selected.
Wie man erkennen kann ist das "Approximate" Resultat in unserem Fall sogar exakt. Die Anzahl der maximalen Fehler zwischen genauem und angenähertem Wert kann bei APPROX_COUNT sogar ermittelt werden. Im zweiten Argument muß dann der Eintrag MAX_ERROR mitgegeben werden.
SQL> select owner, object_type, approx_count(*, 'MAX_ERROR') , approx_rank(partition by owner order by approx_count(*) desc) rang 2 from test_objects 3 group by owner, object_type 4 having approx_rank(partition by owner order by approx_count(*) desc) <= 3 5 order by 1 6 / OWNER OBJECT_TYPE APPROX_COUNT(*,'MAX_ERROR') RANG ------------------------------ ----------------------- --------------------------- ---------- APEX_050100 TABLE 0 3 APEX_050100 TRIGGER 0 2 APEX_050100 INDEX 0 1 APEX_LISTENER SYNONYM 0 1 APPQOSSYS SYNONYM 0 2 APPQOSSYS TABLE 0 1 AUDSYS VIEW 0 3 AUDSYS TABLE PARTITION 0 2 AUDSYS INDEX PARTITION 0 1 9 rows selected.
Hinweise
Wie eingangs schon erwähnt, gibt es noch weitere neue Funktionen im Zusammenhang mit Detail Berechnungen wie zum Beispiel die PERCENTILE und MEDIAN Funktion (also APPROX_PERCENTILE_DETAIL, APPROX_PERCENTILE_DETAIL und TO_APPROX_PERCENTILE), die ähnlich wie im Fall APPROX_COUNT_DISTINCT verwendet werden. In 18c ist zudem auch eine APPROX_SUM Funktion eingeführt worden, die wie APPROX_COUNT angewendet werden muss. Weitere Informationen zu den Themen kann man in den Handbüchern oder aber auch in den Live SQL Tutorials nachlesen.
Lizenzierung
Die "Approximate" Funktionen stehen in allen Datenbank Editionen On-Premise und in der Cloud zur Verfügung.
Weitere Informationen
Zurück zur Community-Seite