Groß- und Kleinschreibung mit UPPER oder LOWER ignorieren


Gilt für
MySQLNein
OracleJa
PostgreSQLJa
SQL ServerJa

Die Groß- und Kleinschreibung bei einer SQL-Abfrage zu ignorieren, ist relativ einfach: Man kann zum Beispiel beide Seiten des Vergleiches direkt in der Abfrage auf Großbuchstaben konvertieren:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand')

Die UPPER-Funktion führt zu den gewünschten Übereinstimmungen – egal, in welcher Schreibweise die Namen gespeichert sind oder wie der Such­begriff angegeben wird.

Beachte

Eine andere Möglichkeit die Groß- und Kleinschreibung zu ignorieren ist, die Sortierung einer Spalte zu ändern. Dazu verwendet man sogenannte „Collations“. SQL Server und MySQL verwenden in der Standard-Konfiguration Collations, die nicht zwischen Groß- und Kleinbuchstaben unterschieden.

Die Logik der UPPER-Methode ist einwandfrei. Der Ausführungsplan aber nicht:

Oracle
Versuch's online auf SQL Fiddle----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |   10 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |   10 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME")='WINAND')
PostgreSQL
Versuch's online auf SQL Fiddle                     QUERY PLAN
------------------------------------------------------
 Seq Scan on employees
   (cost=0.00..1722.00 rows=50 width=17)
   Filter: (upper((last_name)::text) = 'WINAND'::text)

Unser alter Freund, der Full-Table-Scan, taucht wieder auf. Obwohl ein Index auf LAST_NAME vorhanden ist, kann er nicht benutzt werden – weil nicht nach LAST_NAME gesucht wird. Die Suche erfolgt auf UPPER(LAST_NAME). Das ist aus Sicht der Datenbank etwas völlig anderes.

8.-12. September 2014. 5-Tage geballtes Oracle Performance Know-How mit Christian Pfundtner (OCM) und Markus Winand.

Es ist eine Falle, in die wir alle gerne tappen. Wir verstehen den Zusam­men­hang zwischen LAST_NAME und UPPER(LAST_NAME) und erwarten, dass die Datenbank diesen Zusammenhang auch kennt. Aus Sicht der Datenbank sieht die Abfrage aber eher so aus:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE BLACKBOX(...) = 'WINAND';

Die UPPER-Funktion ist nur eine Blackbox. Die Parameter des Funk­ti­ons­aufrufs sind völlig unerheblich, weil zwischen den Parametern und dem Ergebnis einer Funktion kein allgemeiner Zusammenhang besteht.

Tweet this tip

Tipp

Wenn man sich eine Blackbox statt der Funktion vorstellt, kann man die Datenbank besser verstehen.

Auflösung zur Compile-Zeit

Der Optimizer kann den rechten Ausdruck auflösen, da alle Eingangsparameter bekannt sind. Der Ausführungsplan (Predicate Information) zeigt der Suchbegriff daher nur noch in Großbuchstaben an. Dieses Verhalten ist ähnlich einem Compiler, der konstante Ausdrücke zur Compile-Zeit auflöst.

Damit ein Index benutzt werden kann, muss er den tatsächlichen Such­be­griff beinhalten. Das heißt, man darf nicht LAST_NAME indizieren, sondern UPPER(LAST_NAME):

CREATE INDEX emp_up_name 
    ON employees (UPPER(last_name));

Ein Index, dessen Definition Funktionen oder Ausdrücke enthält, wird Funktions-basierender Index (FBI) genannt. Dabei werden die Tabellendaten nicht eins zu eins in den Index kopiert, sondern das Ergebnis des Ausdruckes verwendet. Der Index speichert die Namen also in Großschreibung.

Die Datenbank kann einen Funktions-basierenden Index nutzen, wenn der Ausdruck aus der Indexdefinition unverändert in der SQL-Anweisung verwendet wird – wie im Beispiel oben. Der Ausführungsplan bestätigt das:

Oracle
???--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |  100 |   41 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |  100 |   41 |
|*2 |  INDEX RANGE SCAN          | EMP_UP_NAME |   40 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')
PostgreSQL
???                       QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on employees
  (cost=4.65..178.65 rows=50 width=17)
  Recheck Cond: (upper((last_name)::text) = 'WINAND'::text)
  -> Bitmap Index Scan on emp_up_name
     (cost=0.00..4.64 rows=50 width=0)
     Index Cond: (upper((last_name)::text) = 'WINAND'::text)

Dabei wird ein normaler INDEX RANGE SCAN genutzt, wie er in Kapitel 1 be­schrie­ben wurde. Der Indexbaum wird durchwandert und die Blattknoten-Liste verfolgt. Für einen Funktions-basierenden Index gibt es keine eigenen Zugriffs-Operationen oder Schlüsselwörter.

