SQL - Index als LF erstellen

[_MG_]

Hallo zusammen,

ich lese mir aktuell ein Buch über die DB2 durch.
Das Buch ist sehr gut, nun bin ich leider auf etwas gestoßen was sich mir nicht erschließen mag.

Folgendes Beispiel:
1. Mit den SQL Befehlen
CREATE INDEX I_PLZ ON ITPSQL.ORT(PLZ);
CREATE INDEX I_LKZ ON ITPSQL.ORT(LKZ);

wird ein Index gesetzt welcher sich jeweils als logische Datei darstellt. Soweit ja einfach.



2. Jetzt geht das Buch hin und erstellt zwei Select Abfragen:

select * from itpsql.ort where plz = ‚83646‘;
select * from itpsql.ort where LKZ = ‚40‘;



3. Laut dem Buch wird bei der ersten Abfrage der Index verwendet, bei der zweiten jedoch nicht.


Ich zitiere mal:
„Sie sehen, obwohl die Abfragen fast identisch sind und für beide Abfragen ein Index zur Verfügung steht, fällt die Ausführung unterschiedlich aus!“

Mehr Infos stellt das Buch leider nicht zu Verfügung.

Meine Frage:
Gibt es sozusagen „Regeln“ an denen man sich orientieren kann ob ein Index, der mittels SQL erstellt wurde, verwendet wird oder nicht?

Danke im Voraus für Antworten.

Grüße

[dschroeder]

Ich glaube das Buch will sagen, dass man als Programmierer nicht wissen kann, ob ein Index verwendet wird oder nicht. Das macht der Query optimizer in der Maschine. Es gibt ja den System i Navigator. Dort gibt es den sogenannten Index Adviser. Damit kann man feststellen, ob das System weitere Indizes haben möchte.

Aus meiner Sicht muss es dem Programmierer zunächst mal egal sein, wie das System die Daten liefert (mit oder ohne Indexzugriff), Hauptsache es geht ausreichend schnell.

Ich glaube nicht, dass jemand außerhalb des IBM Labors sagen kann, welche Regeln es für den Query Optimizer gibt. Es gibt natürlich bestimmte Fälle, in denen man sofort ausschließen kann, dass ein Index verwendet wird (z.B. bei bestimmten Suche mit like).

Ich würde beim Programmieren darauf achten, dass zunächst mal die Indizes angelegt werden, von denen man weiß, dass sie sinnvoll sind. Z.B. wenn ich per Kundennummer zugreife, dass dann auch ein Index über die Kundennummer da ist. Alle weiteren würde ich von der Performance und von den Angaben des Index Advisors abhängig machen.

Dieter

[andreaspr@aon.at]

Der Optimizer errechnet mit Hilfe der statistischen Daten von Indices und Tabelle ob und welcher Index (oder Indice) für die Abfrage sinnvoll ist.

Z.B. wenn die Tabelle nur 10 Zeilen beinhaltet, wird maximal beim 1. Select der Index genommen und dann immer ein TABLE SCAN gemacht.
Da in solch einen Fall aufwändiger wäre über einen Index zu gehen, egal welche Spalte als Auswahlkriterium hergenommen wird.

Aber das Thema ist sehr umfangreich und ist schwer hier in kürze zusammenzufassen.

[_MG_]

Vielen Dank für die schnellen und gut verständlichen Antworten.
mhm... ich war bisher der Meinung das es IMMER schneller ist einen Index zu verwenden aber gut...


Eine Frage hätte ich noch.
Wenn ich auf diese Art und Weise einen Index erstelle, muss der sich in der selben Bibliothek befinden wie die Physische-Datei oder das unabhängig voneinander.

[dschroeder]

Es ist egal, in welcher Bibliothek der Index steht.

[andreaspr@aon.at]

Wo sich der Index befindet ist der DB egal.
Es kann sogar aus BackUp&Recovery Gründen Sinn machen Indice in eine eigene Lib zu erstellen bzw. separat zu Sichern.
Das kommt auf das B&R Konzept an.
Z.B. wenn es wichtig ist, dass zuerst mal alle Tabellen und die wichtigsten Indice wiederhergestellt werden und die restlichen Indice erst danach neu erstellt werden.

Ansonsten ist es egal.

[Fuerchau]

"3. Laut dem Buch wird bei der ersten Abfrage der Index verwendet, bei der zweiten jedoch nicht."

Woher nimmt das Buch diese Aussage?
Wenn genügend Daten da sind, dass sich der Indexzugriff lohnt wird er auch verwendet (wie oben schon mal gesagt).
Hat der Index allerdings nur 1 Schlüssel (also alle Sätze identisch) ist ein Tablescan durchaus effektiver.
Dazu müsste man das Beispiel (bzw. die Daten dazu) aus dem Buch kennen.

Je höher die "Selektivity" (Anzahl unterschiedlicher Schlüssel in Releation zur Anzahl Sätze), desto wahrscheinlicher die Verwendung des Index.

[B.Hauser]

"3. Laut dem Buch wird bei der ersten Abfrage der Index verwendet, bei der zweiten jedoch nicht."

Woher nimmt das Buch diese Aussage?

Wahrscheinlich aus der Annahme, das in einer Adress-Tabelle mehr unterschiedliche Postleitzahlen wie unterschiedliche Länder vorhanden sind.
Das ist allerdings eine allgemeine Aussage!

Auf IBM i wird die Entscheidung, ob, wann und wie ein Index verwendet wird von den Statistik-Informationen (Informationen über die Anzahl der unterschiedlichen Schlüssel-Werte und Anzahl der Datensätze, die diese Schlüssel-Werte haben) beeinflusst.

Dazu kommt noch die Art des Indexes, d.h. Binary Radix Tree Indexes (Traditionelle Indixes) werden nur verwendet, wenn max. 15 - 20% der Datensätze zurückgegeben werden.

Zwischen ca. 20 - 70% können Encoded Vector Idices verwendet werden.

Der Optimizer kann auch in unterschiedlichen Situationen unterschiedliche Access Plans erstellen, d.h. wenn mit einem Kunden 80% des Umsatzes gemacht werden, wird bei Auswahl des Kunden kein Index verwednet. Wenn allerdings ein anderer Kunde mit einem Auftrag ausgewählt wird, kann ein Index nach Kunde verwendet werden.

Der Optimizer ist sogar in der Lage für ein und die selbe Datei in ein und derselben Abfrage mehrere Indices gleichzeitig zu verwenden (Index Anding und Index Oring)

Ich würde das folgende White Paper empfehlen:

IBM DB2 for i indexing methods and strategies
Learn how to use DB2 indexes to boost performance


Birgitta

[_MG_]

Verstehe...
Vielen Dank für die Rückmeldungen
Grüße!!!