Index wird nicht verwendet

[ZOD]

Hallo Zusammen,

ich habe folgende Datenbanktabelle:

Code:

/******************************************************************************/
/****              Generated by IBExpert 21.11.2017 13:19:19               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/
CREATE GENERATOR G_TEST;

CREATE TABLE T_TEST (
    ID         INTEGER NOT NULL,
    NAME       VARCHAR(201),
    KEY_VALUE  INTEGER
);


/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE T_TEST ADD CONSTRAINT PK_T_TEST PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/
CREATE INDEX T_TEST_IDX1 ON T_TEST (NAME);
CREATE INDEX T_TEST_IDX2 ON T_TEST (KEY_VALUE);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/
SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/
/* Trigger: T_TEST_BI0 */
CREATE OR ALTER TRIGGER T_TEST_BI0 FOR T_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
    new.id = GEN_ID(G_TEST, 1);
end
^

SET TERM ; ^

Die Tabelle habe ich mit 30.000 Testdatensätzen befüllt.

Wenn ich nun folgenden Abruf mache:

Code:

select
t.*
from t_test t
where
(
  (:test_name = cast('*' as varchar(201)))
  or
  ((not (:test_name = cast('*' as varchar(201)))) and (f_lrtrim8(f_upper(t.name)) = f_lrtrim(f_upper(:test_name))))
)
and
(
  (:test_value = cast('*' as varchar(201)))
  or
  ((not (:test_value = cast('*' as varchar(201)))) and (t.key_value = :test_value))
)

und dabei folgende Parmeterbelegung verwende:

Code:

TEST_NAME = '*' <- also nur das Zeichen als String
TEST_VALUE = 12

dann wird folgender Plan verwendet:

Code:

Plan:
PLAN (T NATURAL)

Der Index wird also nicht benutzt.

Ändere ich die Abfrage wie folgt:

Code:

select
t.*
from t_test t
where
t.name = f_lrtrim(f_upper(:test_name))
and
t.key_value = :test_value

wird bei gleicher Parameterbelegung dieser Plan verwendet:

Code:

Plan:
PLAN (T INDEX (T_TEST_IDX1, T_TEST_IDX2))

Jetzt wird der Index benutzt.

Warum wird bei der ersten Abfrage der Index nicht verwendet und wie kann ich das vermeiden?
Ich vermute, dass dies an der Parameterabfrage liegt.

Anmerkung:
dies ist ein vereinfachtes Beispiel aus einer komplexeren Konstellation heraus.
Es ist daher wirklich nicht einfach möglich, die Parameter zu vermeiden bzw. anders zu belegen ;-(.



Danke.

[ZOD]

Sorry - es fehlt noch eine Information:

Wenn ich die Parameter wie folgt belege:

Code:

TEST_NAME = 'TEST' <- also nur die Zeichen als String
TEST_VALUE = 12

dann habe ich das gleiche Verhalten, der Plan für die erste Abfrage wir ohne Index erzeugt und bei der zweiten Abfrage mit Index.

[jobo]

Mal so grob überflogen:
(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.
Ausnahme sind extra dafür erstellte Function Based Indices.


Das OR kann auch eine Rolle spielen, wobei ich mir nicht sicher bin (wie bei allen Optimizern letztlich) wie geschickt der Optimizer die Anfrage und die Indizierung und die vorhandene "Datenlage" auswertet.

Was passieren kann:
1 OR zwingt das System dazu beide Bedingungen unabhängig zu prüfen.
(auch wenn hier die Besonderheit vorliegt, dass kein Feldinhalt geprüft wird, sondern ein Parameter selbst- was der Optimizer vielleicht auch nicht schnallt)
Mögliche Logik/Schlussfolgerung des Optimizers:
Wenn ich eh mehrere Dinge prüfen muss, ist es schneller, einmal alles zu scannen (egal welche Indizes da sind).

Die Funktionsproblematik Upper(Feldname) bekommst Du vielleicht in den Griff, wenn Du im Trigger schon das Upper machst und nicht mehr in der Abfrage. Geht natürlich nur, wenn der Originalwert unbedeutend ist.

[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:

Zitat von ZOD (Beitrag 1386807)

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.