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 dKommen 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(*) ---------- 500000Jetzt 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(*) ---------- 500000Man 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.
nur als Randnotiz: bei Tom Kyte liest man ziemlich regelmäßig den Hinweis, dass man alles, was man mit SQL machen kann, auch mit SQL machen sollte, und wenn's mit SQL nicht funtioniert, dann mit PL/SQL - und erst wenn's auch damit nicht funktioniert kommen Java und andere Sprachen in Betracht. Auf der Suche nach einem entsprechenden Zitat bin ich bei AskTom dann auf einen Thread gestossen, in dem der Herr Kyte sich explizit zum Thema String-Tokenzizer äußert (http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2189860818012#17088075063394):
AntwortenLöschen"Now, if you had a couple documents AND you needed to parse them THEN java would be a good idea.
If you have a couple of simple strings AND they are columns in a table AND you needed to extract data from them THEN substr/instr et.al. are a good idea
So, it is not that java is evil, or to be avoided - just that the heinous overheads of calling plsql or java (specially the latter) from SQL far outweighs the keystrokes you may or may not save."
Im gegebenen Beispiel würd ich wahrscheinlich auch zunächst an SUBSTR und INSTR denken; aber interessant sind die Möglichkeiten von piplined functions zur Ergänzung zusätzlicher Möglichkeiten neben den Built-In-Funktionen natürlich allemal.
Viele Grüße
Martin