ORACLE - indeksy na wyszukiwanie pełnotekstowe

[Marek Horodyski]

Jest sobie tabelka z polem varchar2, którą będę musiał przeszukiwać na
występowanie podciąg ow. Coś w deseń :

select * from tabelka where pole like '%AlaMaKota%'

Pole względnie długie, rekordów od groma. Baza z niewielkim ruchem, ale
jednak dziennie jakieś 0,125% rekordów może się zmieniać.
Czy prosty indeks po polu wystarczy, czy może baza wspiera to w jakiś inny
specyficzny sposób (baza może być 9, 10, 11) ?
Przeglądam troche wygooglowane pomysły, ale rada doświadczonych użytkowników
zapewne będzie cenniejsza.

Pozdrawiam,
Marek Horodyski

[Sławomir Szyszło]

Dnia Sat, 22 Oct 2011 01:01:27 +0200, "Marek Horodyski"
<marek.h...@interia.pl> wklepał(-a):

Zwykły indeks tego nie obsłuży. Może pomóc indeks tekstowy:
http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php

Potrzebny jest do tego moduł Oracle Text. Sprawdź w DBA_REGISTRY czy masz to
zainstalowane.

Jeśli nie, to z SYS-a:
@?/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

Gdzie CTXSYS to docelowy schemat, SYSAUX (lub DRSYS w 9i) - tablespace dla
tabel, TEMP - tymczasowy tablespace dla użytkownika, NOLOCK - nie blokuje
użytkownika po instalacji.
--
Sławomir Szyszło
Primus inter FAQires & Grand Inquisitor no.0 of pl.comp.bazy-danych
FAQ pl.comp.bazy-danych http://www.dbf.pl/faq/
Archiwum http://groups.google.com/groups?group=pl.comp.bazy-danych

[Marek Horodyski]

Użytkownik "Sławomir Szyszło" <slas...@CIACHTOlist.pl> napisał w wiadomości
news:j812m...@slaszyszCIACHTO.list.pl...

> Dnia Sat, 22 Oct 2011 01:01:27 +0200, "Marek Horodyski"
> <marek.h...@interia.pl> wklepał(-a):
>

[...]

> Potrzebny jest do tego moduł Oracle Text. Sprawdź w DBA_REGISTRY czy masz
> to
> zainstalowane.
>
> Jeśli nie, to z SYS-a:
> @?/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK
>
> Gdzie CTXSYS to docelowy schemat, SYSAUX (lub DRSYS w 9i) - tablespace dla
> tabel, TEMP - tymczasowy tablespace dla użytkownika, NOLOCK - nie blokuje
> użytkownika po instalacji.

Ok - dzięki, dzisiaj to pomacam.

Pozdrawiam,
Marek Horodyski

[Marek Horodyski]

Użytkownik "Sławomir Szyszło" <slas...@CIACHTOlist.pl> napisał w wiadomości
news:j812m...@slaszyszCIACHTO.list.pl...

> Dnia Sat, 22 Oct 2011 01:01:27 +0200, "Marek Horodyski"
> <marek.h...@interia.pl> wklepał(-a):
>

[...]

> Zwykły indeks tego nie obsłuży. Może pomóc indeks tekstowy:
> http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php

Z tych propozycji nadaje się CONTEXT index. W przykładzie jest na blobie,
ale działa varchar2. Robiłem testy na liku bez indeksów.
Po założeniu indeksu wydajność like wyraźnie spadła. Ale zapytania na
contains działają kilka razy szybciej. Przy bazie rzędu kilku milionów
rekordów to ma znaczenie. Contains działa inaczej niż like. Przy warunku :

WHERE CONTAINS( nazwisko, 'KOWAL') > 0

dostanę wszystkie rekordy gdzie występuje KOWAL jako całe słowo (np.
KOWAL-DŁUŻEC), ale KOWALSKI już nie. Być może to jest lepsze od '%KOWAL%',
ale to już użytkownicy będą oceniać. Nie bardzo wiem co daje Score(1) w
przykładach. Nie wiem też co można uzyskać trzecim parametrem w CONTAINS.

Przyglądałem się też CTXCAT indeksom, ale nie bardzo widzę na przykładzie
zastosowanie. Pomijając to, że jeszcze nie mam
GRANT EXECUTE ON CTX_DDL
(może jutro mi dadzą, a może spławią), to nie wiem jak by tam można bez
sztuczek dołożyć 2gi warunek na polu varchar2.
Z ampersandami widzę problemy, ale na razie przez te granty nie mogłem
testować.

