In jeder APEX-Anwendung wird mit SQL-Abfragen gesucht - und zwar stets dann, wenn man eine WHERE-Klausel verwendet. Für die vom Endanwender eingestellten Filter eines interaktiven Berichten muss man hier nicht viel machen; APEX generiert die SQL-WHERE-Klausel selbstständig. Möchte man jedoch im Berichts-SQL selbst eine WHERE-Klausel einbauen oder verwendet man klassische Berichte, so kann es ganz hilfreich sein, den einen oder anderen Such-Trick zu kennen.

Für den ersten "klassischen" APEX-Bericht auf einer Anwendungsseite kann man die Berichtssuche aktivieren (Abbildung 1).

Abbildung 1: Berichtssuche aktivieren für einen klassischen Bericht

APEX generiert daraufhin folgende SQL-Abfrage

select * from (
  select * from emp
)
where (
  instr(upper("ENAME"),upper(nvl(:P2_REPORT_SEARCH,"ENAME"))) > 0  or
  instr(upper("JOB"),upper(nvl(:P2_REPORT_SEARCH,"JOB"))) > 0 
)

Die bei Berichtserstellung angegebene SQL-Abfrage wird nochmals "eingerahmt": Die äußere SQL-Abfrage implementiert die "Suche" mit Hilfe der SQL-Funktion INSTR. Damit findet eine Teilstringsuche statt. Allerdings wird für diese niemals ein Index verwendet - bei großen Tabellen kann eine solche Suche also schonmal etwas länger dauern. Die folgenden Tests wurden mit SQL*Plus gemacht; natürlich kann man die gleichen Effekte auch mit dem SQL Developer oder dem APEX SQL-Workshop erreichen. Für die Tests wurde eine Tabelle BIGEMP erstellt, das ist die Tabelle EMP mit mehreren Millionen Zeilen - lediglich JONES kommt nur einmal vor.

SQL> create index BIGEMP_IX_ENAME on BIGEMP (ENAME)
/

SQL> select empno, ename, job, sal from bigemp where instr(ename, 'JON')>0;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:02.59

Mehr als zwei Sekunden ist für eine solche Abfrage sicherlich zu lang. Der Ausführungsplan zeigt, dass der Index nicht verwendet werden konnte.

Ausführungsplan

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   340K|  7321K| 11088   (2)| 00:02:14 |
|*  1 |  TABLE ACCESS FULL| BIGEMP |   340K|  7321K| 11088   (2)| 00:02:14 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(INSTR("ENAME",'JON')>0)

Und das ist logisch - denn die INSTR-Funktion findet ja nicht nur einfach den Teilstring, sie liefert sogar die Position zurück, an der dieser gefunden wurde. Ein Index kann hier nicht helfen. Eine Alternative ist der SQL LIKE Operator ...

SQL> select empno, ename, job, sal from bigemp where ename like 'JON%';

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.01

Der Ausführungsplan zeigt, dass der Index genutzt wurde ...

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2677556082

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    22 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGEMP          |     1 |    22 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BIGEMP_IX_ENAME |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME" LIKE 'JON%')
       filter("ENAME" LIKE 'JON%')

Allerdings findet diese Abfrage nur die Zeilen, in denen der ENAME tatsächlich mit dem Suchstring beginnt. Sucht man dagegen nach einem Teilstring am Ende des ENAME, findet diese LIKE-Klausel nichts mehr - dann muss man den Platzhalter an den Anfang stellen.

SQL> select empno, ename, job, sal from bigemp where ename like '%NES';

Ausführungsplan
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   340K|  7321K| 11070   (2)| 00:02:13 |
|*  1 |  TABLE ACCESS FULL| BIGEMP |   340K|  7321K| 11070   (2)| 00:02:13 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" LIKE '%ONES' AND "ENAME" IS NOT NULL)

Nun findet ein Full Table Scan statt, denn der Index kann logischerweise nicht genutzt werden - schließlich ist der Anfangsbuchstabe der gesuchten ENAME gar nicht bekannt, es gibt also keine Einstiegspunkte in den Index. Der Datenbank bleibt also nichts übrig, als alle Zeilen durchzuarbeiten.

Aber auch diese Abfrage lässt sich in der Tat durch einen Index unterstützen - ein solcher müsste die Spalteninhalte lediglich "rückwärts" enthalten. Zuerst brauchen wir also eine Funktion, die eine Zeichenkette "umdreht" - wir nennen sie STRING_REVERSE.

create or replace function string_reverse (
  p_string in varchar2
) return varchar2 deterministic is
  v_revstring varchar2(32767);
begin
  for i in reverse 1..length(p_string) loop
    v_revstring := v_revstring || substr(p_string, i, 1);
  end loop;
  return v_revstring;
end;
/

Für die Experten: Es gibt sogar eine eingebaute Funktion namens REVERSE - die macht genau das gleiche, ist aber nicht dokumentiert. Wer also keine Angst vor undokumentierten Funktionen hat, kann auch diese nutzen. Als nächstes gilt es, den Index aufzubauen.

