26. November 2007

Case- und Umlaut-"insensitive" Suche ... und das mit Index!

Erst kürzlich wurde ich von gefragt, wie man denn in der Oracle-Datenbank eine Case-Insensitive Suche durchführen kann - schließlich möchte man dem Endanwender nicht zumuten, den Kundennamen immer in der richtigen Schreibweise eingeben zu müssen. Ein erster Ansatz wäre das Verwenden einer Funktion wie UPPER oder LOWER und dann die Nutzung eines function based Indexes:
create index on kunden_tab (upper(name))
Allerdings greift dieses Beispiel (speziell im deutschsprachingen Raum) etwas zu kurz. Sobald Umlaute ins Spiel kommen, braucht man noch etwas mehr Funktionalität. So wird der Name Müller gerne auch mal als MULLER gesucht. Man bräuchte also eine Suchabfrage, welche u.U. auch Umlaute ignoriert.
Speziell hierfür gibt es seit Oracle10g eine Erweiterung des NLS_SORT-Parameters. Während NLS_SORT=GERMAN erstmal nur eine andere Sortierreihenfolge für die Umlaute wählt, sorgen NLS_SORT=GERMAN_CI dafür, dass Groß- und Kleinschreibung und NLS_SORT=GERMAN_AI dafür, dass die "diakritischen Zeichen" ignoriert werden. Und das besondere ist: Setzt man NLS_COMP=LINGUISTIC, dann gelten diese Einstellungen nicht nur für das Sortieren, sondern auch für das Filtern mit der WHERE-Klausel. Das folgende Beispiel zeigt die Funktionalität:
create table daten (col varchar2(200))
/

insert into daten values ('heinz');
insert into daten values ('Heinz');
insert into daten values ('Müller');
insert into daten values ('Muller');
insert into daten values ('Klara');
insert into daten values ('Fleiß');

commit
/

create index idx_daten on daten (col)
/

-- Einstellen auf Case-Insensitive Suche

alter session set nls_comp=linguistic
/
alter session set nls_sort=german_AI
/

select * from daten where col='MULLER'
/

COL
--------------------------------------------------------------------
Müller
Muller
Allerdings zeigt ein Blick auf den Ausführungsplan, dass der vorher erzeugte, "normale" Index nun nicht mehr zieht ...
explain plan for
select * from daten where col='MULLER'
/

select * from table(dbms_xplan.display())
/

Plan hash value: 914527404

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |   102 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DATEN |     1 |   102 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("COL",'nls_sort=''GERMAN_AI''')=HEXTORAW('50734B4B28640001010101010100') )

Also wird der Index als linguistischer Index neu gebaut:
drop index idx_daten
/

create index idx_daten on daten (NLSSORT(col, 'NLS_SORT = German_AI'))
/
Erneuter Test ...
select * from daten where col='MULLER'
/

COL
---------------------------------------------------------------------

Müller
Muller

explain plan for
select * from daten where col='MULLER'
/

select * from table(dbms_xplan.display())
/

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   909 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DATEN     |     1 |   909 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DATEN |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NLSSORT("COL",'nls_sort=''GERMAN_AI''')=HEXTORAW('50734B4B28640001010101010100'))
Mehr Informationen zum Thema "linguistische Indizes" finden sich in der Dokumentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch5lingsort.htm#i1006421.

Kommentare:

Patrick Wolf hat gesagt…

Das Problem mit nls_comp=linguistic ist (zumindest in 10g), dass bei einem LIKE kein Index Range Scan mehr gemacht wird. Was eine doch nicht unwesentliche Auswirkung auf die Performance sein kann.

Ich glaube aber irgendwo gelesen zu haben, dass sich da in 11g was getan hat.

Patrick

Carsten Czarski hat gesagt…

Ich habe es gerade getestet. In 11g (11.1.0.6) funktioniert die Indexnutzung auch mit LIKE blasenfrei - in 10.2.0.3 nicht. U.U. tut sich was im Patchset 10.2.0.4 ...

Beliebte Postings