WWW.BOOK.DISLIB.INFO
FREE ELECTRONIC LIBRARY - Books, dissertations, abstract
 
<< HOME
CONTACTS



Pages:   || 2 |

«IN versus EXISTS oder doch ein Join? Andrej Pashchenko Trivadis GmbH Düsseldorf Schlüsselworte Unterabfrage, Subquery, Semi-Join, Anti-Join, ...»

-- [ Page 1 ] --

IN versus EXISTS oder doch ein Join?

Andrej Pashchenko

Trivadis GmbH

Düsseldorf

Schlüsselworte

Unterabfrage, Subquery, Semi-Join, Anti-Join, Unnesting, Performance, Best Practice

Einleitung

Wenn man über das Schreiben effizienter SQL-Abfragen spricht, gibt es kaum ein Thema, das

dermaßen mit Vorurteilen besetzt ist, wie der Einsatz von Unterabfragen mit (NOT) IN oder (NOT)

EXISTS. Die Meinungen und Erfahrungen von SQL-Entwicklern gehen häufig ziemlich auseinander.

Das Spektrum reicht von unflexiblem Einsatz nur einer bestimmten Variante über Auswahl der Methode nach bestimmten Best Practices bis hin zu Verzicht von Unterabfragen und deren Ersatz durch einen Join. Mit jeder neuen Datenbankversion macht der Oracle Optimizer einen immer besseren Job. Sind die Best Practices von gestern noch aktuell und anwendbar? Kann man überhaupt eine allgemeingültige Best Practice ausformulieren? Im Vortrag wird auf die Themen wie Subquery Unnesting, Semi- und Anti-Joins, deren Unterschied zu klassischen Joins, Join-Methoden und Performance eingegangen.

Semi-Joins Bevor wir uns den Performance-Aspekten widmen, schauen wir uns zunächst ein paar theoretische Grundlagen an. Es ist wichtig zu verstehen, welche semantische Bedeutung die Abfragen haben und als Folge, ob und unter welchen Bedingungen sie austauschbar sind.

SQL-Abfragen mit Unterabfragen, die durch IN oder EXISTS ausgedrückt sind, stellen einen sogenannten Semi-Join dar. Der Unterschied zu einem herkömmlichen Join von zwei Tabellen - der linken und der rechten - besteht darin, dass aus der linken Tabelle höchstens ein Datensatz zurückgeliefert werden kann, unabhängig von der Anzahl der Treffer in der rechten Tabelle.

Außerdem können aus der rechten Tabelle keine Daten selektiert werden: sie dient ausschließlich zur Einschränkung der Ergebnisse.

Wir werden unsere Beispiele auf dem mit einer Oracle Datenbank mitgelieferten Beispielschema SH (Sales History) aufbauen. Der einzige kleine Unterschied: Die Tabelle SALES wurde für die Zwecke dieses Artikels ohne Partitionen neu angelegt. Dies macht die Ausführungspläne deutlich kompakter und besser lesbar.

Szenario: Wir müssen folgende Frage beantworten: Wie viele Kunden aus Köln haben bereits Bestellungen aufgegeben? Im Listing 1 stellen wir mögliche SQL-Abfragen zusammen, die diese Frage beantworten sollen.

--A SELECT c.cust_last_name, c.cust_first_name, c.cust_id FROM sh.customers c WHERE c.cust_city = 'Koeln' AND EXISTS (SELECT 1 FROM sh.sales s WHERE c.cust_id = s.cust_id );

--B SELECT c.cust_last_name, c.cust_first_name, c.cust_id FROM sh.customers c WHERE c.cust_city = 'Koeln' AND c.cust_id IN (SELECT s.cust_id FROM sh.sales s );

--C SELECT c.cust_last_name, c.cust_first_name, c.cust_id FROM sh.customers c JOIN sh.sales s ON (c.cust_id = s.cust_id) WHERE c.cust_city = 'Koeln';

--D SELECT DISTINCT c.cust_last_name, c.cust_first_name, c.cust_id FROM sh.customers c JOIN sh.sales s ON (c.cust_id = s.cust_id) WHERE c.cust_city = 'Koeln';

Die Abfragen A und B aus dem Listing 1 sind semantisch identisch. Der Join in der Abfrage C liefert dagegen mehr Datensätze zurück als nötig, und zwar, wenn einige Kunden aus Köln mehrere Käufe getätigt haben. Um die Semantik von den Abfragen A und B beizubehalten, benötigt man noch eine DISTINCT-Operation: die Abfrage D ist dann wieder semantisch gleich.

Wenn man sich vorstellen würde, dass die Ausführung einer SQL-Abfrage eins zu eins der Syntax folgen wird, erscheinen die Abfragen A und B auf den ersten Blick im Vergleich zum Join sehr ineffizient. Das könnte auch der Grund für viele Vorurteile sein. Die Unterabfrage wird einmal für jeden Datensatz aus der Tabelle CUSTOMERS ausgeführt.

Wir können dieses Verhalten durch den Optimizer-Hint NO_UNNEST erzwingen. Dabei verbieten

wir dem Optimizer, weitere Optimierungen vorzunehmen. Der Ausführungsplan sieht dann so aus:

SQL SELECT c.cust_last_name, c.cust_first_name, c.cust_id 2 FROM sh.customers c 3 WHERE c.cust_city = 'Koeln' 5 AND EXISTS (SELECT /*+ no_unnest */ 1 6 FROM sh.sales s 7 WHERE c.cust_id = s.cust_id );

...

44 rows selected.

SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, 0, 'ALLSTATS LAST'));

...

-------------------------------------------------------------------------------Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |

-------------------------------------------------------------------------------SELECT STATEMENT | | 1| | 44 |00:01:32.64 | |* 1 | FILTER | | 1| | 44 |00:01:32.64 | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 1| 13 | 532 |00:00:00.12 | |* 3 | TABLE ACCESS FULL| SALES | 532 | 2| 44 |00:01:32.42 |

-------------------------------------------------------------------------------

<

Listing 2:Korrelierte Unterabfrage ohne Unnesting

Dabei kann man folgendes erkennen. Es sind insgesamt 532 Kunden aus Köln („A-Rows“ in Zeile 2).

Die Unterabfrage wurde einmal für jeden Datensatz aus CUSTOMERS gestartet („Starts“ in Zeile 3).

Der Ausführungsplan für die Abfrage B sieht übrigens exakt gleich aus. Wie verhält sich der Join?

SQL SELECT DISTINCT c.cust_last_name, c.cust_first_name, c.cust_id 2 FROM sh.customers c JOIN sh.sales s ON (c.cust_id = s.cust_id) 3 WHERE c.cust_city = 'Koeln';





...

44 rows selected.

SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, 0, 'ALLSTATS LAST'));

...

--------------------------------------------------------------------------------Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |

--------------------------------------------------------------------------------SELECT STATEMENT | | 1| | 44 |00:00:14.86 | | 1 | HASH UNIQUE | | 1| 1099 | 44 |00:00:14.86 | |* 2 | HASH JOIN | | 1| 1716 | 4208 |00:00:14.82 | |* 3 | TABLE ACCESS FULL| CUSTOMERS | 1| 13 | 532 |00:00:00.05 | | 4| TABLE ACCESS FULL| SALES | 1| 918K| 918K|00:00:03.86 |

--------------------------------------------------------------------------------

<

Listing 3: Klassischer Join

Der CBO hat sich hier für einen Hash-Join entschieden. Die Laufzeit ging von 1,5 Minuten auf knapp 15 Sekunden herunter. Also ist der klassische Join tatsächlich die bessere Wahl? Nein! Denn meistens ist der Optimizer in der Lage, ineffiziente Ausführungspläne, wie im Listing 2, auch bei IN- oder EXISTS-Unterabfragen zu vermeiden.

Subquery Unnesting

Bevor der Optimizer zum Auswerten der Zugriffsmethoden und Kosten kommt, führt er mögliche Query Transformationen durch, die die Semantik der Abfrage nicht verändern, dafür aber, zum Beispiel, weitere Zugriffsmöglichkeiten erlauben. Im Fall von Unterabfragen wie in A und B, kommt sogenanntes Subquery Unnesting als Transformation infrage.

