Jak zamienić listę na tabelę?

Ostatnio omawialiśmy funkcję LISTAGG, która umożliwia przedstawienie danych z kolumny w postaci listy rozdzielanej separatorem. Co w sytuacji, gdy chcemy zamienić listę na tabelę? W tę stronę już nie jest tak łatwo. Nie ma funkcji będącej odwrotnością LISTAGG. Musimy ją napisać sami. By wyłuskać elementy z tekstu możemy skorzystać z jednej z następujących funkcji: 

1. regexp_substr

2. substr

3. dbms_utility.comma_to_table

REGEXP_SUBSTR

W celu podzielenia stringu na elementy możemy wykorzystać wyrażenia regularne. Warto zwrócić uwagę na ustawienia NLS_LANGUAGE, gdyż funkcje regexp w językach innych niż angielski potrafią bardzo stracić na wydajności.

 declare
 vEl varchar2(32000);  
 nCntElements number;  
 begin  
 nCntElements := length(regexp_replace(pList, '[^,]+') ) + 1;  
   for i in 1..nCntElements loop  
     vEl := regexp_substr(pList, '[^,]+', 1, i);  
   end loop;  
 dbms_output.put_line(nCntElements);  
 end;  

SUBSTR

Stary dobry substr czyli dziabanie na piechotę element po elemencie. 

   
 declare
 vEl varchar2(32000);
 nCntElements number := 0;  
 nStartPos number;  
 nEndPos number := 0;  
 begin  
 nStartPos := 1;  
    loop  
     nCntElements := nCntElements +1;  
     nStartPos := nEndPos +1;  
     nEndPos := instr(pList,',', nStartPos);  
     vEl := substr(pList,nStartPos, case when nEndPos= 0 then length(plist) else nEndPos - nStartPos end);     
     exit when nEndPos = 0;  
   end loop;  
   dbms_output.put_line(nCntElements);   
 end;  

dbms_utility.comma_to_table

Funkcja wbudowana oracle z pakietu dbms_utility. Funkcja w przeciwieństwie do wcześniejszych nie może być użyta w SQL. Drugą niedogodnością jest to, że jako separator elementów listy dozwolony jest jedynie przecinek a elementy listy muszą być tekstem spełniającym reguły nazewnictwa obiektów w oracle czyli np. element nie może zaczynać się cyfrą czy znakiem specjalnym. 

 
 declare
 tab dbms_utility.uncl_array;  
 nCntElements number := 0;  
 vEl varchar2(32000);  
 BEGIN  
    dbms_utility.comma_to_table(pList, nCntElements, tab);  
    for i in 1..nCntElements  
    loop  
    vEl := tab(i);  
    end loop;  
    dbms_output.put_line(nCntElements);  
 END;  

Z ograniczeniami możemy sobie poradzić zamieniając oryginalny separator na przecinek a elementy otaczać cudzysłowami (tutaj jednak znacząco dozwolona ilość elementów może się zmniejszyć ze względu na  to, że do zmiennej dodajemy dodatkowe znaki). Niemniej funkcja działa szybko i sprawnie. 

 tab dbms_utility.uncl_array;  
 declare
 nCntElements number := 0;  
 nEl varchar2(32000);  
 vList varchar2(32000);  
 BEGIN  
   vList := '"'|| replace(pList, ',', '","') ||'"';  
   dbms_utility.comma_to_table(vList, nCntElements, tab);  
    for i in 1..nCntElements  
    loop  
    nEl := trim( '"' FROM tab(i));  
    end loop;  
   dbms_output.put_line(nCntElements);  
  END;  

Istnieje również funkcja odwrotna czyli dbms_utility.table_to_comma.

Co wybrać?

Przeprowadziłam kilka testów wydajnościowych porównując czasy wykonań. Testowałam listy z elementami jedno znakowymi o długościach 2000, 5000, 10000, 20000, 32000. Dla testu symulującego listę numeryczną przy wykorzystaniu funkcji comma_to_table  maksymalnym testem był test listy o długości 16000 znaków. Dodatkowo funkcję regexp przetestowałam i dla języka polskiego i angielskiego.

Testy przeprowadziłam na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.

Wyniki testów (czas w sekundach):

Nie ukrywam, że wyniki mnie mocno zaskoczyły. Nie spodziewałam się, że comma_to_table i substr będą praktycznie tak samo szybkie. Ale to co mnie zdziwiło najbardziej, to to, że funkcja regexp w obu ustawieniach języka jest tak strasznie wolna! Między regexp a nawet zwykłym substr to przepaść!

Decyzję, którą funkcję użyć zostawiam Wam. Niemniej mam nadzieję, że ten wpis trochę pomoże w wyborze.

Kod źródłowy:

Specyfikacja

Body

Test