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.

BINARY_DOUBLE und die implizite Typ-Konvertierung

Heute geht's um die Verwendung des Datentyps BINARY_DOUBLE bzw. BINARY_FLOAT und die Vermeidung von impliziter Typ-Konvertierung.

Als Beispiel dient das Wallis-Produkt zur Berechnung der Kreiszahl Pi. Die Berechnung erfolgt durch den folgenden anonymen PL/SQL-Block:

declare
 v_pi binary_double := 1.0;
begin
 for i in 1 .. 1000000 loop
  v_pi := v_pi * (1.0 + (1.0 / (4.0 * i * i - 1.0)));
 end loop;
 v_pi := v_pi * 2.0;
 dbms_output.put_line(v_pi);
end;
/
3,1415918681921307E+000

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:05.07

Die Berechnung dauert ungefähr fünf Sekunden und gibt die Zahl Pi bis auf fünf Stellen genau an. Doch an welcher Stelle befindet sich nun das Optimierungspotenzial?

Literale vom Typ BINARY_DOUBLE bzw. BINARY_FLOAT enthalten den Buchstaben d (D) bzw. f (F). Ansonsten handelt es sich um Literale vom Typ NUMBER, die im vorherigen PL/SQL-Block zur Typ-Konvertierung geführt haben.

Der angepasste PL/SQL-Block sieht dann wie folgt aus:

declare
 v_pi binary_double := 1.0d;
begin
 for i in 1 .. 1000000 loop
  v_pi := v_pi * (1.0d + (1.0d / (4.0d * i * i - 1.0d)));
 end loop;
 v_pi := v_pi * 2.0d;
 dbms_output.put_line(v_pi);
end;
/
3,1415918681921489E+000

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.46

Jetzt reduziert sich die Dauer der Berechnung deutlich auf unter eine Sekunde.

Also sollte man bei der Verwendung von BINARY_DOUBLE bzw. BINARY_FLOAT stets auf die korrekte Angabe von Literalen achten.