Die Tabelle aus der Unterabfrage wird in die Hauptabfrage „eingezogen“ und es wird ein Semi-Join

durchgeführt. Der Ausführungsplan sieht dann zum Beispiel wie im Listing 4 aus:

SQL SELECT c.cust_last_name, c.cust_first_name, c.cust_id 2 FROM sh.customers c 3 WHERE c.cust_city = 'Koeln' 5 AND EXISTS (SELECT 1 6 FROM sh.sales s 7 WHERE c.cust_id = s.cust_id );

...

44 rows selected.

...

-------------------------------------------------------------------------------Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |

-------------------------------------------------------------------------------SELECT STATEMENT | | 1| | 44 |00:00:14.62 | |* 1 | HASH JOIN SEMI | | 1| 514 | 44 |00:00:14.62 | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 1| 532 | 532 |00:00:00.05 | | 3| TABLE ACCESS FULL| SALES | 1| 918K| 918K|00:00:03.86 |

-------------------------------------------------------------------------------

<

Listing 4:Semi-Join

Der Unterschied in der Laufzeit zu einem Join ist kaum da. Diese Art der Abfrage hat aber einige Vorteile. Zum einen, kann man erkennen, dass man sich den Schritt HASH UNIQUE sparen kann, weil Semi Joins per Definition keine Dubletten zurückliefert. Zum zweiten, machen die Semi-Join Algorithmen von dieser Tatsache Gebrauch und führen tendenziell weniger Arbeit durch. Die Weiterverarbeitung für einen Satz aus der äußeren Tabelle wird unterbrochen, sobald ein Treffer in der inneren Tabelle gefunden wurde.

Das kann man sich noch gut vorstellen, wenn der Join mit Nested Loops durchgeführt wird. Aber wie funktioniert es bei einem Hash Join? Bei einem Hash Join wird eine Tabelle (in der Regel die kleinere) als treibende gewählt, auf die Join-Kriterien eine Hash-Funktion angewendet und damit eine HashTabelle erzeugt. Dann wird die getriebene Tabelle gescannt, dieselbe Hash-Funktion auf die JoinKriterien angewendet und mit dem Ergebnis die Hash-Tabelle geprobt. Bei einem Treffer werden die Join-Kriterien exakt abgeglichen und der Datensatz bei Übereinstimmung zurückgeliefert. Die Optimierung des Semi-Joins besteht darin, dass beim ersten Treffer der Eintrag aus der Hash-Tabelle gelöscht wird. Weitere potenziellen Treffer finden den Eintrag in der Hash-Tabelle nicht mehr und werden abgewiesen – somit entfällt die Notwendigkeit, das Ergebnis am Ende noch einmal zu deduplizieren, wie es bei einem normalen Join der Fall wäre. Des Weiteren, wenn durch dieses Löschen die Hash-Tabelle leer wird, stoppt die komplette Verarbeitung, noch bevor die getriebene Tabelle komplett gescannt wurde.

Am Beispiel im Listing 5 sieht man es deutlich. Wir haben die Menge extra so eingeschränkt, dass nur ein Datensatz aus der Kundentabelle zutrifft. Während bei einem normalen Hash-Join die Tabelle Sales komplett gelesen wurde („A-Rows“ = 918K), wurden bei einem Hash-Semi-Join nur 13567

Datensätze gelesen, bis der Treffer für unseren Kunden gefunden wurde. Dann war die Abfrage fertig:

das weitere Lesen der Tabelle Sales kann das Ergebnis der Abfrage nicht mehr beeinflussen und wird gestoppt. Das lässt sich auch direkt an der Laufzeit merken - 15 Sekunden versus 0,3 Sekunden.

–  –  –

...

-------------------------------------------------------------------------------Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |

-------------------------------------------------------------------------------SELECT STATEMENT | | 1| | 1 |00:00:00.28 | |* 1 | HASH JOIN SEMI | | 1| 1| 1 |00:00:00.28 | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 1| 1| 1 |00:00:00.06 | | 3| TABLE ACCESS FULL| SALES | 1| 918K| 13567 |00:00:00.06 |

-------------------------------------------------------------------------------

<

Listing 5:Semi-Join im Vergleich zu Join

