Groß- und Kleinschreibung mit UPPER oder LOWER ignorieren


Gilt für
DB2Ja
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.

SQL-Performance im Griff
Bei unseren Schlulungs-, Tuning-, und
Literaturangeboten ist für jeden was dabei

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

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.

?Recent questions at
Ask.Use-The-Index-Luke.com

2
votes
1
answer
1.5k
views
0
votes
2
answers
859
views

different execution plans after failing over from primary to standby server

Sep 17 at 11:46 Markus Winand ♦♦ 741
oracle index update
1
vote
1
answer
291
views

Generate test data for a given case

Sep 14 at 18:11 Markus Winand ♦♦ 741
testcase postgres