Wyszukiwanie z wykorzystaniem wyrażeń regularnych

Wyszukiwanie  wzorów określonych za pomocą wyrażeń  regularnych umożliwia  znaczące uproszczenie algorytmów do przetwarzania tekstów. Różnica polega na tym, że definiujemy wzorzec wyszukiwania, testujemy i ciągle  udoskonalamy..   Podczas rozpoczynania pracy z wyrażeniami regularnymi, okazuje się, że największym problemem jest prawidłowe zdefiniowanie wzorca.
Przeanalizujmy następujący przykład - w tekście wyszukujemy kod pocztowy..
Wyrażenie opisujące kod pocztowy z myślnikiem ma następującą postać:
[[:digit:]]{2}-[[:digit:]]{3} - to oznacza  wzorzec składający się z dwóch dowolnych cyferek, znaku myślnika i trzech cyferek. Ale czy działa on poprawnie??
SELECT REGEXP_SUBSTR('Mój kod pocztowy to 02-768', '[[:digit:]]{2}-[[:digit:]]{3}',1 ,1) FROM DUAL  --to polecenie wykona się poprawnie

SELECT REGEXP_SUBSTR('Mój telefon to to 502-559-373', '[[:digit:]]{2}-[[:digit:]]{3}',1 ,1) FROM DUAL  --to polecenie zwróci 02-559 i  bedzie przeszukiwać nr telefonów, nr kont. 
Jeśli mamy  wejściowy plik z adresami, to z reguły powinno wystarczyć. W innych przypadkach proponuję zastosowanie poniższego wzorca
(^|[[:blank:]])[[:digit:]]{2}-[[:digit:]]{3}($|[[:blank:]])
(^|[[:blank:]]) to podwyrażenie oznacza, że wzorzec powinien zaczynać się od początku  przeszukiwanego tekstu lub powinien być poprzedzony  znakiem niewidocznym, np. spacją
($|[[:blank:]])- to podwyrażenie oznacza, że wzorzec ma się kończyć na ostatnim znaku przeszukiwanego tekstu lub znakiem niewidocznym np. spacją

SELECT REGEXP_SUBSTR('Mój telefon to to 502-559-373', '(^|[[:blank:]])[[:digit:]]{2}-[[:digit:]]{3}($|[[:blank:]])',1 ,1) FROM DUAL  --teraz wyrażenie zadziała poprawnie i nic nie zwróci, dobrze są opisane warunki brzegowe
Często nie da się stworzyć ogólnego wzorca dla poszukiwanych danych, ale można znacznie uprościć mechanizmy wyszukiwania. Dużo łatwiej jest modyfikować wyrażenia regularne niż kod do analizy zawartości tekstu.

Poniżej inne ciekawe przykłady:
Wyszukiwanie adresu poczty elektroniczne:
SELECT REGEXP_SUBSTR( 'Mój mail to szkaradnik@gmail.com', '[[:alpha:]]([[:alnum:]]|\.){0,30}[[:alnum:]]@[a-zA-Z0-9._%-]+\.[[:alpha:]]{2,4}') FROM DUAL;

SELECT REGEXP_SUBSTR( 'Nie jestem posiadaczem adresu Kazimierz.Szpyt@gmail.com.pl - bo nie istnieje', '[[:alpha:]]([[:alnum:]]|\.){0,30}[[:alnum:]]@[a-zA-Z0-9._%-]+\.[[:alpha:]]{2,4}') FROM DUAL;

Wyszukiwanie  inicjałów z nazwiskiem, przy założeniu, że po inicjałach imion sa kropki, litery inicjałów i pierwsza litera nazwiska są duże:
SELECT REGEXP_SUBSTR( 'To nie sa moje inicjały to  A.   D.  C.Kowalski  :) ', '([[:upper:]]{1}.{1}[[:blank:]]*)+[[:upper:]]{1}[[:lower:]]+', 1,1)  FROM DUAL;
Należy zwrócić uwagę, że powyższe wyrażenie nie  działa poprawnie dla np. nazwisk dwuczłonowych

Wykrywanie podstringów w formacie DDDDSDDSDD,  DDDDSDSD, DDDDSDSDD lub DDDDSDDSD, gdzie D to cyfra, a S to separator w postaci któregoś z ukośników lub myślnika
SELECT REGEXP_SUBSTR('Co to za data 2000\11/23','[[:digit:]]{4}((\\)|-|/)[[:digit:]]{1,2}((\\)|-|/)[[:digit:]]{1,2}', 1, 1 ) FROM DUAL;
To wyrażenie jest pomocne w wykrywaniu wyrażeń będących datami.

