Index wird nicht verwendet

[ZOD]

@jobo
heute scheint mein zentrlaler Verarbeitungsknoten (Gehirn) wirklich einen Knoten zu haben.

Als erstes eine Fehlerkorrektur:

Code:

  (:test_value = cast('*' as varchar(201)))
  or
  ((not (:test_value = cast('*' as varchar(201)))) and (t.key_value = :test_value))

ist bei einem Integer-Feld (key_value) nicht sehr sinnvoll. Sorry - das ist mir beim "Vereinfachen" passiert.
Aber dieser Teil der

Code:

where

Bedingung kann auch einfach wegbleiben - das Verhalten ist das gleiche.

Aber das verstehe ich nicht:

Zitat:

(f_lrtrim8(f_upper(t.name)) = f_lrtrim(f_upper(:test_name))
enthält eine Funktion die auf Feld angewendet wird.
Damit ist ein Index idR. nicht mehr zu gebrauchen.

Bisher hatte ich hier nie Probleme. Als Funktionsbibliothe ist freeadhocudf eingebunden.

[mkinzler]

Das Problem ist nicht die funktion sondern deren Verwendung in der Abfrage. Im index stehen die originalen mixed-case Strings der Tabelle mit voller Länge und nicht die getrimmten Uppercase-Varianten.
Wandele den Index in einen entsprechenden expression index, dann sollte der Index verwendet werden.

[bnreimer42]

Woher soll der DB-Server - also konkret der Optimizer wissen - wie die Funktion die auf Feld oder Parameter angewendet wird, diese verändert?

Es hilft, einen Index mit dem Ausdruck zu erstellen, auf den Du abprüfst.
Siehe http://www.firebirdtest.com/file/doc...5-ddl-idx.html unter Computed (Expression) Indexes bzw. ein Beispiel ganz unten.

Achte darauf, beim Index exakt die selben Funktionsaufrufe zu verwenden, wie bei der Abfrage, sonst nutzt er ihn nicht. Konkret ist es ja egal, ob erst trim oder erst upper angewendet wird.


Übrigens gibt es die Funktionen auch eingebaut:
https://firebirdsql.org/refdocs/lang...unc-upper.html
https://firebirdsql.org/refdocs/lang...func-trim.html

Ändert aber am Problem nichts.

[ZOD]

danke für die Antworten.

Verständnisfrage:
ich muss also IMMER einen Index anlegen, der EXAKT die Funktionsaufrufe der where-Bedingung erfüllt?
Bisher dachte ich immer, dass der Optimizer für die angesprochenen Spalten das Vorhandensein eines Index prüft und ihn dann verwendet,
eben auch bei Weiterbehandlung in einem Funktionsaufruf.

[mkinzler]

Dies wäre dann aber nicht schneller als die Suche in der Originaltabelle.

[jobo]

Zitat von ZOD:

ich muss also IMMER einen Index anlegen, der EXAKT die Funktionsaufrufe der where-Bedingung erfüllt?

Wenn Du eine Funktion in der Whereclause auf ein Feld legst, ja.
jein.
nein.

Es ist ja relativ klar, dass man nicht für alle möglichen Abfragen der Welt funkionale Indizes bauen kann.

Du musst also immer überlegen, wie Du die Dinge anpackst.

Bspw. wäre ein sinnvolles Vorgehen:
Funktionsaufruf in Where Clause nur in hinreichend kleinen Teilmengen.
Einschränkung großer Mengen demzufolge über andere sinnvolle Kriterien wie Foreign Keys, Datumswerte, -Ranges, Klassen usw. usf. Hauptsache, die Einschränkungen sind stark und natürlich index fähig.

Wenn dann am Ende auch ein paar Hundert Records übrigbleiben, ist es kein Problem, darauf mit Funktionen zu arbeiten. Dabei ist es dann fallweise dennoch so, dass der Optimizer mal auf der falschen Fährte ist, dann muss man helfend eingreifen.

Bei mir sieht es z.B. so aus, dass ich teilweise Views verwende, die gar nicht ohne Where Clause abgefragt werden können, weil viel zu lahm. Erst im Kontext der Anwendung mit den Parametern, die fachlich sinnvoll und notwendig sind, spuckt der View schön brav Datensätze aus, blitzschnell.