Durch Subquery Unnesting bekommt der Optimizer die Möglichkeit, außer Filter-Operation die bekannten Join-Methoden Nested Loops, Hash oder Sort/Merge je nach Mengengerüsten und JoinKriterien anzuwenden. Auch die Join-Reihenfolge kann noch optimiert werden. Bei der Operation HASH JOIN SEMI wird die äußere Tabelle immer als Hash Tabelle verwendet, auch wenn sie größer ist als die innere. Ab Version 10g gibt es eine Verbesserung: HASH JOIN RIGHT SEMI. Bei dieser Operation ist es möglich, die Hash-Tabelle aus der Unterabfrage zu erzeugen.

Listing 5 zeigt auch, dass die IN-Unterabfrage genauso vom Subquery Unnesting profitiert. Sie wird intern in die korrelierte EXISTS-Form transformiert.

Wir haben gesehen: Wenn Subquery Unnesting stattfindet, ist der Optimizer in der Lage, effiziente Wege für Zugriffsmethoden, Join-Methoden sowie Join-Reihenfolge zu finden. Und dies unabhängig davon, ob die Abfrage mit IN oder EXISTS formuliert ist, oder davon, welche Seite stärkere Selektivität aufweist. Man sollte den CBO einfach seine Arbeit machen lassen.

Anti Join Wie sieht es mit Unterabfragen aus, die mit NOT IN oder NOT EXISTS an die Hauptabfrage angebunden sind? In der Tabelle CUSTOMERS sind alle Kunden in die Einkommensgruppen eingeteilt. Wie beantworten wir die Frage, ob unter den Kunden in Hamburg alle Einkommensgruppen vertreten sind, die es auch bei Kölnern gibt? Vorstellbar sind zwei Varianten mit Unterabfragen (Listing 6, A und B).

--A SQL SELECT DISTINCT c.cust_income_level 2 FROM sh.customers c 3 WHERE c.cust_city = 'Koeln' 5 AND NOT EXISTS (SELECT 1 6 FROM sh.customers c2 7 WHERE c2.country_id = 52776 8 AND c2.cust_city = 'Hamburg' 9 AND c2.cust_income_level=c.cust_income_level);

K: 250,000 - 299,999

–  –  –



Pages:   || 2 |


Similar works:

«75 Wasserbaukolloquium 2006: Strömungssimulation im Wasserbau Dresdner Wasserbauliche Mitteilungen Heft 32 Morphodynamische Untersuchungen zur Verbesserung der Unterhaltungssituation an der Tideems Christoph Heinzelmann, Harro Heyer Das Feststoffregime der Tideems ist geprägt durch einen deutlich stromauf gerichteten Sedimenttransport (tidal pumping) und starke Sedimentationen im Bereich des Emder Fahrwassers und der Unterems, verbunden mit hohen Kosten für Unterhaltungsbaggerungen und die...»

«Informationen zu den Workshops und Anregungen für einen individuellen Ausstellungsbesuch mit Schulklassen «Flex-Sil Reloaded – eine Hommage an Roman Signer» (24. Mai – 4. August 2013) Ausstellungsansichten: Roman Signer, Navid Nuur, Valentin Carron, Raphael Hefti, Nina Canell Photo: Kunst Halle Sankt Gallen, Gunnar Meier Inhalt Einführung 2 Workshops in der Ausstellung «Flex-Sil Reloaded» Zur Ausstellung, Inhalt Workshops, Zeitraum, Zielgruppen, Zeitaufwand, Ablauf 3 Individueller...»

«15 Jahre EXIST Existenzgründungen aus der Wissenschaft Entwicklung des Förderprogramms von 1998 bis 2013 Dr. Marianne Kulicke Arbeitspapier der wissenschaftlichen Begleitforschung zu EXIST Existenzgründungen aus der Wissenschaft Fraunhofer-Institut für Systemund Innovationsforschung ISI Breslauer Straße 48 76139 Karlsruhe Karlsruhe, April 2014 Der Förderansatz von EXIST Existenzgründungen aus der Wissenschaft 1 Der Förderansatz von EXIST Existenzgründungen aus der Wissenschaft...»

