Dość powszechnym wyzwaniem dla programistów jest umożliwienie użytkownikom bazy danych efektywnego wyszukiwania informacji w oparciu o częściowe dopasowanie danych tekstowych, np. szukanie produktów zawierających określone słowo w opisie, szukanie symboli produktów zawierających określony fragment, szukanie dokumentów tekstowych zawierających podaną frazę, itp. Zwykle zadania takie są realizowane za pomocą operatora LIKE '%...%' lub UPPER(...) LIKE '%...%'. Niestety, tradycyjne struktury indeksów (B*-drzewo i bitmapowy) są w przypadku takich zapytań bezsilne, co doprowadza do planów wykonania bazujących na nieefektywnych pełnych odczytach tabeli.
W celu znalezienia skuteczniejszego rozwiązania przyjrzyjmy się bliżej własnościom ciekawego specjalizowanego typu indeksu służącego do wsparcia takich właśnie częściowych dopasowań danych tekstowych – indeksowi Oracle Text. A dokładniej mówiąc, jednemu z trzech indeksów Oracle Text, nazwanego standardowym (CONTEXT). Dwa pozostałe, mniej popularne typy indeksów Oracle Text, noszą nazwy: katalogowy (CTXCAT) i klasyfikacyjny (CTXRULE).
Tworzenie indeksu CONTEXT odbywa się za pomocą polecenia CREATE INDEX wyposażonego w klauzulę INDEXTYPE:
CREATE INDEX poezje_ind ON poezje(tresc) INDEXTYPE IS CTXSYS.CONTEXT;
Struktura indeksu typu CONTEXT jest fizycznie zapisana w formie czterech tabel: tabeli $I zawierającej pojedyncze wyekstrahowane słowa , tabeli (IOT) $K odwzorowującej identyfikatory dokumentów tekstowych na ROWID, tabeli $R o strukturze odwrotnej do $K oraz z tabeli $N zawierającej usunięte identyfikatory dokumentów. Ponadto, tabelom $I i $R towarzyszą indeksy. Użycie indeksu jest transparentne dla użytkownika, wymaga jednak zastąpienia operatora LIKE specjalizowaną funkcją CONTAINS (niewrażliwa na wielkość znaków!), np.:
SELECT * FROM poezje WHERE CONTAINS(tresc, 'zdrowie')>0;
Należy zaznaczyć, że w przeciwieństwie do klasycznych indeksów, indeksy Oracle Text domyślnie nie są automatycznie synchronizowane po operacjach DML. Do administratora bazy danych należy wykonywanie takiej synchronizacji za pomocą procedury CTX_DDL.SYNC_INDEX(). Alternatywnie można wymusić automatyczne synchronizowanie indeksu w chwili zatwierdzenia transakcji (PARAMETERS ('SYNC (ON COMMIT)')). Trzeba o tym pamiętać, bo w przeciwnym razie wyniki zapytań będą niekompletne!
Zademonstrujmy zastosowanie indeksu typu CONTEXT w optymalizacji zapytań dokonujących częściowego dopasowania danych tekstowych. Utwórzmy: prostą tabelę POEZJE z kolumną tekstową oraz indeks typu CONTEXT (bez automatycznej synchronizacji). Przed utworzeniem indeksu do tabeli POEZJE wstawimy jeden rekord.
CREATE TABLE poezje (p_id NUMBER(10) PRIMARY KEY, tresc VARCHAR2(1000));
INSERT INTO poezje VALUES (1, 'Litwo, Ojczyzno moja! ty jestes jak zdrowie');
CREATE INDEX poezje_ind ON poezje(tresc) INDEXTYPE IS CTXSYS.CONTEXT;
W celach „poznawczych” rzućmy okiem na zawartość wygenerowanych tabel $I, $K, $R.
SELECT * FROM DR$POEZJE_IND$I;
TOKEN_TEXT TOKEN_TYPE TOKEN_FIRST TOKEN_LAST TOKEN_COUNT TOKEN_INFO
---------------- ---------- ----------- ---------- ----------- ----------
JAK 0 1 1 1 008806
JESTES 0 1 1 1 008805
LITWO 0 1 1 1 008801
MOJA 0 1 1 1 008803
OJCZYZNO 0 1 1 1 008802
TY 0 1 1 1 008804
ZDROWIE 0 1 1 1 008807
SELECT * FROM DR$POEZJE_IND$I;
DOCID TEXTKEY
----- ------------------
1 AAASM9AADAAAP1GAAA
SELECT * FROM DR$POEZJE_IND$R;
ROW_NO DATA
------ ----------------------------
0 00001233D0000C00003F51804141
Zastosujmy teraz funkcję CONTAINS w celu wyszukania rekordów zawierających podane słowo lub fragment słowa.
SELECT * FROM poezje WHERE CONTAINS(tresc, 'zdrowie')>0;
P_ID TRESC
---- --------------------------------------------
1 Litwo, Ojczyzno moja! ty jestes jak zdrowie
SELECT * FROM poezje WHERE CONTAINS(tresc, '%CZY%')>0;
P_ID TRESC
---- -------------------------------------------
1 Litwo, Ojczyzno moja! ty jestes jak zdrowie
Pamiętajmy, że domyślnie indeks Oracle Text nie jest automatycznie synchronizowany. Zatem jeśli dodamy nowy rekord do tabeli POEZJE, nie zostanie on uwzględniony w indeksie i w wynikach zapytania!
INSERT INTO poezje VALUES (2, 'Wplynalem na suchego przestwor oceanu');
COMMIT;
SELECT * FROM poezje WHERE CONTAINS(tresc, 'OCEANU')>0;
no rows selected
EXEC CTX_DDL.SYNC_INDEX('poezje_ind');
PL/SQL procedure successfully completed.
SELECT * FROM poezje WHERE CONTAINS(tresc, 'OCEANU')>0;
P_ID TRESC
---- -------------------------------------
2 Wplynalem na suchego przestwor oceanu
Aby eksperymentalnie wykazać użyteczność indeksów Oracle Text wypełniliśmy testową tabelę POEZJE milionem rekordów, gdzie w każdym rekordzie znajdowało się losowo wygenerowane zdanie składające się z od 5 do 20 losowo wygenerowanych słów pochodzących ze zbioru 500 słów (kod PL/SQL generujący testową tabelę można pobrać TUTAJ). Następnie badaliśmy czas odpowiedzi na zapytania przeszukujące tabelę POEZJE w celu znalezienia rekordów zawierających podane słowo lub podany fragment słowa, zarówno korzystając z indeksu Oracle Text (funkcja CONTAINS()), jak i bez niego (operator LIKE).
SQL> CREATE INDEX poezje_ind ON poezje(tresc) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.
Elapsed: 00:00:33.34
SQL> SELECT * FROM poezje WHERE CONTAINS(tresc, 'zdrowie')>0;
P_ID TRESC
---- -------------------------------------------
0 Litwo, Ojczyzno moja! ty jestes jak zdrowie
Elapsed: 00:00:00.01
SQL> SELECT * FROM poezje WHERE CONTAINS(tresc, '%drowi%')>0;
P_ID TRESC
---- -------------------------------------------
0 Litwo, Ojczyzno moja! ty jestes jak zdrowie
Elapsed: 00:00:00.04
SQL> SELECT * FROM poezje WHERE UPPER(tresc) LIKE '%ZDROWIE%';
P_ID TRESC
---- -------------------------------------------
0 Litwo, Ojczyzno moja! ty jestes jak zdrowie
Elapsed: 00:00:01.15
Jak widać, indeks Oracle Text doskonale przyspiesza wyszukiwanie dokumentów tekstowych według pełnych słów. W przypadku wyszukiwania częściowych dopasowań, wydajność będzie zależna od całkowitej liczby wszystkich wyrazów języka (w naszych dokumentach stosowaliśmy 500 słów). Warto też przyjrzeć się rozmiarowi indeksu Oracle Text w porównaniu z rozmiarem tabeli z danymi.
SQL> SELECT 'ORACLE TEXT INDEX', SUM(bytes)
FROM user_extents
WHERE segment_name LIKE 'DR$POEZJE_IND%'
UNION ALL
SELECT 'TABELA POEZJE', SUM(bytes)
FROM user_extents
WHERE segment_name='POEZJE';
'ORACLETEXTINDEX' SUM(BYTES)
----------------- ----------
ORACLE TEXT INDEX 48758784
TABELA POEZJE 117440512
Godna polecenia dokumentacja opisująca indeksy Oracle Text znajduje się tu (ToadWorld) i tu (Oracle-Base).