Zeichenketten mit PL/SQL zerlegen

Mit Bezug auf den vorherigen Post möchte ich heute eine Lösung mithilfe von PL/SQL aufzeigen und die beiden Möglichkeiten vergleichen; insbesondere hinsichtlich der Performance.

create or replace function split_string_plsql(
 p_list_in in varchar2,
 p_delimiter_in in varchar2
 )
return string_nt pipelined
is
 v_before_pattern integer;
 v_after_pattern integer;
 v_list varchar2(32767) := p_list_in;
begin
 loop
  v_before_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 0, 'c');
  if (v_before_pattern = 0) then
   pipe row(v_list);
   exit;
  else
   pipe row(substr(v_list, 1, v_before_pattern - 1));
  end if;
  v_after_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 1, 'c');
  v_list := substr(v_list, v_after_pattern);
  exit when v_list is null;
 end loop;
 return;
exception
 when others then
  return;
end split_string_plsql;
/

Auch damit kann man Zeichenketten zerlegen, wobei das Trennzeichen auch durch einen regulären Ausdruck angegeben werden kann.

select 
 column_value
from 
 table(split_string_plsql('a....b...c....d', '[.]+'));

COLUMN_VALUE
-------------
a
b
c
d       

Kommen wir nun zum Performance-Duell der beiden Varianten. Dafür steht eine Tabelle bereit, welche 100.000 Zeichenketten enthält, die jeweils durch ein Semikolon in fünf Felder unterteilt sind.

select * from strings_to_split;

STRING
------------------------------------------------------------------------------------
BnHZjfREKnUMdSbwOBflCRbqZISiKbLtraWEIuLpEKQCilFrRp;CZXLlbwZHuGcyCOqQlNnmTMwnlDhhryVH
hmwVoAdyOXKsTBhow;YrKdmqKbsSSvuPbtZTrPUPAkxfmNechcYrVkLSJfPnjvsaHwdk;iVxogWXxrQSsDRK
ofFhkZoHaKNqHgbPVjbQOywpNAdLMxabPAa;LgklKsjnwNxmTbriHCNABgisxWOjqLnBoRMaOSONbwiIosMS

acwjSJDzUBDWolfKMhtGwizwJpZvKMipMnuGKsRhbSpDzHHvqh;yIBOHkSVedWmpgoyczdxsoAiKvqGrzWYD
LbqZeIZjgeHOQRRDd;EAjqQJErVzkouioxcPLxYMgIEHERANYuGaqnhIDwpJTCIYeGWZ;NqejlJHoIQWcmca
acbqstRZeAhlKPbgSHexKEOPzEEkpYPkvez;QXYvDNXPtAkoGQvUnqqpagfbhShJZegBinXkgfurKoCVQAYX

ELbJayqFFdsDRcoHYmvqLxfsKqgDzNhmVBoHzYtcNYjLTzwaIL;qjVLHPBINaLXrbegLQAiklKeBexbTTCWn
VYoBXJBhLwlErEucT;hgMSqrOTmaiEfBvkMtynhtcwTodPZeyeIfltOCoLPkboCdwIfC;fmnEQBVqiQWNyuG
UxtDkwceZckXYFyqJBNiOgdWTJkPmGWFYiG;OslJbHXKdVALzjdFbeYcoGeTXCzjyKrlQqISRYXMkgpVDmpr

ztfMZluktZSOwtMIeQIBRLKNyrtUKogyddXQWLbCsjnTFwpTmL;nHDbrxlYhjabiWDpfSEnLWAvVXIzgkDhb
NPYwOBEAwmJZvZSmK;jApmGJeLaXioFJxpAelEugPrxkRZNIirzVWKqFYqweDHNymmoK;zUyCCgkGLURMCat
EzvOVgwIoLIeCeluQtMKkXkcieHSbRpjCDc;TTjxzLccEhSfXFgbxaKVndDhxymsrOlyWOjtcWoPSqPznLjP

LWgGKQSVpqaOabvBYAbUrPQaVbomxyaxOXVvPojYkDkPqNrozP;gafSCgxUsHPtTENvKpPfaJDMkhdIOVqUM
dRdeMsNDehCdxzvEF;sRxDbQIJaQCopFlgWxQGJOUwhDHDHonWZLYSlVmrugAiToLDAt;DJJZCaVEGjFvgAc
bGbaXYQeRodCgAixLMQOpxvPfBqQSrWHHJu;MkUeKsmMFCISPSzTldvrGIRrqQnmYxXPQUNHDzcTsGNeyBJC