SQL> create index BIGEMP_IX_ENAME_REV on bigemp (string_reverse(ename))
  2  /

Nun kann man den Index nutzen. Allerdings muss die LIKE-Abfrage dazu etwas anders formuliert werden ...

SQL> select empno, ename, job, sal from bigemp where string_reverse(ename) like string_reverse('%NES');


     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.07

Wiederum ging das recht flott. Der Ausführungsplan zeigt, dass der Index auch tatsächlich genutzt wurde.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1402227292

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |   340K|  9651K|  4839   (1)| 00:00:59 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGEMP              |   340K|  9651K|  4839   (1)| 00:00:59 |
|*  2 |   INDEX RANGE SCAN          | BIGEMP_IX_ENAME_REV | 61342 |       |   149   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(STRING_REVERSE("ENAME") LIKE 'SEN%')
       filter(STRING_REVERSE("ENAME") LIKE 'SEN%')

Beides kann nun kombiniert werden ...

SQL> select empno, ename, job, sal from bigemp 
  2  where ename like 'JON%' or string_reverse(ename) like string_reverse('%NES');

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.09

... was zur Nutzung beider Indizes führt ...

Ausführungsplan

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   340K|  9651K|  4844   (1)| 00:00:59 |
|   1 |  CONCATENATION               |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIGEMP              |     1 |    29 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | BIGEMP_IX_ENAME     |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| BIGEMP              |   340K|  9651K|  4840   (1)| 00:00:59 |
|*  5 |    INDEX RANGE SCAN          | BIGEMP_IX_ENAME_REV | 61342 |       |   149   (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------

Eine Teilstringsuche "in der Mitte", also ein LIKE '%ONE%' führt aber in jedem Fall zu einem Full Table Scan. Denn da die Wildcard nun auf beiden Seiten steht, findet die Datenbank für beide Indizes keine Einstiegspunkte - sie muss die Tabelle also wieder komplett durcharbeiten.

SQL> select empno, ename, job, sal from bigemp 
  2  where ename like '%ONE%' or string_reverse(ename) like string_reverse('%ONE%');

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:13.76

Ausführungsplan

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   341K|  9674K| 11169   (2)| 00:02:15 |
|*  1 |  TABLE ACCESS FULL| BIGEMP |   341K|  9674K| 11169   (2)| 00:02:15 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" LIKE '%ONE%' AND "ENAME" IS NOT NULL AND "ENAME"
              IS NOT NULL OR STRING_REVERSE("ENAME") IS NOT NULL AND STRING_REVERSE("ENAME") IS NOT
              NULL AND STRING_REVERSE("ENAME") LIKE '%ENO%')

Was bedeutet das nun für die APEX-Anwendung?

Zunächst sollte man den Endanwender die Wildcards selbst eingeben lassen. Wenn man dabei möchte, dass der Endanwender statt des Prozentzeichens einen Stern nutzen kann, so ist das mit TRANSLATE völlig unproblematisch. Der Nutzer hat es dann selbst in der Hand: Setzt er die Wildcard nur vorne oder nur hinten, so ist die Abfrage (dank Indexnutzung) sehr schnell. Verwendet er Wildcards vorne und hinten, so ist die Suche "mächtiger", kostet aber auch mehr Zeit.

Die Abfrage des "klassischen" APEX Berichts kann nun also entsprechend geändert werden. Dazu sind folgende Aktionen nötig:

• Es braucht einen "normalen" Index auf die Spalten ENAME und JOB
• Es braucht einen "reverse" Index auf die Spalten ENAME und JOB

create index IX_EMP_ENAME on EMP (ENAME);
create index IX_EMP_JOB on EMP (JOB); 
create index IX_EMP_ENAME_REV on EMP (string_reverse(ENAME));
create index IX_EMP_JOB_REV on EMP (string_reverse(JOB));

Schließlich stellen wir die SQL-Abfrage entsprechend um:

select * from (
  select * from emp
)
where (
  :P2_REPORT_SEARCH is null or 
  ENAME like translate(:P2_REPORT_SEARCH, '*', '%') or
  string_reverse(ENAME) like string_reverse(translate(:P2_REPORT_SEARCH, '*', '%'))
  ) or (  
  :P2_REPORT_SEARCH is null or 
  JOB like translate(:P2_REPORT_SEARCH, '*', '%') or
  string_reverse(JOB) like string_reverse(translate(:P2_REPORT_SEARCH, '*', '%'))
  )

Der APEX-Bericht funktioniert nun wie vorher, allerdings muss nun die Wildcard eingegeben werden. Wird diese nur vorne oder nur hinten angegeben, so arbeitet die Suche auch bei sehr großen Tabellen performant.

Abbildung 2: APEX-Bericht mit neuer SQL-Abfrage: Nun muss die Wildcard aktiv verwendet werden

