Performance von Joins: inner join vs. Where- Klauseln

[philippd]

Moin,


wir hatten heute eine kleine Diskussion bezüglich Joins.

Es gibt ja sowohl die Möglichkeit, Joins über die Where- Klausel zu beschreiben, als auch den inner join befehl zu verwenden...

Hier mal ein Beispiel:

-- Join über WHERE- Klausel
SELECT
k.name, sum(b.menge * p.preis) as Umsatz
FROM 
Auftrag a, Kunde k, bestellung b, Pizza p
WHERE 
a.Telefon = k.Telefon AND
b.ANummer = a.Nummer AND
b.PNummer = p.Nummer
GROUP BY k.name;


-- Join über Inner Join Befehl
SELECT
k.name, sum(b.menge * p.preis) as Umsatzs
FROM
Auftrag a inner join Kunde k on (a.Telefon = k.Telefon) 
          inner join Bestellung b on (b.ANummer = a.nummer)
          inner join Pizza p on (b.PNummer = p.Nummer)
group by k.name;

Das Ergebnis sollte ja in jedem Falle das gleiche sein oder?

Mir persönlich gefällt die erste Variante, also das Verknüpfen in der WHERE Klausel wesentlich besser, weil man finde ich rein von der Schreibweise her einfacher damit arbeiten kann und auf den ersten Blick die Verknüpfungen richtig erkennt.

Nun habe ich gehört, dass die zweite Variante gerade bei großen Datenmengen wesentlich performanter sein soll. Ist da denn nun etwas dran?

Ich dachte ein DBMS wie Oracle optimiert die SQL Statements ohnehin vor der Ausführung... Wird dann nicht quasi physikalisch das gleiche auf der DB veranstaltet?

Welche Variante bevorzugt ihr?


Danke und Gruß
Philipp

[dimitri]

Das Ergebnis sollte ja in jedem Falle das gleiche sein oder?

Ja.

Mir persönlich gefällt die erste Variante, also das Verknüpfen in der WHERE Klausel wesentlich besser

Mir auch.

Nun habe ich gehört, dass die zweite Variante gerade bei großen Datenmengen wesentlich performanter sein soll. Ist da denn nun etwas dran?

Auf Hörensagen sollte man sich nie verlassen. Mal ein kleines Beispiel:

create table cons_columns as
select * from ALL_CONS_COLUMNS;


create table constraints as
select * from all_tables;

create table objects as
select * from all_objects;

create index cons_columns_ix on cons_columns(table_name);
create index constraints_ix on constraints(table_name);
create index objects_ix on objects(object_name);

begin
 dbms_stats.gather_table_stats(tabname=>'CONS_COLUMNS',ownname=>'DIMITRI',cascade=>true);
 dbms_stats.gather_table_stats(tabname=>'CONSTRAINTS',ownname=>'DIMITRI',cascade=>true);
 dbms_stats.gather_table_stats(tabname=>'OBJECTS',ownname=>'DIMITRI',cascade=>true);
end;
/

Und jetzt schauen wir mal, wie Oracle mit den beiden Joinarten umgeht:

explain plan for 
  select c.* from cons_columns a, constraints b, objects c
  where a.table_name=b.table_name
    and b.table_name=c.object_name
    and c.object_type='TABLE';

select * from table(dbms_xplan.display);
Plan hash value: 2761773263

------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |  8312 |  1144K|   302   (1)| 00:00:04 |
|*  1 |  HASH JOIN             |                 |  8312 |  1144K|   302   (1)| 00:00:04 |
|*  2 |   HASH JOIN            |                 |  1843 |   217K|   287   (1)| 00:00:04 |
|   3 |    INDEX FAST FULL SCAN| CONSTRAINTS_IX  |  2524 | 50480 |     5   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL   | OBJECTS         |  1838 |   181K|   281   (1)| 00:00:04 |
|   5 |   INDEX FAST FULL SCAN | CONS_COLUMNS_IX | 11350 |   221K|    15   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
   2 - access("B"."TABLE_NAME"="C"."OBJECT_NAME")
   4 - filter("C"."OBJECT_TYPE"='TABLE')

Und jetzt die Ansi Syntax:

explain plan for 
  select c.* from cons_columns a
     inner join constraints b on(a.table_name=b.table_name)
     inner join objects c     on(b.table_name=c.object_name)
  where
     c.object_type='TABLE';

select * from table(dbms_xplan.display);
Plan hash value: 2761773263

