IN versus EXISTS oder doch ein Join? - doag.org fileTrivadis ist führend bei der IT-Beratung, der...

IN versus EXISTS oder doch ein Join?

Andrej PashchenkoSenior Consultant,Trivadis GmbH

19.11.2013

2013 © Trivadis

BASEL BERN BRUGG LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN

2013 © Trivadis

19.11.2013IN versus EXISTS oder doch ein Join?

1

Trivadis ist führend bei der IT-Beratung, der Systemintegration, dem Solution-Engineering und der Erbringung von IT-Services mit Fokussierung auf und Technologien im D-A-CH-Raum.

Unsere Leistungen erbringen wir aus den strategischen Geschäftsfeldern:

Unser Unternehmen

2013 © Trivadis

Trivadis Services übernimmt den korrespondierenden BetriebIhrer IT Systeme.


B E T R I E BB E T R I E B

2

Mit über 600 IT- und Fachexperten bei Ihnen vor Ort

12 Trivadis Niederlassungen mitüber 600 Mitarbeitenden

200 Service Level Agreements

Mehr als 4'000 Trainingsteilnehmer

Forschungs- und Entwicklungs-budget: CHF 5.0 / EUR 4 Mio.

HamburgHamburg

DüsseldorfDüsseldorf

2013 © Trivadis

3

budget: CHF 5.0 / EUR 4 Mio.

Finanziell unabhängig undnachhaltig profitabel

Erfahrung aus mehr als 1'900 Projekten pro Jahr bei über 800 Kunden

Stand 12/2012

3


FrankfurtFrankfurt

FreiburgFreiburgMünchenMünchen

WienWien

BaselBaselZürichZürichBernBern

LausanneLausanne

3

StuttgartStuttgart

BruggBrugg

3

Über mich

� Senior Consultant bei der Trivadis GmbH, Düsseldorf

� Schwerpunkt Oracle� Application Development� Application Performance � Data Warehousing

� 20 Jahre IT-Erfahrung, davon 14 Jahre mit Oracle DB

2013 © Trivadis

� 20 Jahre IT-Erfahrung, davon 14 Jahre mit Oracle DB


4

AGENDA

1. Einführung

2. Semi-Join

3. Subquery Unnesting

4. Anti-Join

2013 © Trivadis


5

Einführung

� Unterabfragen (Subqueries) können in einem SQL-Statement an unterschiedlichen Stellen eingesetzt werden: SELECT, FROM, WHERE, HAVING, WITH…

� Wir betrachten Subqueries in der WHERE-Bedingung, auch „NestedSubqueries“ genannt, angebunden mit IN oder EXISTS (NOT IN / NOT EXISTS)

2013 © Trivadis

� Skripte zu diesem Vortrag basieren auf dem Oracle-Beispiel-Schema Sales History (SH)


6

Einführung

2013 © Trivadis


7

Semi-Join� Welche Kunden aus Köln haben bereits Bestellungen aufgegeben?

--ASELECT c.cust_last_name, c.cust_first_name, c.cust _idFROM customers cWHERE c.cust_city = 'Koeln'AND EXISTS (SELECT 1

FROM sales s WHERE c.cust_id = s.cust_id );

--BSELECT c.cust_last_name, c.cust_first_name, c.cust _idFROM customers c

korreliert

2013 © Trivadis


8

FROM customers cWHERE c.cust_city = 'Koeln'AND c.cust_id IN (SELECT s.cust_id

FROM sales s );

unkorreliert

CUSTOMERS SALES

� Keine Daten aus der „rechten“ Tabelle

� Jeder Datensatz aus der „linken“ Tabelle höchstens einmal

Semi-Join

� Subqueries als Performance-Killer: Mit FILTER-Operation einmal pro eindeutigem Wert des Kriteriums

SQL> SELECT c.cust_last_name, c.cust_first_name, c.cust_id2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND EXISTS (SELECT /*+ no_unnest */ 16 FROM sh.sales s7 WHERE c.cust_id = s.cust_id );

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

2013 © Trivadis


9

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, 0, 'ALLSTATS LAST'));...--------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |--------------------------------------------------------------------------------| 0 | 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 |--------------------------------------------------------------------------------

Semi-Join

Viele unterschiedliche Empfehlungen und Best Practices

