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;
/

Kryzysowy( kredytowy ) SQL - symulacja spłaty kredytu

Post został  inspirowany  nocnymi dyskusjami na temat sensowności korzystania z kredytów hipotecznych. W pierwszym podejściu miał być to pakiet, ale postanowiłem ułatwić sobie życie i napisałem zapytanie wyświetlające plan spłaty kredytu  dla rat stałych i malejących.
Zapytanie można w łatwy sposób parametryzować (parametry mają kolor ciemnozielony) zmieniając:

• kwotę kredytu
• czas spłaty
• oprocentowanie w skali rocznej

Przyjęte zostały następujące założenia:

1. Oprocentowanie  podczas spłaty kredytu jest stałe
2. Raty są płatne z góry od  bieżącego dnia co miesiąc 
3. Zakładamy, że oprocentowanie w obrębie każdego miesiąca jest jednakowe

Poniższe zapytanie przy odrobinie  fantazji, można w ciekawy sposób rozwinąć, np zakładając  rózne oprocentowanie w kolejnych latach.

  SELECT d + 1 AS "Numer raty",
          DATA_RATY AS "Data raty",        
          ROUND (RATA_MAL_KAPITAL, 2) AS "Kapitał raty malejącej",
          ROUND (RATA_MAL_ODSETKI, 2) AS "Kwota odsetek",
          ROUND (RATA_MAL_KAPITAL + RATA_MAL_ODSETKI, 2) AS "Kwota raty  malejącej",
          ROUND (KWOTA_MAL_DO_SPLATY, 2) AS "Zadłużenie - raty malejące",
          ROUND (RATA_STALA_KAPITAL, 2) AS "Kapitał raty stałej",
          ROUND (RATA_STALA_ODSETKI, 2) AS "Odsetki raty stałej",
          ROUND (RATA_STALA, 2) AS "Kwota raty stałej",
          ROUND (KWOTA_STALA_DO_SPLATY, 2) AS "Zadłużenie - raty stałe"
     FROM (SELECT 1 FROM DUAL)
   MODEL
      DIMENSION BY (0 d)
      MEASURES (300000 KWOTA_KREDYTU, 6 OPROCENTOWANIE, 240 LICZBA_RAT,
      TRUNC (SYSDATE) DATA_RATY,
             0 KWOTA_MAL_DO_SPLATY,
             0 RATA_MAL_KAPITAL,
             0 RATA_MAL_ODSETKI,            
             0 RATA_STALA,
             0 RATA_STALA_KAPITAL,
             0 RATA_STALA_ODSETKI,
             0 KWOTA_STALA_DO_SPLATY
             )
      RULES
         ITERATE (10000) UNTIL (ITERATION_NUMBER = LICZBA_RAT[0] -1)
         (              
         LICZBA_RAT [ITERATION_NUMBER] =  
               NVL (LICZBA_RAT[ITERATION_NUMBER - 1], LICZBA_RAT[0]),
          OPROCENTOWANIE [ITERATION_NUMBER] =
               NVL (OPROCENTOWANIE[ITERATION_NUMBER - 1], OPROCENTOWANIE[0]/1200),
         RATA_MAL_KAPITAL [ITERATION_NUMBER] =
               KWOTA_KREDYTU[0] / LICZBA_RAT[0],
         DATA_RATY [ITERATION_NUMBER] =
               ADD_MONTHS (DATA_RATY[0], ITERATION_NUMBER),
         KWOTA_MAL_DO_SPLATY [ITERATION_NUMBER] =
                 NVL (KWOTA_MAL_DO_SPLATY[ITERATION_NUMBER - 1],
                      KWOTA_KREDYTU[0])
               - NVL (RATA_MAL_KAPITAL[ITERATION_NUMBER - 1], 0),
         RATA_MAL_ODSETKI [ITERATION_NUMBER] =
                 KWOTA_MAL_DO_SPLATY[ITERATION_NUMBER]
               * OPROCENTOWANIE[ITERATION_NUMBER]  ,                                       
         RATA_STALA_KAPITAL [ITERATION_NUMBER]              
         = KWOTA_KREDYTU[0] *OPROCENTOWANIE [ITERATION_NUMBER] *POWER( 1+OPROCENTOWANIE [ITERATION_NUMBER], ITERATION_NUMBER)/
          ( POWER( 1+OPROCENTOWANIE [ITERATION_NUMBER],  LICZBA_RAT[ITERATION_NUMBER]) -1  ),
           KWOTA_STALA_DO_SPLATY [ITERATION_NUMBER] =
                 NVL (KWOTA_STALA_DO_SPLATY[ITERATION_NUMBER - 1],
                      KWOTA_KREDYTU[0])
               - NVL (RATA_STALA_KAPITAL[ITERATION_NUMBER - 1], 0),
           RATA_STALA_ODSETKI [ITERATION_NUMBER] =
                 KWOTA_STALA_DO_SPLATY[ITERATION_NUMBER]
               * OPROCENTOWANIE[ITERATION_NUMBER],
           RATA_STALA[ITERATION_NUMBER] = RATA_STALA_KAPITAL[ITERATION_NUMBER] + RATA_STALA_ODSETKI[ITERATION_NUMBER]       
          )