«Die neue wirtschaftspolitische Governance der EU — Christophe Degryse Working Paper 2012.14 Die neue wirtschaftspolitische Governance der EU — Christophe Degryse Working Paper 2012.14 european trade union institute Die französische Originalfassung dieses Textes wurde vom CRISP (Centre de recherche et d’information socio-politiques, Brüssel) unter dem Titel La nouvelle gouvernance économique et européenne in der Publikationsreihe Courrier hebdomadaire veröffentlicht. www.crisp.be –...»

«Zusammenfassungen ∙ Resums ∙ Abstracts Alejandro Casadesús Bordoy (Palma) Alcover, Moll und die deutsche Sprache Der Artikel stellt zwei Hauptfiguren der katalanischen Philologie aus mehrperspektivischer Sicht dar. Mossèn Alcover und Francesc de B. Moll verbindet man in jedem Fall mit der katalanischen Sprache, aber man sollte ebenfalls in Betracht ziehen, dass sie sich auch mit der deutschen Sprache beschäftigten. Alcover lernte Deutsch, um einen besseren Kontakt zu den...»

«PRESS RELEASE PAN-EUROPEAN INTRADAY TARGET MODEL CONFINES EXCHANGES’ DEVELOPMENT CAPACITY Exchange Council backs flexibility products London │ Paris, 14/03/2013 – Performing power markets need flexibility: This is the result of discussions amongst the members of the Exchange Council of the European Power Exchange EPEX SPOT at its latest meeting. Concerning this matter, the Exchange Council deliberated on the tender for the pan-European Intraday system and supported the development of new...»

«Die Rezeption Salvador Dalís in der amerikanischen Kultur der Nachkriegszeit Eva Morawietz (Göttingen) 1 Dalí in den USA Die Wirkungsgeschichte des Surrealismus in den USA begann maßgeblich mit der Rezeption der Werke Salvador Dalís und seiner Präsenz im New York der 1930er Jahre. Innerhalb dieser Dekade avancierte Dalí zum führenden Vertreter des Surrealismus in den Vereinigten Staaten und übernahm jene Rolle, die André Breton zuvor in Europa innegehabt hatte. Während die Werke...»

«Zeitschrift für ausländisches und internationales Arbeitsund Sozialrecht (ZIAS) Herausgegeben von Institut für Arbeitsrecht und Arbeitsbeziehungen in der Europäischen Gemeinschaft Max-Planck-Institut für ausländisches und internationales Sozialrecht B e i r a t : Prof. D r. F r a n z G a m i l l s c h e g, Universität Göttingen • Prof. D r. F r a n z X a v e r K a u f m a n n, Universität Bielefeld • Prof. D r. A l f r e d M a u r e r, Universität B e r n, Zürich • Prof....»

«REAL Revista de Estudos Alemães http://real.fl.ul.pt/editorial.page Pesquisa | Subscrição | Contacte-nos | Ficha Técnica Entrada » Editorial » Editorial » Textos » Ecléctica » Novas Publicações » Notícias » Arquivo Representações da Pobreza Repräsentationen der Armut 2013-07-31 por Teresa Seruya, Helena Gonçalves da Silva, Bernd Sieberg, Gerd Hammer „Da es nicht für alle reicht, springen die Armen ein“ hat Ernst Bloch 1959 in Spuren scharfsichtig formuliert. Und auch...»

«Elektrische Antriebe mit dauermagneterregten Maschinen im dynamischen sensorlosen Betrieb An der Fakultät für Elektrotechnik der Helmut-Schmidt-Universität Universität der Bundeswehr Hamburg zur Erlangung des akademischen Grades eines Doktor-Ingenieurs eingereichte DISSERTATION von Bassel Sahhary Hamburg 2008 Erstgutachter: Prof. Dr.-Ing. Ekkehard Bolte Helmut-Schmidt-Universität/ Universität der Bundeswehr Hamburg Professur für Elektrische Maschinen und Antriebe Zweitgutachter: Prof....»





 
<<  HOME   |    CONTACTS
2016 www.book.dislib.info - Free e-library - Books, dissertations, abstract

Materials of this site are available for review, all rights belong to their respective owners.
If you do not agree with the fact that your material is placed on this site, please, email us, we will within 1-2 business days delete him.