� Oracle Dokumentation bis 10gR2 (Perfomance Tuning Guide, Kapitel "SQL Tuning Overview" - "Developing efficient SQL Statements") sagt: "In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.”

2013 © Trivadis


10

� Use EXISTS rather than IN whenever possible

� Use Joins whenever possible

Semi-Join

SQL> SELECT DISTINCT c.cust_last_name, c.cust_first_name, c.cust_id2 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'));...

� Formuliert als Join: deutliche Performance-Steigerung

� Ist das die Lösung?

sq1.sql

2013 © Trivadis


11

...---------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |---------------------------------------------------------------------------------| 0 | 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 |---------------------------------------------------------------------------------

Subquery Unnesting

� Subquery Unnesting – eine der Query Transformationen, die CBO durchführen kann

� Subquery wird in die Hauptabfrage „eingezogen“, u. U. über eine Inline-View, und dann ein Semi-Join durchgeführt

� In 8i inaktiv (hidden parameter), ab 9i immer aktiv, ab 10g cost based

� In den CBO-Trace-Dateien (event 10053) Abkürzung SU

2013 © Trivadis


12

� Durch Hints UNNEST, NO_UNNEST in der Unterabfrage steuerbar

� Validity Checks müssen erfüllt sein

� Nicht möglich, wenn� korreliert über mehrere Ebenen� korrelierte Unterabfrage mit GROUP BY� …

Subquery Unnesting

SQL> SELECT c.cust_last_name, c.cust_first_name, c.cust_id2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND EXISTS (SELECT 16 FROM sh.sales s7 WHERE c.cust_id = s.cust_id );

...

sq2.sql

� Deduplizierung ist Teil der SEMI JOIN Operation

� Gleichzeitig auch ein Optimierungspotenzial dadurch

2013 © Trivadis


13

...44 rows selected....--------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |--------------------------------------------------------------------------------| 0 | 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 |--------------------------------------------------------------------------------

Subquery Unnesting

kleinere Datenmenge

größere Datenmenge

proben

Hash-Join Semi

2013 © Trivadis


14

Join-Ergebnis

Subquery Unnesting

SQL> SELECT DISTINCT c.cust_last_name, c.cust_first_name, c.cust_id2 FROM sh.customers c JOIN sh.sales s ON (c.cust_id = s.cust_id)3 WHERE c.cust_city = 'Koeln'5 AND c.cust_last_name LIKE 'Nappi%';

Nappier Beryl 2397

...

sq3.sql

� Der Beweis: beim Hash-Join wird die Tabelle SALES komplett gelesen (918K Datensätze)

2013 © Trivadis


15

...---------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:14.99 || 1 | HASH UNIQUE | | 1 | 1 | 1 |00:00:14.99 ||* 2 | HASH JOIN | | 1 | 2 | 86 |00:00:14.99 ||* 3 | TABLE ACCESS FULL| CUSTOMERS | 1 | 1 | 1 |00:00:00.04 || 4 | TABLE ACCESS FULL| SALES | 1 | 918K| 918K|00:00:04.13 |---------------------------------------------------------------------------------

Subquery Unnesting

SQL> SELECT c.cust_last_name, c.cust_first_name, c.cust_id2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND c.cust_last_name LIKE 'Nappi%'6 AND c.cust_id IN (SELECT s.cust_id

sq3.sql

� Beim Hash-Semi-Join werden aus der Tabelle SALES nur 13K gelesen.

� deutlich schneller

� Auch IN-Subqueries führen zum gleichen Ausführungsplan

2013 © Trivadis


16

6 AND c.cust_id IN (SELECT s.cust_id7 FROM sh.sales s );

Nappier Beryl 2397

...--------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |--------------------------------------------------------------------------------| 0 | 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 |--------------------------------------------------------------------------------

Subquery Unnesting

� Subquery Unnesting ermöglicht es, außer FILTER alle bekannten Join-Methoden anzuwenden: Nested Loops, Hash, Sort-Merge

� Hash Join Semi kann bis 10g suboptimal sein, da die Hash-Tabelle immer über die Hauptabfrage gebildet wurde

� Ab 10g gibt es die Operation HASH JOIN RIGHT SEMI – Hash-Tabelle wird über die Subquery gebildet.

2013 © Trivadis


17

Null-Accepting Semi-Joins in 12c

SQL> alter session set optimizer_features_enable='11.1.0.6';

SQL> SELECT DISTINCT c.cust_income_level2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'4 AND ( EXISTS (SELECT 15 FROM sh.customers c26 WHERE c2.country_id = 52776

� Korrelierte Spalte IS NULL, OR-Verknüpfung mit Subquery:� Keine Subquery Unnesting in 11g

2013 © Trivadis


18

7 AND c2.cust_city = 'Hamburg'8 AND c2.cust_income_level=c.cust_income_level)9 or c.cust_income_level IS NULL);