OOPamDAHRipKbVWWaSScmdjIWFJbunDBBnXKTRceqsJEpOcnxQ;dwffwkUYBgqEJSWNUvnzAQKbgcsLwTWmY
ZSzmVMZqKWlxNFezc;OdEHsJZYrsEqLaGJEvnimLFJkxdQGgEGpAbOwxxJsqLXzwTbgk;wsqFmybhYypdegs
gEHXETxmyGmIZkQiZIwlrEQizNlbcqlXQeX;BmYSjCeyeWPJHgvbVjwbgSsygJiJPlJJMIjScmvQNUapgJMw

...

Nun sollen die Zeichenketten in jeweils fünf Zeichenketten zerlegt werden, wodurch eine Tabelle mit 500.000 Zeilen entsteht. Beginnen wir mit der Java-Variante aus dem vorherigen Post:

create table sub_strings as 
 select 
  t.* 
 from 
  strings_to_split s, 
  table(split_string(s.string, ';')) t;

Tabelle wurde erstellt.

Abgelaufen: 00:04:37.35

select count(*) from sub_strings;

COUNT(*)
----------
    500000

Jetzt zum Vergleich die PL/SQL-Variante:

create table sub_strings_plsql as 
 select 
  t.* 
 from 
  strings_to_split s, 
  table(split_string_plsql(s.string, ';')) t;

Tabelle wurde erstellt.

Abgelaufen: 00:00:19.81

select count(*) from sub_strings;

COUNT(*)
----------
    500000

Man sieht deutlich, dass die PL/SQL-Variante um ein Vielfaches schneller ist; mehr als 14 mal so schnell.

An dieser Stelle gilt der Dank Carsten Czarski, der mich darauf aufmerksam gemacht hat, wie viel Zeit für das Context-Switching zwischen Java und SQL verloren geht.

Zeichenketten zerlegen mithilfe von Java in der Datenbank

Heute geht's um die Zerlegung von Zeichenketten. Dabei soll die Trennung durch ein Trennzeichen vorgenommen werden, welches auch als regulärer Ausdruck angegeben werden kann. Die Zeichenkette 'a,b,c' enthält drei, durch Kommata getrennte, Elemente. Eine Zerlegung, mit dem Komma als Trennzeichen, soll dann die Elemente 'a', 'b' und 'c' enthalten.

Die Elemente, die aus der Zerlegung hervorgehen, werden in einer Nested Table gespeichert:

create or replace type string_nt as table of varchar2(100);
/

Natürlich kann die Aufgabe auch mit PL/SQL gelöst werden, jedoch enthält Java bereits eine Methode zur Lösung des Problems; die Methode split der Klasse String.

create or replace and compile java source named string_utilities as

import java.sql.Connection;
import java.sql.DriverManager;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class StringUtilities
{
 public static ARRAY split(String input, String delimiter) throws Exception
 {
  Connection connection = DriverManager.getConnection("jdbc:default:connection:");

  ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("STRING_NT", connection);

  String[] elements = input.split(delimiter);
 
  return new ARRAY(aDesc, connection, elements);
 }
}
/

Der Java-Code ruft die Methode split auf, um das erste Argument in seine Bestandteile zu zerlegen. Das zweite Argument beschreibt das Trennzeichen als regulären Ausdruck.

Nun fehlt nur noch die Funktion in PL/SQL, um die Methode verwenden zu können.

create or replace function split_string (
 p_string_in in varchar2, 
 p_delimiter_in in varchar2
) return string_nt
is language java name 
'StringUtilities.split(java.lang.String, java.lang.String) return oracle.sql.ARRAY';
/

Da der Rückgabetyp der Funktion eine Collection (Nested Table) ist, erfolgt der Aufruf im FROM-Teil des Select-Statements mit dem Table-Keyword.

select 
 column_value 
from 
 table(split_string('a,b,c,d', ','));

COLUMN_VALUE
-------------
a
b
c
d       

Wie bereits angedeutet, wird das Trennzeichen als regulärer Ausdruck interpretiert. Das folgende Beispiel trennt die Elemente bei einem '#' oder einem '/'.

select 
 column_value 
from 
 table(split_string('a#b/c#d', '(#|/)'));

COLUMN_VALUE
-------------
a
b
c
d     

Durch die Beschreibung als regulärer Ausdruck können auch sehr komplexe Trennzeichen beschrieben werden.

Abschließend sei noch erwähnt, dass die Funktion natürlich auch in PL/SQL verwendet werden kann, wie das folgende Beispiel zeigt:

declare
 v_columns string_nt := split_string('empno;ename;job;sal', ';');
begin
 for i in v_columns.first .. v_columns.last loop
  dbms_output.put_line(i || ': ' || v_columns(i));
 end loop;
end;
/

1: empno
2: ename
3: job
4: sal