Allerdings hat der Bericht noch einen Schönheitsfehler. Wenn man den Suchbegriff in Kleinbuchstaben eingibt, wird nichts gefunden. Und wo wir gerade dabei sind: Gerade bei Daten in europäischen Sprachen wäre es gut, wenn man Akzent-Insensitiv suchen könnte - man sucht nach Muller - und findet auch Müller. Hierfür gibt es in der Datenbank mittlerweile eigene Session-Parameter - mit einem ALTER SESSION kann das Suchverhalten also umgestellt werden. Allerdings ist es in APEX oft problematisch, Session-Parameter umzustellen, daher sei hier ein anderer Weg gezeigt.

Die in die Datenbank eingebaute Funktion NLSSORT "normalisiert" eine Zeichenkette - das heißt, die Zeichen werden in ihre Grundformen im "Lower Case" umgewandelt. Dazu ein Beispiel

SQL> select nlssort('München ist schön und im Sommer heiß','nls_sort=binary_ai') from dual;

NLSSORT('MÜNCHENISTSCHÖNUNDIMSOMMERHEIß','NLS_SORT=BINARY_AI')
----------------------------------------------------------------------------
6D756E6368656E20697374207363686F6E20756E6420696D20736F6D6D657220686569737300

Die Rückgabe ist ein RAW ... wandelt man das wiederum explizit in ein VARCHAR2 um, sieht das Ergebnis so aus.

SQL> select utl_raw.cast_to_varchar2(
  2    nlssort('München ist schön und im Sommer heiß','nls_sort=binary_ai')
  3  ) from dual;

UTL_RAW.CAST_TO_VARCHAR2(NLSSORT('MÜNCHENISTSCHÖNUNDIMSOMMERHEIß','NLS_SORT=BINA
--------------------------------------------------------------------------------
munchen ist schon und im sommer heiss

Und analog zur Vorgehensweise mit STRING_REVERSE kann man nun auch Indizes hierfür aufbauen. Damit die SQL-Abfrage leserlich bleibt, brauchen wir zunächst zwei Funktionen: PREP_STRING und PREP_STRING_REV.

create or replace function prep_string(
  p_string in varchar2
) return varchar2 deterministic is
begin
  return utl_raw.cast_to_varchar2(nlssort(p_string, 'nls_sort=binary_ai'));
end prep_string;
/

create or replace function prep_string_rev(
  p_string in varchar2
) return varchar2 deterministic is
begin
  return utl_raw.cast_to_varchar2(nlssort(string_reverse(p_string), 'nls_sort=binary_ai'));
end prep_string_rev;
/

Als nächstes bauen wir die Indizes auf die Spalten ENAME und JOB neu.

drop index IX_EMP_ENAME ;
drop index IX_EMP_JOB ;
drop index IX_EMP_ENAME_REV;
drop index IX_EMP_JOB_REV;

create index IX_EMP_ENAME     on EMP (prep_string(ENAME));
create index IX_EMP_JOB       on EMP (prep_string(JOB)); 
create index IX_EMP_ENAME_REV on EMP (prep_string_rev(ENAME));
create index IX_EMP_JOB_REV   on EMP (prep_string_rev(JOB));

Zum Schluß tauschen wir wiederum die SQL-Abfrage aus.

select * from (
  select * from emp
)
where (
  :P2_REPORT_SEARCH is null or 
  prep_string(ENAME)     like prep_string(translate(:P2_REPORT_SEARCH, '*', '%')) or
  prep_string_rev(ENAME) like prep_string_rev(translate(:P2_REPORT_SEARCH, '*', '%'))
) or (  
  :P2_REPORT_SEARCH is null or 
  prep_string(JOB)       like prep_string(translate(:P2_REPORT_SEARCH, '*', '%')) or
  prep_string_rev(JOB)   like prep_string_rev(translate(:P2_REPORT_SEARCH, '*', '%'))
)

Die Suche im klassischen APEX-Bericht hat sich nun, gegenüber der APEX-Standardimplementierung, ein gutes Stück verbessert.

Abbildung 3: Nun kann auch Case- und Umlaut-Insensitiv gesucht werden

• Es kann nun auch Umlaut-Insensitiv gesucht werden
• Der Nutzer kann entscheiden, ob er mit oder ohne Wildcard suchen möchte
• Wird die Wildcard nur vorne oder nur hinten verwendet, so ist die Suche auch auf großen Datenmengen sehr performant, da Indizes genutzt werden können.

Die Möglichkeiten von "Standard SQL" sind damit erschöpft, nicht aber die Möglichkeiten der Oracle-Datenbank. Mit Hilfe der Volltextengine Oracle TEXT können Sie die Suche um "Volltextfeatures" wie linguistische Suche, Ähnlichkeitssuche und "echte" Teilstring-Suche, also "%SUCHE%" erweitern. Mehr dazu finden Sie in den Tipps der APEX-Community zum Thema oder im deutschsprachigen Oracle TEXT Blog.

• Volltextrecherche mit Application Express: Erste Schritte
• Volltextrecherche mit Application Express: Wartung und Pflege des Index
• Volltextsuche auf "normalen" Tabellendaten mit Oracle TEXT und USER_DATASTORE auf oracle-text-de.blogspot.com

Zurück zur Community-Seite