... ---------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.07 || 1 | HASH UNIQUE | | 1 | 12 | 11 |00:00:00.07 ||* 2 | FILTER | | 1 | | 522 |00:00:00.07 ||* 3 | TABLE ACCESS FULL| CUSTOMERS | 1 | 669 | 532 |00:00:00.02 ||* 4 | TABLE ACCESS FULL| CUSTOMERS | 12 | 5 | 11 |00:00:00.05 |---------------------------------------------------------------------------------

Null-Accepting Semi-Joins in 12c

SQL> alter session set optimizer_features_enable='12.1.0.1.1';

SQL> SELECT DISTINCT c.cust_income_level2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'4 AND ( EXISTS (SELECT 15 FROM sh.customers c26 WHERE c2.country_id = 52776

� Neue Operation Null-Accepting Semi-Join ab 12c� Subquery Unnesting kann stattfinden� Funktioniert nicht für IN-Subqueries

2013 © Trivadis


19

7 AND c2.cust_city = 'Hamburg'8 AND c2.cust_income_level=c.cust_income_level)9 or c.cust_income_level IS NULL);

... --------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 || 1 | HASH UNIQUE | | 1 | 12 | 11 |00:00:00.01 ||* 2 | HASH JOIN RIGHT SEMI NA | | 1 | 668 | 522 |00:00:00.01 ||* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 59 | 44 |00:00:00.01 ||* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 669 | 532 |00:00:00.01 |--------------------------------------------------------------------------------------

Subquery Unnesting

Fazit

� Wenn Subquery Unnesting stattfindet, ist der Optimizer in der Lage, effiziente Wege für Zugriffsmethoden, Join-Methoden sowie Join-Reihenfolge zu finden.

� Subqueries sind nicht per se langsam – erst den CBO seine Arbeit machen lassen

2013 © Trivadis


20

� IN und EXISTS sind austauschbar

� Join nur als Notlösung oder wenn Daten aus der Unterabfrage im Gesamtergebnis benötigt werden

Anti-Join

--ASQL> SELECT DISTINCT c.cust_income_level

2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND NOT EXISTS (SELECT 16 FROM sh.customers c27 WHERE c2.country_id = 527768 AND c2.cust_city = 'Hamburg'

� Sind unter den Kunden in Hamburg alle Einkommensgruppen vertreten, die es auch bei Kölnern gibt?

2013 © Trivadis


21

9 AND c2.cust_income_level=c.cust_income_level);K: 250,000 - 299,999

--BSQL> SELECT DISTINCT c.cust_income_level

2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND c.cust_income_level NOT IN (SELECT c2.cust_income_level6 FROM sh.customers c27 WHERE c2.country_id = 527768 AND c2.cust_city = 'Hamburg');

K: 250,000 - 299,999

Anti-Join

-- Update auf NULLSQL> UPDATE sh.customers c

2 SET c.cust_income_level = NULL3 WHERE c.cust_city = 'Hamburg'5 AND c.cust_id = 48958;

1 row updated.

� Unterschiedliche Semantik im Hinblick auf NULLs� NOT IN liefert kein Ergebnis zurück

sq4.sql

2013 © Trivadis


22

--B erneut ausführenSQL> SELECT DISTINCT c.cust_income_level

2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND c.cust_income_level NOT IN (SELECT c2.cust_income_level6 FROM sh.customers c27 WHERE c2.country_id = 527768 AND c2.cust_city = 'Hamburg');

no rows selected

Anti-Join

SQL> SELECT DISTINCT c.cust_income_level2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND c.cust_income_level IS NOT NULL6 AND c.cust_income_level NOT IN (SELECT c2.cust_income_level7 FROM sh.customers c28 WHERE c2.country_id = 527769 AND c2.cust_city = 'Hamburg'

sq5.sql

� Unterschiedliche Semantik im Hinblick auf NULLs� Bis 11g braucht man NOT NULL-Constraints oder Where-Bedingungen, damit

Subquery Unnesting stattfindet

2013 © Trivadis


23

10 AND c2.cust_income_level IS NOT NULL );K: 250,000 - 299,999