Określanie, czy dany  tekst jest o charakterze politycznym (zwracanie liczby wystąpień nazw partii w tekście)
SELECT REGEXP_COUNT( 'POlityczni POpaprańcy POpierający POlityczną POprawność i POdnoszący POdatki', 'PO|PiS|SLD|PSL', 1 ) FROM DUAL;

Wyszukiwanie w danym tekście kolejnych zdań(niepustych tekstów rozdzielonych kropkami)
DECLARE
 vc_Wyrazenie VARCHAR2(75 CHAR)  := 'Ala  ma kota, psa i papugę.Ala ma także X-Box-a. Ala ma mnóstwo lalek.';
 vn_Liczba NUMBER;
 BEGIN
  vn_Liczba := REGEXP_COUNT ( vc_Wyrazenie,'[^(\.)]+');
   FOR el IN 1.. vn_Liczba
   LOOP
     DBMS_OUTPUT.PUT_LINE( REGEXP_SUBSTR ( vc_Wyrazenie,'[^(\.)]+', 1, el));
   END LOOP;
 END;
Zauważmy, że powyższy kod ma następujące ograniczenia:

• zwracane zdania nie kończą się kropką
• kod nie radzi sobie ze skrótowcami zawierającymi kropki, z inicjałami, do obróbki tekstów biorokratycznych sie nie nadaje, ale z beletrystyką poradzi sobie całkiem dobrze

Instrukcja REGEXP_SUBSTR nadaje się  do parsowania tekstu z separatorami ale pod warunkiem, że wszystkie tokeny są niepuste lub pozycja poszczególnego tokena jest bez znaczenia.
Poprawnie zostanie przeprocesowany tekst:
Kazimierz|Szpyt|śpioch|łasuch|
natomiast  poniższy tekst zostanie rozbity na tokeny, ale bez pustych tokenów
Kazimierz||Szpyt||||śpioch|łasuch|

Wydajny jak CLOB

Jest to problem występujący często w przy implementacji algorytmów,
Tworzymy korzystając z bibliotek dostarczanych od producenta kawałek  kodu i okazuje się, że jest albo za wolno albo niestabilnie dla naszego zbioru danych. Czasami udaje się nam  błądzić po parametrach brzegowych lub znaleźć zastosowania dla funkcji bibliotecznych o jakich się nie śniło ich autorom.

  Przykładem jest nadzwyczaj nędzna wydajność operacji  łańcuchowych na polach typu CLOB. Rozważmy poniższy blok kodu.
Instrukcja dbms_random.string('p', 4000) oznacza stwórz losowy łańcuch tekstowy składający się z  4000 widocznych na wydruku znaków ASCII.
Kod poniższy tworzy losowy łańcuch  długości 32000 znaków i zlicza w nim cyferki dwoma metodami za pomocą  zliczania naiwnego i wyrażenia regularnego reagującego na cyferki..
Celem dalszych testów przypomnijmy sobie, że w kodzie PL/SQL maksymalna wielkość  zmiennej  typu VARCHAR2 wynosi 32767 znaków...


DECLARE
    vc_Clob   CLOB := EMPTY_CLOB();
    vn_Start NUMBER;
    vn_Count NUMBER := 0;
    vn_Size NUMBER;
BEGIN
  vc_Clob :=dbms_random.string('p', 4000);

  vc_Clob := vc_Clob || vc_Clob;
  vc_Clob := vc_Clob || vc_Clob;
  vc_Clob := vc_Clob || vc_Clob;
  vn_Start := dbms_utility.get_time;
 
  vn_Size := LENGTH(vc_Clob);
  FOR lp IN 1..vn_Size
    LOOP
        IF  SUBSTR( vc_Clob, lp, 1) BETWEEN '0'  AND  '9' THEN
           vn_Count := vn_Count + 1;
        END IF;
    END LOOP;
  dbms_output.put_line( 'Metoda 1. Długość : ' || LENGTH (vc_Clob) || ', liczba cyferek '  || vn_Count );
  dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );

  vn_Start := dbms_utility.get_time;
  vn_Count := REGEXP_COUNT( vc_Clob,'[[:digit:]]');
  dbms_output.put_line( ' Metoda 2. Długość : ' || LENGTH (vc_Clob) || ', liczba cyferek '  || vn_Count );
  dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...');
END;

Po uruchomieniu  na wydajnym serwerze, radzącym sobie z terabajtowymi bazami, otrzymujemy następujący wynik
Metoda 1. Długość : 32000, liczba cyferek 3224
8.8 sekund...
 Metoda 2. Długość : 32000, liczba cyferek 3224
0.1 sekund...

Różnica jest olbrzymia - ale oba rozwiązania są wolne..