Warnung

UPPER und LOWER werden von ORM-Tools manchmal ohne Wissen der Entwickler verwendet. Hibernate verwendet zum Beispiel die LOWER-Funktion, um Case-insensitive Suchen umzusetzen.

Der Ausführungsplan stimmt aber noch nicht ganz mit dem ohne UPPER überein. Die geschätzte Zeilenzahl ist viel höher. Besonders auffällig ist, dass die Zeilen beim Tabellenzugriff höher sind als beim INDEX RANGE SCAN. Wie kann es hundert Tabellenzugriffe geben, wenn der Indexzugriff nur 40 ROWIDs liefert? Es ist unmöglich. Solche Widersprüche deuten häufig auf Probleme mit den Statistiken hin. In diesem Fall liegt es daran, dass die Oracle Datenbank die Tabellenstatistiken beim Anlegen eines neuen In­dexes nicht aktualisiert (siehe „Oracle-Statistiken für funktions-basierende Indizes“).

Oracle-Statistiken für funktions-basierende Indizes

Die Statistiken über die Anzahl der verschiedenen Werte jeder Spalte werden von der Oracle Datenbank auf Tabellenebene geführt. Dadurch können diese Werte für mehrere Indizes genutzt werden, wenn eine Spalte mehrfach indiziert ist.

Die Statistiken für Funktions-basierende Indizes (FBI) werden mittels virtueller Spalten ebenfalls auf Tabellenebene geführt. Die Oracle Datenbank ermittelt zwar die Index-Statistiken beim Anlegen des Indexes ab Version 10g automatisch, aktualisiert die Tabellen-Statistiken aber nicht. Die Oracle-Dokumentation empfiehlt daher, nach dem Anlegen eines Funktions-basierenden Indexes auch die Tabellenstatistiken zu aktualisieren:

After creating a function-based Index, collect statistics on both the Index and its base table using the DBMS_STATS package. Such statistics will enable Oracle Database to correctly decide when to use the Index.

Ich gehe in meiner Empfehlung noch weiter und rate bei jeder Index-Änderung sowohl die Statistiken der Tabelle als auch die ihrer Indizes zu aktualisieren. Da das aber auch zu unerwünschten Effekten führen kann, sollte man diesen Vorgang mit den DBAs koordinieren und die alten Statistiken vorher sichern.

Nach dem Aktualisieren der Statistiken gibt der Optimizer eine bessere Schätzung ab:

Oracle
???--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')
PostgreSQL
???                      QUERY PLAN
----------------------------------------------------------
 Index Scan using emp_up_name on employees
   (cost=0.00..8.28 rows=1 width=17)
   Index Cond: (upper((last_name)::text) = 'WINAND'::text)

Da die Zeilenschätzung jetzt geringer ausfällt – von vormals 50 auf 1 – verwendet der Query Planner die einfachere Index Scan Operation.

Beachte

Statistiken auf Ausdrücke und Spaltenkombinationen wurden mit Oracle release 11g eingeführt.

Obwohl die Ausführungsgeschwindigkeit durch die neuen Statistiken nicht besser wird – der Index wurde ohnehin richtig genutzt – ist es immer gut, einen Blick auf die Schätzungen des Optimizers zu werfen. Gerade die Zeilenschätzung (cardinality estimate) ist ein sehr wichtiger Wert, der auch in den Ausführungsplänen von SQL Server und PostgreSQL auftaucht.

Tipp

Anhang A, „Ausführungspläne, zeigt, wie die Zeilenschätzung in SQL Server- und PostgreSQL-Ausführungsplänen dargestellt wird.

SQL Server unterstützt Funktions-basierende Indizes in dieser Form nicht. Stattdessen kann man berechnete Spalten (computed columns) für denselben Zweck verwenden. Dazu muss man der Tabelle zuerst eine berechnete Spalte hinzufügen, die man dann indizieren kann:

ALTER TABLE employees ADD last_name_up AS UPPER(last_name);
CREATE INDEX emp_up_name ON employees (last_name_up);

SQL Server kann den Index verwenden, wenn der indizierte Ausdruck in der Anweisung auftaucht. Man muss also die Abfragen nicht umschreiben, um den Namen der berechneten Spalte zu verwenden.

Über den Autor

Markus Winand hat sich als Autor, Trainer und Coach darauf spezialisiert, Entwicklern bei Problemen mit SQL-Performance zu helfen. Er hat das Buch „SQL Performance Explained“ veröffentlicht und twittert seine besten Tipps als @SQLPerfTipps.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
101
views
0
votes
0
answers
349
views

Fanout in R-Tree

Mar 27 at 08:07 jamie 1
tree indexing
0
votes
1
answer
132
views

Think About It

Mar 26 at 12:54 Markus Winand ♦♦ 511
reflection