---------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.12 || 1 | HASH UNIQUE | | 1 | 1 | 1 |00:00:00.12 ||* 2 | HASH JOIN ANTI | | 1 | 1 | 10 |00:00:00.12 ||* 3 | TABLE ACCESS FULL| CUSTOMERS | 1 | 13 | 532 |00:00:00.06 ||* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 13 | 43 |00:00:00.04 |---------------------------------------------------------------------------------

Anti-Join

SQL> SELECT DISTINCT c.cust_income_level2 FROM sh.customers c3 WHERE c.cust_city = 'Koeln'5 AND c.cust_income_level NOT IN (SELECT c2.cust_income_level6 FROM sh.customers c27 WHERE c2.country_id = 527768 AND c2.cust_city = 'Hamburg');

no rows selected

� Null-Aware Anti-Join ab 11g

2013 © Trivadis


24

---------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.08 || 1 | HASH UNIQUE | | 1 | 1 | 0 |00:00:00.08 ||* 2 | HASH JOIN ANTI NA | | 1 | 1 | 0 |00:00:00.08 ||* 3 | TABLE ACCESS FULL| CUSTOMERS | 1 | 13 | 532 |00:00:00.05 ||* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 13 | 1 |00:00:00.01 |---------------------------------------------------------------------------------

Anti-Join

SQL> SELECT DISTINCT c.cust_income_level2 FROM sh.customers c LEFT JOIN sh.customers c23 ON (c.cust_income_level = c2.cust_income_level)4 WHERE c.cust_city = 'Koeln‘5 AND c2.country_id = 527766 AND c2.cust_city = 'Hamburg'7 AND c2.cust_id IS NULL;

K: 250,000 - 299,999

� Anti-Join ohne Subquery über Outer Join� schlechte Lesbarkeit� verhält sich bezüglich NULLS wie NOT EXISTS

2013 © Trivadis


25

K: 250,000 - 299,999

-------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.20 || 1 | HASH UNIQUE | | 1 | 5 | 1 |00:00:00.20 ||* 2 | FILTER | | 1 | | 10 |00:00:00.19 ||* 3 | HASH JOIN RIGHT OUTER| | 1 | 5 | 2678 |00:00:00.18 ||* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 44 | 44 |00:00:00.09 ||* 5 | TABLE ACCESS FULL | CUSTOMERS | 1 | 532 | 532 |00:00:00.06 |-------------------------------------------------------------------------------------

Anti-Join

� Mit NOT IN immer die NULL-Problematik im Blick behalten

� Auch hier kann Subquery Unnesting stattfinden

� NOT NULL – Constraints oder Where-Bedingungen vor 11g einsetzen

� Ab 11g Null-Aware Anti-Joins

2013 © Trivadis


26

Fazit

� Subqueries sind keine „Performance Killer“

� Können schneller als ein Join sein

� Jede neue Version der DB führt zu Verbesserungen

� CBO erhält neue Möglichkeiten, effiziente Ausführungspläne zu generieren

� Dem CBO keine Informationen vorenthalten – Constraints!

2013 © Trivadis


27

� Dem CBO keine Informationen vorenthalten – Constraints!

Fragen und Antworten...Andrej PashchenkoSenior Consultant

Tel. +49 211 58 66 64 70

[email protected]

2013 © Trivadis

BASEL BERN BRUGG LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN

2013 © Trivadis


TrivadisTrivadis an der DOAGan der DOAG

Ebene 3 Ebene 3 -- gleich neben der Rolltreppegleich neben der Rolltreppe

Wir freuen uns auf Ihren Besuch. Wir freuen uns auf Ihren Besuch.

2013 © Trivadis

Trivadis – das Unternehmen

Wir freuen uns auf Ihren Besuch. Wir freuen uns auf Ihren Besuch.

Denn mit Denn mit TrivadisTrivadis gewinnen Sie immer.gewinnen Sie immer.

IN versus EXISTS oder doch ein Join? - doag.org fileTrivadis ist führend bei der IT-Beratung, der...

Documents

Transcript of IN versus EXISTS oder doch ein Join? - doag.org fileTrivadis ist führend bei der IT-Beratung, der...