Zamieńmy w powyższym bloku linię
vc_Clob   CLOB := EMPTY_CLOB();
na
 vc_Clob VARCHAR(32000 CHAR);szybko okażesię ,że nawet grudniowe noce sa za krótkie
Wówczas otrzymamy następujące czasy wykonania:


Metoda 1. Długość : 32000, liczba cyferek 3424
0.01 sekund...
 Metoda 2. Długość : 32000, liczba cyferek 3424
0.16 sekund...

Dla metody naiwnej przyrost wydajności jest  prawie tysiąckrotny, wyrażenia regularne dla obu typów działają wolno z podobną szybkością..

Co więc robić jeśli chcemy  napisać zaawansowane przetwarzanie obszernych pól CLOB??

• przenieść procesowanie na godziny nocne, szybko okaże się, że nawet grudniowe noce są za krótkie
• napisać to przetwarzanie w Java/C++ na kliencie na jakimś złomowatym serwerze. I tak będzie szybciej:)
• postarać się tak zmienić strukturę danych, aby korzystać tylko z typów VARCHAR2, co nie zawsze jest  wykonalne

Niektóre operacje  np. zaawansowana  obróbka dużej ilości danych w XML, algorytmy tekstowe na d polach CLOB powinny być wyprowadzone poza motor bazy danych..

Kto mnie trzyma??

 Problem blokad jest wpisany w naturę  przetwarzania bazodanowego.. Często powstają one w najmniej pożądanej chwili i są w stanie skutecznie  zdezorganizować kluczowe wielkie procesy np zamknięcie miesiąca, złożone raportowanie, przetwarzanie transakcyjne.. Dlatego warto takie sytuacje logować (wykorzystałem wcześniej opisany pakiet  PCKG_LOGGING).
Poniższa funkcja wykrywa blokady da danego obiektu  bazodanowego w określonym schemacie i  zapisuje informacje o blokadach z wykorzystaniem transakcji autonomicznych, nie ingeruje zatem w logikę transakcyjną kodu w którym jest użyta.
Co ciekawego można zrobić z poniższym kodem:

1. dodanie tabeli mapującej loginy użytkowników na adresy poczty elektronicznej  i wysyłanie maili do niesfornych użytkowników przy wykorzystaniu pakietu utl_smtp, warto także w ten sposób poinformować osobę bezpośrednio odpowiedzialną za bieżące przetwarzanie bazodanowe
2. jeśli  DBA opakuje ładnie kill session w procedurę PL/SQL - ubijać sesje tworzące nieporządane blokady

Wymagane uprawnienia - dostęp do widoków systemowych v$session, v$locked_object
CREATE OR REPLACE FUNCTION FDB_IsObjectUsed( pc_ObjectOwner VARCHAR2, pc_ObjectName VARCHAR2 ) RETURN NUMBER
AS
CURSOR C_MAIN IS
    SELECT 'Użytkownik bazodanowy ' || USERNAME || ' w sesji NR ' || SID
        || ' zalogowany do domeny jako '  || OSUSER ||  ' na komputerze ' || MACHINE
        || DECODE ( PROGRAM, NULL, NULL, ' korzystający z programu ' || PROGRAM) AS MSG,
        USERNAME,
        OSUSER 
 FROM v$session
 WHERE
   SID IN
 (SELECT DISTINCT SESSION_ID FROM v$locked_object WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = pc_ObjectName AND OWNER =pc_ObjectOwner))
AND SID <> sys_context('USERENV', 'SID')
ORDER BY OSUSER;
 vn_Result NUMBER := 0;
 vc_Body VARCHAR2(4000 CHAR);
 vc_LogMessage VARCHAR2( 4000 CHAR);
BEGIN
  vc_LogMessage := 'Następujący użytkownicy blokują obiekt ' ||  pc_ObjectOwner || '.' || pc_ObjectName ||  ': ';
  FOR EL IN C_MAIN
    LOOP   
        vn_Result := vn_Result +1;

           IF vc_Body IS NULL THEN
             vc_Body := vc_Body || EL.MSG;
          ELSE           
              vc_Body := vc_Body || CHR(13) || CHR(10) || EL.MSG;
          END IF;
                      
    END LOOP;
vc_LogMessage := vc_LogMessage || CHR(13) || CHR(10) || vc_Body;
   IF vn_Result > 0 THEN                  
    PCKG_LOGGING.PDB_ERror('Wykryto blokadę na obiekcie ' || pc_ObjectOwner || '.' || pc_ObjectName, vc_LogMessage);
   END IF;
        RETURN vn_Result;   
   EXCEPTION WHEN OTHERS THEN
        RETURN vn_Result;
END;
/