✔ Faustregel --> auf welche Spalten soll ein Index erstellt werden?

[oraclin25]

Hallo zusammen,

ich habe folgende SQL-Anfrage:

SELECT
    a.column_01,
    a.column_02,
    b.column_02
FROM
    tabelle_a a
INNER JOIN
    tabelle_b b 
ON 
    a.column_01 = b.column_01 AND a.column_02 != b.column_02;

Also, im Grunde eine einfache SQL-Anfrage. Das Problem ist, tabelle_a hat hunderte von Millionen Records, tabelle_b ebenso.

Nun, die Ausführung von der SQL-Anfrage dauert mindestens eine Stunde. Ich dachte, ich könnte sicherlich die Ausführungszeit optimieren, wenn ich Indexe auf den Tabellen habe. Nur, ich frage mich, bei so einer SQL-Anfrage, auf welche Spalten soll ich einen Index erstellen?

Vielen Dank für Eure Hilfestellungen.

Schöne Grüße aus Rheinland,

Eure Ratna

 

[Yaslaw]

a.column_01 = b.column_01 AND a.column_02 != b.column_02;

 

[oraclin25]

Hallo Yaslaw,

war Deine Antwort vielleicht aus Versehen unvollständig?

Schöne Grüße aus Rheinland,

Eure Ratna

 

[sheel]

Er will damit ausdrücken, dass die 4 Spalten einen Index bekommen sollen

edit: nicht einen einzigen Index

 

[Yaslaw]

Genau, war im Büro grad im Stress und hatte darum keinen kommentar geschrieben.

Index1: a.column_01, a.column_02
Index2: b.column_01 b.column_02

 

[oraclin25]

Hallo Yaslaw und sheel,

@Yaslaw, könntest Du bitte kurz erklären, warum ich ausgerechnet die beiden Indexe erstellen soll? Die Faustregel zur Erstellung eines Index --> man guckt sich eher die WHERE-Bedingung an, oder vielleicht die SELECT-Bedingung sogar?

Schöne Grüße aus Rheinland,

Eure Ratna

 

[Yaslaw]

ON ist auch eine WHERE-Bedinung im weiteren Sinn.

 

[MPr]

aus Gründen der Vollständigkeit und da die Überschrift ja die Frage nach einer Faustregel beinhaltet noch der Hinweis, dass sich der Performancegewinn im gegebenen Fall dadurch ergibt, dass die beiden Indizes hier als schlankere Varianten der Tabelle verwendet werden können (wobei sich die Verbesserung auch nur ergibt, wenn die Tabellen neben den indizierten noch weitere Spalten besitzen) - in Oracle ergibt sich ein INDEX FAST FULL SCAN für beide Indizes (was eine Art FULL TABLE SCAN aller LEAF-Blocks des Indes ist). Allerdings gibt es keine selektive Bedingung, die die Daten einschränken würde - die Reduzierung der Daten erfolgt nur durch den Join:

drop table tabelle_a;
drop table tabelle_b;

-- zwei identische Tabellen
create table tabelle_a
as
select rownum column_01
     , mod(rownum, 10) column_02
     , lpad('*', 10, '*') padding
  from dual
connect by level <= 100000;

create table tabelle_b
as
select rownum column_01
     , mod(rownum, 10) column_02
     , lpad('*', 10, '*') padding
  from dual
connect by level <= 100000;

-- Korrektur eines Satzes, so dass die Query einen Ergebnissatz liefern kann
update tabelle_b set column_02 = 42 where column_01 = 1;

exec dbms_stats.gather_table_stats(user, 'tabelle_a')
exec dbms_stats.gather_table_stats(user, 'tabelle_b')

create index tabelle_a_idx on tabelle_a(column_01, column_02);
create index tabelle_b_idx on tabelle_b(column_01, column_02);

SELECT
    a.column_01,
    a.column_02,
    b.column_02
FROM
    tabelle_a a
INNER JOIN
    tabelle_b b 