------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |  8312 |  1144K|   302   (1)| 00:00:04 |
|*  1 |  HASH JOIN             |                 |  8312 |  1144K|   302   (1)| 00:00:04 |
|*  2 |   HASH JOIN            |                 |  1843 |   217K|   287   (1)| 00:00:04 |
|   3 |    INDEX FAST FULL SCAN| CONSTRAINTS_IX  |  2524 | 50480 |     5   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL   | OBJECTS         |  1838 |   181K|   281   (1)| 00:00:04 |
|   5 |   INDEX FAST FULL SCAN | CONS_COLUMNS_IX | 11350 |   221K|    15   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
   2 - access("B"."TABLE_NAME"="C"."OBJECT_NAME")
   4 - filter("C"."OBJECT_TYPE"='TABLE')

Siehe da, die gleichen Join und Filter Prädikate.

Jetzt mischen wir mal Filter und Join Kriterium, um zu sehen, was Oracle daraus macht:

explain plan for 
  select c.* from cons_columns a
     inner join constraints b on(a.table_name=b.table_name)
     inner join objects c     on(b.table_name=c.object_name
                                 and b.owner= c.owner)
  where 
    c.object_type='TABLE';

select * from table(dbms_xplan.display);
Plan hash value: 3217815943

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |  8289 |  1198K|   322   (1)| 00:00:04 |
|*  1 |  HASH JOIN            |                 |  8289 |  1198K|   322   (1)| 00:00:04 |
|*  2 |   HASH JOIN           |                 |  1838 |   229K|   307   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL  | CONSTRAINTS     |  2524 | 68148 |    25   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL  | OBJECTS         |  1838 |   181K|   281   (1)| 00:00:04 |
|   5 |   INDEX FAST FULL SCAN| CONS_COLUMNS_IX | 11350 |   221K|    15   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
   2 - access("B"."TABLE_NAME"="C"."OBJECT_NAME" AND "B"."OWNER"="C"."OWNER")
   4 - filter("C"."OBJECT_TYPE"='TABLE')

Und jetzt ziehen wir das Join Kriterium in die where-Bedingung:

explain plan for 
  select c.* from cons_columns a
     inner join constraints b on(a.table_name=b.table_name)
     inner join objects c     on(b.table_name=c.object_name
                                 and b.owner= c.owner)
  where
      b.owner=c.owner
  and c.object_type='TABLE';

select * from table(dbms_xplan.display);
Plan hash value: 3217815943

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |  8289 |  1198K|   322   (1)| 00:00:04 |
|*  1 |  HASH JOIN            |                 |  8289 |  1198K|   322   (1)| 00:00:04 |
|*  2 |   HASH JOIN           |                 |  1838 |   229K|   307   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL  | CONSTRAINTS     |  2524 | 68148 |    25   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL  | OBJECTS         |  1838 |   181K|   281   (1)| 00:00:04 |
|   5 |   INDEX FAST FULL SCAN| CONS_COLUMNS_IX | 11350 |   221K|    15   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
   2 - access("B"."OWNER"="C"."OWNER" AND "B"."TABLE_NAME"="C"."OBJECT_NAME")
   4 - filter("C"."OBJECT_TYPE"='TABLE')

Auch hier erkennt Oracle richtig was eine Join Bedingung und was ein Filterkriterium ist obwohl wir es anders geschrieben haben.

In der Theorie wird Oracle also einen Join, wie immer er auch geschrieben ist, so umschreiben, dass er, aus Oracle Sicht, optimal ausgeführt wird. In der Praxis ist es so, dass man die Logik die hinter dem Optimizer stecke durchaus auch etwas verwirren kann. Insb. bei komplexeren Joins in der Version 10 ist mir z.T. aufgefallen, dass Oracle unterschiedliche Pläne verwendet abhängig davon, ob man die neue und alte Syntax verwendet. Die Beispiele wurden mit der 11g gemacht - vielleicht würde dort auch meine Beobachtung nicht mehr auftreten.

Fazit: Ob Kommajoin oder Ansi Join ist größtenteils Geschmackssache, aber es schadet auch nie, auch mal die andere alternative zu testen und ggf. herauszufinden warum unterschiedliche Pläne verwendet werden. Meistens liegt es daran, dass die Filterkriterien nicht gut genug sind z.B. wenn eine fachliche Annahme (da kann ja eh nur Wert XY rauskommen) nicht auch technisch so in der WHERE Bedingung geschrieben ist.

Dim

[philippd]

Hallo Dimitri,


vielen Dank für die ausführliche Antwort und die Beispiele!

Schon beeindruckend, wie der optimizer doch optimieren kann

[jum]

@dimitri - gelesen hat man es schon, aber so "schön" bewiesen - Vielen Dank