Przy okazji - jak w poleceniu INSERT wstawić do pola vachar2 ampersand ?
Przyładu nie znalazłem, w faq nie widziałem.
Różne próby po jakichś "\" też mi nie zadziałały. To jakieś podstawy
pewnie, i taka zagwozdka.

Przykład z CTXRULE wygląda na inne przeznaczenie. Szukam prostego
wyszukiwania, głównie po polach testowych.
Przeważnie jedno lub dwa, rzadziej trzy. Że też nie ma czegoś takiego :

CREATE INDEX my_table_idx ON my_table( field1[, fieldN]) INDEXTYPE IS
GOOGLE; :)

Pozdrawiam,
Marek Horodyski

[Sławomir Szyszło]

Dnia Mon, 24 Oct 2011 21:25:45 +0200, "Marek Horodyski"
<marek.h...@interia.pl> wklepał(-a):

>Z tych propozycji nadaje się CONTEXT index. W przykładzie jest na blobie,
>ale działa varchar2. Robiłem testy na liku bez indeksów.
>Po założeniu indeksu wydajność like wyraźnie spadła. Ale zapytania na
>contains działają kilka razy szybciej. Przy bazie rzędu kilku milionów
>rekordów to ma znaczenie. Contains działa inaczej niż like. Przy warunku :
>
>WHERE CONTAINS( nazwisko, 'KOWAL') > 0
>
>dostanę wszystkie rekordy gdzie występuje KOWAL jako całe słowo (np.
>KOWAL-DŁUŻEC), ale KOWALSKI już nie. Być może to jest lepsze od '%KOWAL%',
>ale to już użytkownicy będą oceniać. Nie bardzo wiem co daje Score(1) w
>przykładach. Nie wiem też co można uzyskać trzecim parametrem w CONTAINS.

Racja, zapomniałem napisać, że trzeba używać CONTAINS a nie LIKE.
A próbowałeś CONTAINS (nazwisko, 'KOWAL%') > )?
Nie mam teraz pod ręką bazy z takim indeksem, to i nie mogę sprawdzić.

>Przy okazji - jak w poleceniu INSERT wstawić do pola vachar2 ampersand ?
>Przyładu nie znalazłem, w faq nie widziałem.
>Różne próby po jakichś "\" też mi nie zadziałały. To jakieś podstawy
>pewnie, i taka zagwozdka.

W SQL*PLus:
SET DEFINE OFF;

albo:

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

Albo: CHR(38)

http://orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F

[Marek Horodyski]

Użytkownik "Sławomir Szyszło" <slas...@CIACHTOlist.pl> napisał w wiadomości

news:j84nj...@slaszyszCIACHTO.list.pl...

> Dnia Mon, 24 Oct 2011 21:25:45 +0200, "Marek Horodyski"
> <marek.h...@interia.pl> wklepał(-a):

[...]

> A próbowałeś CONTAINS (nazwisko, 'KOWAL%') > )?

Dzisiaj spróbuję.

[...]

> W SQL*PLus:
> SET DEFINE OFF;
>
> albo:
>
> SET ESCAPE '\'
> SELECT '\&abc' FROM dual;
>
> Albo: CHR(38)
>
> http://orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F

Używam aplkacji Harbourowej, i otrzymanych w select wartości budowałem
prosto insert do drugiej bazy. No i zaskoczony zaważyłem, że użytkownicy
używają ampersanda :).
Poczytam to wiki, napewno można, skoro w schematach źródłowych były. W
Harbour jest zaimplementowana OCI, ale w dość ograniczonym zakresie. Nie ma
np bindowania, co od razu by rozwiązało problem.

Pozdrawiam,
Marek Horodyski

[homar]

On 24 Paź, 22:07, Sławomir Szyszło <slasz...@CIACHTOlist.pl> wrote:
> Dnia Mon, 24 Oct 2011 21:25:45 +0200, "Marek Horodyski"

> <marek.horody...@interia.pl> wklepał(-a):

[...]

> Racja, zapomniałem napisać, że trzeba używać CONTAINS a nie LIKE.
> A próbowałeś CONTAINS (nazwisko, 'KOWAL%') > )?
> Nie mam teraz pod ręką bazy z takim indeksem, to i nie mogę sprawdzić.