ON 
    a.column_01 = b.column_01 AND a.column_02 != b.column_02;

 COLUMN_01  COLUMN_02  COLUMN_02
---------- ---------- ----------
         1          1         42

Abgelaufen: 00:00:00.07

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 652612413

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               | 90909 |  1420K|       |   731   (1)| 00:00:04 |
|*  1 |  HASH JOIN            |               | 90909 |  1420K|  1960K|   731   (1)| 00:00:04 |
|   2 |   INDEX FAST FULL SCAN| TABELLE_A_IDX |   100K|   781K|       |   133   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| TABELLE_B_IDX |   100K|   781K|       |   133   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - access("A"."COLUMN_01"="B"."COLUMN_01")
       filter("A"."COLUMN_02"<>"B"."COLUMN_02")


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        541  consistent gets
          0  physical reads
          0  redo size
        682  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Oracles Autotrace zeigt die INDEX FAST FULL SCANs und zeigt außerdem, dass hier 541 consistent gets erforderlich waren, um das Ergebnis zu liefern, was relativ viel ist, wenn man es mit einem Zugriff mit einer selektiven Bedingung vergleicht, die einen INDEX RANGE SCAN ermöglicht:

SELECT
    a.column_01,
    a.column_02,
    b.column_02
FROM
    tabelle_a a
INNER JOIN
    tabelle_b b
ON
    a.column_01 = b.column_01 AND a.column_02 != b.column_02
and a.column_01 = 1

 COLUMN_01  COLUMN_02  COLUMN_02
---------- ---------- ----------
         1          1         42

Abgelaufen: 00:00:00.01

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 884188798

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    16 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS     |               |     1 |    16 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| TABELLE_A_IDX |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| TABELLE_B_IDX |     1 |     8 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("A"."COLUMN_01"=1)
   3 - access("B"."COLUMN_01"=1)
       filter("A"."COLUMN_02"<>"B"."COLUMN_02")


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        682  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Hier reduziert sich die Arbeit auf 6 consistent gets.

Einen sehr guten Einstieg in Fragestellungen der Indizierung und SQL-Optimierung liefert übrigens Markus Winands (auch deutschsprachige) Webseite http://use-the-index-luke.com/de.

Viele Grüße

Martin Preiß

Nachtrag: und noch eine Ergänzung aus Gründen der Vollständigkeit: wenn eine Tabelle sehr klein wäre, dann könnte der Join als NESTED LOOPs erfolgen und dann könnte die Join-Bedingung verwendet werden um einen indizierten Lookup gegen die zweite Tabelle durchzuführen. Aber für Tabellen mit mehreren 100M rows kommt eigentlich nur der HASH JOIN mit IFFS in Frage.

 

[oraclin25]

Guten Morgen sheel, Yaslaw und MPr,

vielen lieben Dank für Eure Hilfestellungen. Vor allem der Ausführungsplan von MPr hat mir sehr geholfen, aber leider auch gezeigt, dass bei einer ernsthaften Beschäftigung mit SQL früher oder später das Thema Optimierung einem begegnet. Ich war immer der Einstellung "hauptsache der Code läuft". Aber wenn der Code so arg lange dauert, vergeht mir der Spaß schon ein bisschen. Also, das Thema Optimierung, es muss..

Euch vielen Dank nochmal. Ich werde sicherlich noch öfter im Forum bzgl. SQL fragen. Bis denne.

Schöne Grüße aus Rheinland,

Eure Ratna

 

[j2se]

Stimmt, die Optimierung wird unter Oracle schnell zum Thema. Es tut ja nicht weh, wenn man über Ausführungspläne, Ausführungskosten etc. Bescheid weiss. Will man sich zum Data Warehouse Spezi weiterbilden, gehört das zum Standard Knowhow. Analysiert man den Ausführungsplan bekommt man mit der Zeit ein gutes Gefühl wie das DBMS das SQL behandeln wird und welche Konstrukte zu bevorzugen sind und welche nicht.