Usuwanie indeksów z wysokim clustering factor

Opisany problem dotyczy bardzo niskiej wydajności operacji DML na  tabelach o trochę większym wolumenie (zetknąłem się problemem, kiedy wielkość tabel przekraczała około 300 mln rekordów), często partycjonowanych, zawierających klasyczne drzewiaste indeksy o  bardzo dużej selektywności. Wówczas najszybszą (w moi odczuciu :) ) metodą dokonania operacji DML na większym wolumenie danych jest:

1. Usunięcie indeksów o wysokim clustering factor
2. Wykonanie operacji DML
3. Ponowne stworzenie usuniętych indeksów 

W poniższym rozwiązaniu wykorzystałem własny pakiet logujący prosty logger wzorowany na log4j.
Do usuwania indeksów w innych schematach niż bieżący przydałyby się uprawnienia CREATE ANY INDEX i DROP ANY INDEX


CREATE TABLE TBL_INDEXES
(
  ID             NUMBER(10),
  SCHEMA_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  TABLE_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  INDEX_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  DDL_STATEMENT  CLOB
);


ALTER TABLE TBL_INDEXES ADD (
  CONSTRAINT PK_INDEXES_COMPLEX
  PRIMARY KEY (ID, SCHEMA_NAME, TABLE_NAME, INDEX_NAME);

CREATE SEQUENCE SEQ_INDEXES START  WITH 1 NOCACHE NOCYCLE;

CREATE OR REPLACE PACKAGE PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN;
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN;
END PCKG_INDEXES;

CREATE OR REPLACE PACKAGE BODY PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN
AS
vn_LiczbaBledow NUMBER(10) :=0;  
CURSOR C IS
    SELECT            
          INDEX_NAME,
          DDL_STATEMENT
        FROM TBL_INDEXES
        WHERE ID =
        (
            SELECT MAX(ID) FROM TBL_INDEXES WHERE TABLE_NAME = pc_TableName AND SCHEMA_NAME = pc_SchemaName
        );

BEGIN
    COMMIT; -- aby zamknąc wszystkie locki w danym połączeniu
     FOR EL IN C
        LOOP
            BEGIN              
                EXECUTE IMMEDIATE  EL.DDL_STATEMENT;
                PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_CreateIndexes', 'Pomyślnie utworzono indeks: ' || EL.INDEX_NAME);
            EXCEPTION WHEN OTHERS THEN          
                PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_CreateIndexes', 'Błąd podczas tworzenia indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );              
                vn_LiczbaBledow := vn_LiczbaBledow +1;
            END;       
        END LOOP;  

    IF vn_LiczbaBledow > 0 THEN      
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;      
END;
------------------------------------------------------------------------------------------
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN
AS
vn_SeqId NUMBER(10):= SEQ_INDEXES.nextval;
CURSOR C IS
    SELECT
            vn_SeqId AS ID,
            OWNER,
            TABLE_NAME,
            INDEX_NAME,
            DBMS_METADATA.GET_DDL( 'INDEX', INDEX_NAME, pc_SchemaName)  AS DDL_STATEMENT
        FROM ALL_INDEXES -- Tutaj działającą perspektywą zawsze będzieALL_INDEXES, dla ALL_INDEXES trzeba mieć specjalne uprawnienia
        WHERE TABLE_NAME = pc_TableName
            AND OWNER = pc_SchemaName
            AND 4*CLUSTERING_FACTOR > NUM_ROWS
            AND INDEX_TYPE = 'NORMAL';
BEGIN
     FOR EL IN C
        LOOP
            BEGIN
                INSERT INTO TBL_INDEXES VALUES EL;
                EXECUTE IMMEDIATE 'DROP INDEX ' || EL.INDEX_NAME;--- DDL czyli niejawny COMMIT = zatwierdzainstrukcję  insert
                PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Pomyślnie usunięto indeks: ' || EL.INDEX_NAME);
            EXCEPTION WHEN OTHERS THEN          
                DELETE FROM TBL_INDEXES WHERE ID = vn_SeqId;
                PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Błąd podczas usuwania indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );
                COMMIT;
                RETURN FALSE;
            END;       
        END LOOP;
    RETURN TRUE;
END;
END PCKG_INDEXES;