Po raz pierwszy używam groups.google do newsów - fajne :)
Powyższe 'KOWAL%' działa. Działa też znak podkreślenia jako jako
zastępnik dowolnego znaku. A tak w ogóle, to ten indeks przy
odpowiednim potraktowaniu ma znacznie więcej możliwości. Np można mieć
pole jako kontener zbierający wiele danych [jednak index kontekstowy
nie złoży wielu pól, np (poleA, poleB) czy (poleA||poleB) nie
przejdzie], i przeszukać go np. na warunek :

where contains( myWorek, 'zawisza & czarny & grab%')

co zwróci wszystkie rekordy gdzie w myWorek występują w dowolnej
kolejności słowa jako całość : GRABOWA (lub inna końcówka), ZAWISZA
oraz CZARNY.
Można tam w środku operować innymi operatorami z różnymi ciekawymi
efektami - co już łatwo wygooglać. Zapytanie trzeba wstawić w jakiegoś
try/catcha bo contains potrafi się przy niewłaściwym wzorcu wywalić.
Wydajność rewelacyjna. Na tyle zadowalająca, że nie próbowałem CTXCAT.
Index natomiast chyba nie jest odświeżany przy update - muszę to
jeszcze sprawdzić, gdyż nie zadziałał mi test na wielkość liter.
Generalnie - rewelacja. I pod względem wydajności i efektów
pozwalających na wybrnięcie z sytuacji gdzie dane wprowadzane nie
weryfikowano z jakimiś słowikami.
Dzięki za cenne wskazówki.

Pozdrawiam,
Marek Horodyski

[Sławomir Szyszło]

Dnia Tue, 25 Oct 2011 11:25:01 -0700 (PDT), homar <marek.h...@interia.pl>
wklepał(-a):

>Index natomiast chyba nie jest odświeżany przy update - muszę to
>jeszcze sprawdzić, gdyż nie zadziałał mi test na wielkość liter.
>Generalnie - rewelacja. I pod względem wydajności i efektów
>pozwalających na wybrnięcie z sytuacji gdzie dane wprowadzane nie
>weryfikowano z jakimiś słowikami.
>Dzięki za cenne wskazówki.

Coś mi się kojarzą problemy z aktualizacją indeksu, ale musiałbym poszukać.

O mam - domyślnie indeks jest tworzony z manualnym odświeżaniem.

Można też zrobić:
Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON
COMMIT)')

Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS 'SYNC (EVERY
"SYSDATE+1/24")')

[homar]

On 25 Paź, 23:05, Sławomir Szyszło <slasz...@CIACHTOlist.pl> wrote:
> Dnia Tue, 25 Oct 2011 11:25:01 -0700 (PDT), homar <marek.horody...@interia.pl>
> wklepał(-a):

[...]

> O mam - domyślnie indeks jest tworzony z manualnym odświeżaniem.
>
> Można też zrobić:
> Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON
> COMMIT)')
>
> Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS 'SYNC (EVERY
> "SYSDATE+1/24")')

Ale tam jest możliwości.
Myślę o update danych Jobem raz na dobę, wtedy będzie można zrobić
alter index rebuild.
Testy robię na 9i, i nie mam wyrażeń regularnych.
Czy jest jakiś sposób aby zbić w ciągu wielokrotne spacje do jednej ?
Takie :
regexp_replace( nazwa,'( ){2,}', ' ')

ale bez regexpów. Jak sobie wcześniej radzono w takich przypadkach ?

Pozdrawiam,
Marek Horodyski

[geos]

homar wrote:
> Jak sobie wcześniej radzono w takich przypadkach ?

może tak? :) -> Poor man's text index in Oracle

http://www.adp-gmbh.ch/ora/plsql/poor_mans_text_index.html

gdyby Ci się chciało przeprowadzić testy wydajności względem indeksów
pełnotekstowych to byłoby super :)

pozdrawiam,
geos

[Sławomir Szyszło]

Dnia Wed, 26 Oct 2011 05:43:54 -0700 (PDT), homar <marek.h...@interia.pl>
wklepał(-a):

>Ale tam jest możliwości.
>Myślę o update danych Jobem raz na dobę, wtedy będzie można zrobić
>alter index rebuild.

Nie musisz przebudowywać całego indeksu, to zresztą ma negatywny wpływ na
wydajność - bloki indeksu wylatują z cache.

Użyj procedury CTX_DDL.SYNC_INDEX.

>Testy robię na 9i, i nie mam wyrażeń regularnych.

Ehm, support dla 10g skończył się w zeszłym roku, a dla 9i to już nie wiem
kiedy. :)