Oracle: LIKE Abfrage optimieren

[wortfuchs]

Möglicherweise war das andere Brett falsch, hier noch einmal das Posting:

Die Frage betrifft Oracle8i/9i

In einer Applikation wird folgende Abfrage verwendet:

SELECT * FROM sample
WHERE UPPER(‚Zeichenkette‘) LIKE sample.spalte

Spalte beinhaltet Vergleichswerte mit %-Patterns, z.b.: „%“, „%text%“ oder „%beispiel“. Durch die %-wildcards am Anfang der Spalte kann Oracle keinen Index für die Suche verwenden und führt einen Fulltablescan aus. Bei 1000 und mehr Datensatzen in der Tabelle sample werden die Antwortzeiten deutlich länger.

Meiner Überlegungen gehen nun dahin, das % nach Möglihckeit weg zu optimieren. Dazu möchte ich die Zeichenketten aufteilen. „%text%“ wird auf zwei Spalten aufgeteilt, jeweils „et%“ (erste hälfte, rückwärts) und „xt%“ (zweite hälfte). Wie die Aufteilung im Detail funktioniert, soll an dieser Stelle nicht weiter interessieren. Als Ergebnis erhalte ich aber zwei Spalten die entweder ein „%“ oder „%“ erhalten. Ein „%“ wird es aber nicht mehr geben.

Die neue Datenbankabfrage sieht dann wie folgt aus:

SELECT * FROM sample
WHERE UPPER(’

[Erwin_Rockenbauer]

SELECT * FROM sample
WHERE UPPER(‚Zeichenkette‘) LIKE sample.spalte

Spalte beinhaltet Vergleichswerte mit %-Patterns, z.b.: „%“,
„%text%“ oder „%beispiel“. Durch die %-wildcards am Anfang der
Spalte kann Oracle keinen Index für die Suche verwenden und
führt einen Fulltablescan aus. Bei 1000 und mehr Datensatzen
in der Tabelle sample werden die Antwortzeiten deutlich
länger.

ich vermute mal, dass aufgrund deiner speziellen where-clausel: „zeichenkette like spalte“ ein index ohnehin nicht verwendet werden kann. meines wissens nach klappt das nur bei den „klassischen“ where-clauseln: „spalte like zeichenkette“.

grundsätzlich kennt oracle ja sogenannte fuction-based-indexes. du kannst also einen index so anlegen:

create index tab_i
on tab(upper(spalte)) ;

suchst du nun nach
select *
from tab
wehre upper(spalte) = ‚WERT‘ ;

dann kann oracle diesen index nutzen (was normalerweise ja nicht klappen würde).

analog müsste also auch so ein index funktionieren:
create index tab_i
on tab(substr(spalte,3)) ;

solche indizes sind zwar etwas langsamer als „normale“ indizes, aber immer noch schneller als ein full-table-scan.

trotzdem vermute ich, dass oracle in deinem fall den index nicht nutzt.

erwin

[wortfuchs]

SELECT * FROM sample
WHERE UPPER(‚Zeichenkette‘) LIKE sample.spalte

Spalte beinhaltet Vergleichswerte mit %-Patterns, z.b.: „%“,
„%text%“ oder „%beispiel“. Durch die %-wildcards am Anfang der
Spalte kann Oracle keinen Index für die Suche verwenden und
führt einen Fulltablescan aus. Bei 1000 und mehr Datensatzen
in der Tabelle sample werden die Antwortzeiten deutlich
länger.

ich vermute mal, dass aufgrund deiner speziellen
where-clausel: „zeichenkette like spalte“ ein index ohnehin
nicht verwendet werden kann. meines wissens nach klappt das
nur bei den „klassischen“ where-clauseln: „spalte like
zeichenkette“.

Im Gegensatz zu einem „… LIKE ‚%…‘“, wo durch den wildcards am Anfang der Zeichenkette prinzipiell jede Spalte in Frage kommt, könnte Oracle beim „… LIKE spalte“ jene Spalten filtern, die entweder mit dem entsprechenden Buchstaben/Präfix beginnen oder am Anfang ein %-Zeichen haben.

Beispiel: Spalte=„abcd“ „bcde“ „a%“ „%“
Beim Vergleich mit einer Zeichenkette „abcd“ könnte Oracle einen Index auf der Spalte verwenden und >= (GE) nach „abcd“ suchen. „bcde“ würde durch den Index ausgeschlossen. Umgekehrt kann bei einer Zeichenkette „xyz“ alles ausgeschlossen werden. In jedem Fall müsste nur beachtet werden, dass „%“ immer in Frage kommt.

Ich habe nur keine Ahnung, wie ich in Erfahrung bringen kann, ob Oracle den Index nutzt. Ich kenne mich mit den Analyse-Optionen zu wenig aus.

grundsätzlich kennt oracle ja sogenannte
fuction-based-indexes. du kannst also einen index so anlegen:

create index tab_i
on tab(upper(spalte)) ;

suchst du nun nach
select *
from tab
wehre upper(spalte) = ‚WERT‘ ;

dann kann oracle diesen index nutzen (was normalerweise ja
nicht klappen würde).

analog müsste also auch so ein index funktionieren:
create index tab_i
on tab(substr(spalte,3)) ;

solche indizes sind zwar etwas langsamer als „normale“
indizes, aber immer noch schneller als ein full-table-scan.

trotzdem vermute ich, dass oracle in deinem fall den index
nicht nutzt.

Leider nicht. Praktisch geht es an dieser Stelle um URLs, d.h. der Wert links von LIKE ist immer eine vollständige URL, wie man sie auch im Webbrowser sieht. Auf der rechten Seiten befinden sich Patterns, die einen URL-Match ausmachen.

links:
http://www.wer-weiss-was.de/cgi-bin/forum

rechts eine Reihe von Patterns:
%wer-weiss-was.de/cgi-bin/%
www.wer-weiss-was.de/%
%.de/secrets/%

Gesucht werden alle matches. Ein function based Index hilft meiner Meinung da nicht weiter …

Markus

[Peter_Goppelt_Langer_d5bddf]

Hallo,

du kannst Dir mit analyze table compute statistics for table den Ausführungsplan ansehen. Das Ergebnis steht in der PLAN_TABLE. Schöner gehts noch in der Management Konsole.

Performance:
Du wirst kaum darum rum kommen, dass die Tabelle komplett gescannt wird. Was Du auf jeden Fall tun kannst ist, dass du sie im Buffer Pool hältst. Oracle hat dafür einen extra KEEP Pool.

Wenn es sich nur um feste Elemente handelt, nach denen Du suchst, kannst Du eine 1:n Relation aufbauen und beim Insert nach allen Stellen parsen und die Verweise dort eintragen. Dann kannst Du in einer Indizierten Tabelle ohne LIKE selektieren.

Gruß

Peter

[wortfuchs]

Hallo,

du kannst Dir mit analyze table compute
statistics for table den Ausführungsplan ansehen. Das Ergebnis
steht in der PLAN_TABLE. Schöner gehts noch in der Management
Konsole.

Danke für den Tipp.

Performance:
Du wirst kaum darum rum kommen, dass die Tabelle komplett
gescannt wird. Was Du auf jeden Fall tun kannst ist, dass du
sie im Buffer Pool hältst. Oracle hat dafür einen extra KEEP
Pool.

Meinst du damit, dass sie im Speicher bleibt? Das ist schon der Fall, Plattenzugriffe finden praktisch nicht statt. Leider steigt die Ausführungszeit mit wachsender Tabellengröße, hier sehe ich den Flaschenhals. Aktuell versuche ich einen Index zu finden, der die Ergebnismenge im Vorfeld einschränkt. Anhand des Patterns sehe ich allerdings nicht viele Möglichkeiten …

Wenn es sich nur um feste Elemente handelt, nach denen Du
suchst, kannst Du eine 1:n Relation aufbauen und beim Insert
nach allen Stellen parsen und die Verweise dort eintragen.
Dann kannst Du in einer Indizierten Tabelle ohne LIKE
selektieren.

Mir ist nicht ganz klar,wie du das meinst, kannst du ein kleines Beispiel nennen?

Danke,
Markus

[Peter_Goppelt_Langer_d5bddf]

Wenn es sich nur um feste Elemente handelt, nach denen Du
suchst, kannst Du eine 1:n Relation aufbauen und beim Insert
nach allen Stellen parsen und die Verweise dort eintragen.
Dann kannst Du in einer Indizierten Tabelle ohne LIKE
selektieren.

Mir ist nicht ganz klar,wie du das meinst, kannst du ein
kleines Beispiel nennen?

Hallo Markus,

zuerst brauchst du eine Hilfstabelle, die den Schlüssel deiner eigentlichen tabelle um den suchtext erweitert.
Bei einem Neueintrag in deine Tabelle überprüfst Du einmal, ob die Suchtexte darin vorkommen und schreibst sie gegebenenfalls in die Hilfstabelle ein. Das kannst Du alles mit einem Trigger machen. Beim Suchen suchst Du statt im Text in der Hilfstabelle - aber ohne LIKE und deshalb über den Index, den Du auf das Suchtext-Feld angelegt hast.

Das bringt immer dann etwas, wenn inserts seltener sind als selects.

Gruß

Peter