Dynamisches SQL mehrere where klauseln

[B.Hauser]

Da kann ich Dieter nur zustimmen.
Die Reihenfolge der Whereklausel ist bei Vergleichen ungleich "=" von entscheidender Bedeutung:

where F1 between :#V1 and :#V2
and F2 between :#V3 and :#V4

Wenn V1 = Minval und V2 = Maxval wird ein Tablescan erzwungen.

Das war einmal! Zu CQE-Zeiten.
Heute wird das SQL-Statement analysiert und in Verbindung mit dem Statistics Manager, der Informationen darüber liefert wie viele verschiedene Schlüssel und Werte ein bestimmter Index liefert und wie schnell man mit diesem Index an die Daten kommt (bzw. wieviel das "kostet").
Die Indices werden vorselektiert, d.h. es wird zunächst geprüft, ob sich die Join-Felder, die Felder, die in den WHERE-Bedingungen auf =, IN und BETWEEN geprüft werden als Schlüssel-Felder in Indices hinterlegt sind. Dann werden die verschiedenen Indices bewertet und sobald ein "teurer" Index gefunden wird, wird der "günstigste" Index verwendet und die weitere Prüfung abgebrochen.
Ein (Binary Radix Tree) Index wird i.Ü. nur verwendet wenn weniger als 15-20% der Daten selektiert wurden.
Zwischen 15-20 und 70-80% kann der Optimizer auch noch einen Encoded Vector Index (EVI) verwenden, ansonsten wird die ganze Tabelle verarbeitet, (wobei auch nicht mehr Table Scans verwendet werden, sondern meist über RRN- und Values Listen gearbeitet wird.

[Fuerchau]

De Technik dahinter ist mir letztlich egal.
Mein Erlebnis ist einfach (auch noch mit V7R4, dass solche Abfragen einfach langsam sind.
Bei nur ein paar 1000 Zeilen braucht man keine Indizes, da ist die Kiste sowieso schnell.
Bei ein paar Millionen Zeilen wird es schon mal kritisch.

Außerdem benötigen meine Queries in der Regel mehr als 1 Tabelle (auch schon mal bis zu 40), da gelten dann noch weitere Regeln. Ein Index alleine hilft da eher wenig.
Und ich habe schon genug Optimizervorschläge bekommen und ausprobiert ohne dass diese Indizes dann auch genommen wurden.
Auch so seltsame Vorschlage, statt dem Index "Firma, Werk, Teil" einen Index "Firma, Teil, Werk" zu nehmen entzieht sich mir da im Anschluss ja trotzdem der 1. Index verwendet wird.

Und ist ein RRN-Zugriff nichts anderes als TableScan?

Es steht außer Frage, dass die IBM i einen inzwischen besseren Optimizer hat. Meine Erfahrung ist, dass ich mit selbst erstellten Indizes sehr häufig bessere Laufzeiten erziele, weil der Optimizer diese dann verwendet. Da ist dann auch schon mal ein Index-Only-Zugriff dabei.