Przeszukiwanie danych pełnotekstowych – indeks Oracle Text typu CTXSYS.CONTEXT

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).

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *