Extract from CLOB with JSON objects

On AskTOM, Kim Berg Hansen recently used JSON_OBJECT to parse CLOB data in “name=value” format. I added a variant based on my work with CSV data.

Kim decided to use objects instead of arrays for an excellent and important reason:

  • a JSON object is an unordered collection of name/value pairs.
  • a JSON array is an ordered list of values.

CSV data is an ordered list of values, going from left to right, so a JSON array is the obvious choice. The AskTOM question concerned name/value pairs that were not necessarily in the same order and not necessarily all there! A JSON object was the natural choice.

I’m really liking JSON more and more: not only is it simple, but it seems pretty easy to figure out how best to use it for different problems.

Instead of copying everything here, I’m going to be lazy for once and invite you to read my contribution here:

More on JSON_TABLE

You will see some of the same ideas:

  • Use PIPE_CLOB to cut the CLOB into VARCHAR sized bites;
  • Use JSON_ARRAY on the whole bite to escape characters if needed;
  • Use REPLACE to form an overall array, but this time of objects;
  • Then use JSON_TABLE to generate one row per object and one column per value.

Hope this helps…

Advertisements

Extract CSV from CLOB with JSON arrays

Marc Bleron blogged about CSV CLOBs and JSON_TABLE two years ago. Here’s my contribution to improve on a great idea.

“Simple” CSV

The Oracle Database does a fine job of parsing CSV data in flat files, using the External Tables facility. Unfortunately, this service is not available for CSV data in VARCHARs or CLOBs. Marc showed that JSON_TABLE (and XML_TABLE) can parse “simple” CSV if it is reformatted. What is “simple”?

CSV data consists of records and fields within records.

  • Records are delimited by NEWLINE (or some other string).
  • Fields are terminated by commas (or some other string),
  • If necessary, some or all fields can be enclosed by double quotes " (or some other string).

When Marc says “simple”, he means that fields are never enclosed. This is important, because enclosed fields may contain the double quote (provided it is present twice in a row) and / or the comma. With “simple” CSV, we know that all commas are true field terminators and we don’t have to replace "" with " .

“Simple” also means that there is no trimming of whitespace: you get everything between the commas.

Finally, Marc assumes there is no terminator after the last field of the record, even though Oracle allows it.

So, “simple” CSV has delimited records with terminated fields that are never enclosed. There is no trimming of whitespace and the last field in the record is not terminated.

My contribution

  • First of all, I break the CLOB into VARCHAR2 bites using the pipelined table function PIPE_CLOB (as explained in my previous post).
  • Then I remove any field terminator that immediately precedes a record delimiter.
  • Then I use JSON_ARRAY over the entire VARCHAR2 in case some characters need to be escaped.
  • Then I do several REPLACES such that:
    • each record becomes a JSON array of string values, and
    • those arrays are included in one overall array.
  • Finally, I use JSON_TABLE to break the overall array into rows and the inner arrays into columns.

Note that everything before the COLUMNS clause in JSON_TABLE is generic, because the inner arrays can contain any number of elements.

To demonstrate, here is a CLOB containing data from the EMP table, with a trailing comma added:

7369,SMITH,CLERK,7902,1980-12-17T00:00:00,800,,20,
7499,ALLEN,SALESMAN,7698,1981-02-20T00:00:00,1600,300,30,
7521,WARD,SALESMAN,7698,1981-02-22T00:00:00,1250,500,30,
7566,JONES,MANAGER,7839,1981-04-02T00:00:00,2975,,20,
7654,MARTIN,SALESMAN,7698,1981-09-28T00:00:00,1250,1400,30,
7698,BLAKE,MANAGER,7839,1981-05-01T00:00:00,2850,,30,
7782,CLARK,MANAGER,7839,1981-06-09T00:00:00,2450,,10,
7839,KING,PRESIDENT,,1981-11-17T00:00:00,5000,,10,
7844,TURNER,SALESMAN,7698,1981-09-08T00:00:00,1500,0,30,
7876,ADAMS,CLERK,7788,1987-05-23T00:00:00,1100,,20,
7900,JAMES,CLERK,7698,1981-12-03T00:00:00,950,,30,
7902,FORD,ANALYST,7566,1981-12-03T00:00:00,3000,,20,
7934,MILLER,CLERK,7782,1982-01-23T00:00:00,1300,,10,

And the code:

with last_term_removed as (
  select replace(column_value, ','||chr(10), chr(10)) str
  from table(pipe_clob((select c from t), 3500))
)
, json_data as (
  select '[' ||
    replace (
      replace(json_array(str), ',', '","'),
      '\n',
      '"],["'
    )
    || ']' jstr  
  from last_term_removed
)
select sql_data.*
from json_data j, json_table(
   j.jstr, '$[*]'
   columns empno    number        path '$[0]'
         , ename    varchar2(128) path '$[1]'
         , job      varchar2(128) path '$[2]'
         , mgr      number        path '$[3]'
         , hiredate date          path '$[4]'
         , sal      number        path '$[5]'
         , comm     number        path '$[6]'
         , deptno   number        path '$[7]'
) sql_data;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10

 

Scalability: yes!

When Marc tested reading CLOBs directly, performance went bad as the CLOB increased in size:

Rows Seconds
91336 2.2
182672 4.3
365344 9.4
730688 22
1461376 840

 

In my tests with very similar data, the number of rows per second remains about the same:

LINES SECS LINES_PER_SEC AVG_LINES_PER_SEC PCT_DIFF
91336 1.374 66475 68517 -3
182672 2.6 70258 68517 2.5
365344 5.35 68289 68517 -.3
730688 10 73069 68517 6.6
1461376 22 66426 68517 -3.1

 

Split a string again and again

I recently needed to gather multiple columns from multiple rows into one string, then split them out again. JSON_TABLE did both splits at the same time!

The situation

I want to find the shortest “foreign key” path between two tables in the OBE schema: OEHR_ORDER_ITEMS and OEHR_DEPARTMENTS. I also want the intermediate tables and the constraints used to connect the tables. As a starting point for today, I’ll create a little table with the data for the tables in the OBE schema that start with ‘OEHR’:

drop table t purge;
create table t as
with search_for (owner, table_name) as (
  select 'OBE', 'OEHR_DEPARTMENTS' from dual union all
  select 'OBE', 'OEHR_ORDER_ITEMS' from dual
)
, pur as (
  select OWNER, CONSTRAINT_NAME, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner = 'OBE'
  and table_name like 'OEHR%'
)
, relations as (
  select a.table_name, a.constraint_name,
    b.table_name r_table_name, a.r_constraint_name
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
select * from relations;

Now I’ll use a hierarchical query to find paths from one table to the other:

select rownum rn, level lvl, TABLE_NAME, CONSTRAINT_NAME, R_TABLE_NAME, R_CONSTRAINT_NAME
from t a
start with table_name = 'OEHR_ORDER_ITEMS'
connect by nocycle table_name = prior r_table_name
and table_name != 'OEHR_DEPARTMENTS';
RN LVL TABLE_NAME CONSTRAINT_NAME R_TABLE_NAME R_CONSTRAINT_NAME
1 1 OEHR_ORDER_ITEMS OEHR_ORDER_ITEMS_ORDER_ID_FK OEHR_ORDERS OEHR_ORDER_PK
2 2 OEHR_ORDERS OEHR_ORDERS_CUSTOMER_ID_FK OEHR_CUSTOMERS OEHR_CUSTOMERS_PK
3 3 OEHR_CUSTOMERS OEHR_CUST_ACCT_MGR_FK OEHR_EMPLOYEES OEHR_EMP_EMP_ID_PK
4 4 OEHR_EMPLOYEES OEHR_EMP_DEPT_FK OEHR_DEPARTMENTS OEHR_DEPT_ID_PK
5 4 OEHR_EMPLOYEES OEHR_EMP_JOB_FK OEHR_JOBS OEHR_JOB_ID_PK
6 2 OEHR_ORDERS OEHR_ORDERS_SALES_REP_FK OEHR_EMPLOYEES OEHR_EMP_EMP_ID_PK
7 3 OEHR_EMPLOYEES OEHR_EMP_DEPT_FK OEHR_DEPARTMENTS OEHR_DEPT_ID_PK
8 3 OEHR_EMPLOYEES OEHR_EMP_JOB_FK OEHR_JOBS OEHR_JOB_ID_PK
9 1 OEHR_ORDER_ITEMS OEHR_ORDER_ITEMS_PRODUCT_ID_FK OEHR_PRODUCT_INFORMATION OEHR_PRODUCT_INFO_PK

 

Just looking at the data myself, I can see that the shortest path will include rows 1, 6 and 7. Rows 1 through 4 will work too, but the path is longer.

Getting the data for the whole path

The problem is, how can I identify those three rows I want and just query them? The only way I see is to use SYS_CONNECT_BY_PATH (or the equivalent with a CTE) to string together all the data from all the rows I want – which means I have to split that data out again later.

To simplify what follows, I’m going to manually delete the rows that don’t matter:

delete from t where table_name not in ('OEHR_ORDER_ITEMS','OEHR_ORDERS','OEHR_EMPLOYEES');
delete from t where r_table_name not in ('OEHR_DEPARTMENTS','OEHR_ORDERS','OEHR_EMPLOYEES');
commit;

The rows that are left contain the data I want:

TABLE_NAME CONSTRAINT_NAME R_TABLE_NAME R_CONSTRAINT_NAME
OEHR_ORDER_ITEMS OEHR_ORDER_ITEMS_ORDER_ID_FK OEHR_ORDERS OEHR_ORDER_PK
OEHR_ORDERS OEHR_ORDERS_SALES_REP_FK OEHR_EMPLOYEES OEHR_EMP_EMP_ID_PK
OEHR_EMPLOYEES OEHR_EMP_DEPT_FK OEHR_DEPARTMENTS OEHR_DEPT_ID_PK

 

Now I’ll string that data together:

select sys_connect_by_path(
  table_name  ||'/'||  constraint_name||'/'||
  r_table_name||'/'||r_constraint_name
  ,'<'
) path
from t a
where r_table_name = 'OEHR_DEPARTMENTS'
start with table_name = 'OEHR_ORDER_ITEMS'
connect by table_name = prior r_table_name;

<OEHR_ORDER_ITEMS/OEHR_ORDER_ITEMS_ORDER_ID_FK/OEHR_ORDERS/OEHR_ORDER_PK
<OEHR_ORDERS/OEHR_ORDERS_SALES_REP_FK/OEHR_EMPLOYEES/OEHR_EMP_EMP_ID_PK
<OEHR_EMPLOYEES/OEHR_EMP_DEPT_FK/OEHR_DEPARTMENTS/OEHR_DEPT_ID_PK

I split the data into three lines for legibility. Note that I have to use different literals in the two parameters of SYS_CONNECT_BY_PATH, otherwise I get an exception.

Splitting out the data

Now we’re finally getting to the point. I can use JSON_ARRAY to string together the data for one row. Once I have all the data for all the rows, I can turn that into an array of arrays. Finally, JSON_TABLE will split everything out for me. Let’s start with JSON_ARRAY:

select sys_connect_by_path(
  json_array(table_name, constraint_name, r_table_name, r_constraint_name)
  , '
'
) path
from t a
where r_table_name = 'OEHR_DEPARTMENTS'
start with table_name = 'OEHR_ORDER_ITEMS'
connect by table_name = prior r_table_name;

["OEHR_ORDER_ITEMS","OEHR_ORDER_ITEMS_ORDER_ID_FK","OEHR_ORDERS","OEHR_ORDER_PK"]
["OEHR_ORDERS","OEHR_ORDERS_SALES_REP_FK","OEHR_EMPLOYEES","OEHR_EMP_EMP_ID_PK"]
["OEHR_EMPLOYEES","OEHR_EMP_DEPT_FK","OEHR_DEPARTMENTS","OEHR_DEPT_ID_PK"]

Now I’ll turn that data into an array of arrays:

with data as (
  select sys_connect_by_path(
    json_array(table_name, constraint_name, r_table_name, r_constraint_name)
    , '/'
  ) path
  from t a
  where r_table_name = 'OEHR_DEPARTMENTS'
  start with table_name = 'OEHR_ORDER_ITEMS'
  connect by table_name = prior r_table_name
)
select '['||substr(replace(path,'/',','),2) || ']' path
from data;
[
  [
    "OEHR_ORDER_ITEMS",
    "OEHR_ORDER_ITEMS_ORDER_ID_FK",
    "OEHR_ORDERS",
    "OEHR_ORDER_PK"
  ],
  [
    "OEHR_ORDERS",
    "OEHR_ORDERS_SALES_REP_FK",
    "OEHR_EMPLOYEES",
    "OEHR_EMP_EMP_ID_PK"
  ],
  [
    "OEHR_EMPLOYEES",
    "OEHR_EMP_DEPT_FK",
    "OEHR_DEPARTMENTS",
    "OEHR_DEPT_ID_PK"
  ]
]

Now JSON_TABLE will break down the top array into rows, just by putting ‘$[*]’ in the second parameter. The COLUMNS will do the rest, splitting the data in the inner arrays into different columns.

with data as (
  select sys_connect_by_path(
    json_array(table_name, constraint_name, r_table_name, r_constraint_name)
    , '/'
  ) path
  from t a
  where r_table_name = 'OEHR_DEPARTMENTS'
  start with table_name = 'OEHR_ORDER_ITEMS'
  connect by table_name = prior r_table_name
)
select a.* from data, json_table(
  '['||substr(replace(path,'/',','),2) || ']',
  '$[*]' columns(
    rn for ordinality,
    table_name varchar2(128) path '$[0]',
    constraint_name varchar2(128) path '$[1]',
    r_table_name varchar2(128) path '$[2]',
    r_constraint_name varchar2(128) path '$[3]'
  )
) a;
RN TABLE_NAME CONSTRAINT_NAME R_TABLE_NAME R_CONSTRAINT_NAME
1 OEHR_ORDER_ITEMS OEHR_ORDER_ITEMS_ORDER_ID_FK OEHR_ORDERS OEHR_ORDER_PK
2 OEHR_ORDERS OEHR_ORDERS_SALES_REP_FK OEHR_EMPLOYEES OEHR_EMP_EMP_ID_PK
3 OEHR_EMPLOYEES OEHR_EMP_DEPT_FK OEHR_DEPARTMENTS OEHR_DEPT_ID_PK

 

Whew!

P.S. If you think a CTE solution is cleaner:

with data( TABLE_NAME, R_TABLE_NAME, path ) as (
  select n.TABLE_NAME, n.R_TABLE_NAME,
    json_array(n.TABLE_NAME, n.CONSTRAINT_NAME, n.R_TABLE_NAME, n.R_CONSTRAINT_NAME)
  from t n
  where table_name = 'OEHR_ORDER_ITEMS'
  union all
  select n.TABLE_NAME, n.R_TABLE_NAME,
    o.path || ',' ||
    json_array(n.TABLE_NAME, n.CONSTRAINT_NAME, n.R_TABLE_NAME, n.R_CONSTRAINT_NAME)
  from t n
  join data o on (n.table_name = o.r_table_name)
    and n.table_name != 'OEHR_DEPARTMENTS'
)
select b.* from data a, json_table(
  '['|| path || ']',
  '$[*]' columns(
    rn for ordinality,
    table_name varchar2(128) path '$[0]',
    constraint_name varchar2(128) path '$[1]',
    r_table_name varchar2(128) path '$[2]',
    r_constraint_name varchar2(128) path '$[3]'
  )
) b
where a.r_table_name = 'OEHR_DEPARTMENTS';

Splitting Strings: a New Champion!

My last post on splitting strings conceded that “a combined SQL + PL/SQL solution beats the best pure SQL solution.” Wrong! I didn’t try JSON_TABLE.

Pretend it’s an array

It’s not hard to reformat a string like

'a,b,c,d,e,"'

to be a JSON array:

select replace(json_array('a,b,c,d,e,"'), ',', '","') jstr
from dual;

JSTR
--------------------------
["a","b","c","d","e","\""]

Notice that the double quote in the data itself is properly escaped by the call to JSON_ARRAY. Then I turn the string into a series of array elements by putting double quotes around each comma. Once all that is done, JSON_TABLE will split it like this:

select subs from json_table(
  replace(json_array('a,b,c,d,e,"'), ',', '","'),
  '$[*]' columns (
    subs varchar2(4000) path '$'
  )
);

SUBS
----
a
b
c
d
e
"

So the escaped data is automatically unescaped. Now for speed, here is my test harness:

set serveroutput on
declare
  l_num number;
  l_timestamp timestamp;
  l_plsql_secs number;
  l_JSON_secs number;
  l_num_substrings number := 10;
  procedure do(p_sql in varchar2) is
  begin
    dbms_output.put_line(p_sql);
    execute immediate p_sql;
  end do;
begin
  select count(*) into l_num from user_tables where table_name = 'T';
  if l_num > 0 then
    do('drop table t purge');
  end if;
  do('create table t(id number, str varchar2(4000)) cache');
  insert into t
  select level, to_char(level,'fm000000000')||',000000002,000000003,000000004,000000005,000000006,000000007,000000008,000000009,000000010'
  from dual
  connect by level <= 10000;
  commit;
  dbms_output.put_line('Substrings' || chr(9) || 'JSON' || chr(9) || 'PL/SQL');
  for i in 1..10 loop
    select count(*) into l_num from t;
     
    l_timestamp := localtimestamp;
    select count(column_value) into l_num from (
      select id, column_value from t, table(string_tokenize(str, ','))
    );
    l_plsql_secs := extract(second from localtimestamp - l_timestamp);
     
    l_timestamp := localtimestamp;
    select count(subs) into l_num from (
      select id, subs from t, JSON_table(
      '["' || replace(str, ',' , '","') || '"]', '$[*]' columns(
      subs varchar2(99) path '$'
      )
    )
    );
    l_JSON_secs := extract(second from localtimestamp - l_timestamp);
    dbms_output.put_line(l_num_substrings || chr(9) || l_JSON_secs || chr(9) || l_plsql_secs);
    update t set str =
    str || ',000000001,000000002,000000003,000000004,000000005,000000006,000000007,000000008,000000009,000000010';
    l_num_substrings := l_num_substrings + 10;
    commit;
  end loop;
end;
/

Notice that I keep the same number of input rows here and increase the number of substrings per row.

PL/SQL does great, but JSON_TABLE appears to beat it.

Splitting Strings: PL/SQL

Marc Bleron and Todd Hershiser gave me some very valuable feedback through their comments on my recent “Splitting Strings” posts. The big news is: PL/SQL beats SQL!

fn:tokenize

XQUERY provides some builtin functions prefixed by “fn”. fn:tokenize is equivalent to ora:tokenize except that it doesn’t break when the delimiter is absent from the input string: it just returns the string. Marc says fn:tokenize is supported, and it does work in 12.1.0.2, but the official documentation says “not supported”. I have asked Marc for more information.

This is not a big deal. With fn:tokenize, there would simply be a bit less typing.

Escaping the delimiter

Todd pointed out that tokenize splits a string based on a regular expression, and some common delimiters (like ‘|’) have special meaning in regular expressions. As a result, I now recommend to simply escape the delimiter with a backslash ‘\’.

PL/SQL

Todd provided a string splitting function in PL/SQL and claimed it is clearly faster than ora:tokenize. He is right!

I wrote a function similar to his and compared it to the “tokenize” solution. Here is the function:

create or replace function string_tokenize(
  p_string in varchar2,
  p_delim in varchar2
)
return sys.odcivarchar2list pipelined
as
  i_prev_pos integer := 1;
  i_pos integer;
  i_max_pos integer := length(p_string) + 1;
  i_delim_length integer := length(p_delim);
begin
  loop
    i_pos := instr(p_string, p_delim, i_prev_pos);
    if i_pos = 0 then
      i_pos := i_max_pos;
    end if;
    pipe row(substr(p_string, i_prev_pos, i_pos - i_prev_pos));
    exit when i_pos = i_max_pos;
    i_prev_pos := i_pos + i_delim_length;
  end loop;
  return;
end string_tokenize;
/

By the way, I tested this function with and without the PRAGMA UDF clause introduced in 12. I found no difference in performance in this case. Here is my final test harness:

set serveroutput on
declare
  l_num number;
  l_timestamp timestamp;
  l_plsql_secs number;
  l_tokenize_secs number;
  l_num_substrings number := 10;
  procedure do(p_sql in varchar2) is
  begin
    dbms_output.put_line(p_sql);
    execute immediate p_sql;
  end do;
begin
  select count(*) into l_num from user_tables where table_name = 'T';
  if l_num > 0 then
    do('drop table t purge');
  end if;
  do('create table t(id number, str varchar2(4000)) cache');
  insert into t
  select level, to_char(level,'fm000000000')||',000000002,000000003,000000004,000000005,000000006,000000007,000000008,000000009,000000010'
  from dual
  connect by level <= 10000;
  commit;
  dbms_output.put_line('Substrings' || chr(9) || 'tokenize' || chr(9) || 'PL/SQL');
  for i in 1..10 loop
    select count(*) into l_num from t;
    
    l_timestamp := localtimestamp;
    select count(column_value) into l_num from (
      select id, column_value from t, table(string_tokenize(str, ','))
    );
    l_plsql_secs := extract(second from localtimestamp - l_timestamp);
    
    l_timestamp := localtimestamp;
    select count(subs) into l_num from (
      select id, subs from t, xmltable(
        'if (contains($X,",")) then ora:tokenize($X,"\,") else $X' 
        passing str as X columns subs varchar2(4000) path '.')
    );
    l_tokenize_secs := extract(second from localtimestamp - l_timestamp);
    dbms_output.put_line(l_num_substrings || chr(9) || l_tokenize_secs || chr(9) || l_plsql_secs);
    update t set str =
    str || ',000000001,000000002,000000003,000000004,000000005,000000006,000000007,000000008,000000009,000000010';
    l_num_substrings := l_num_substrings + 10;
    commit;
  end loop;
end;
/

Notice that I keep the same number of input rows here, whereas in my previous tests I kept the same number of output rows. My “tokenize” solution scales OK, but the PL/SQL function is much faster and scales even better.

In this case a combined SQL + PL/SQL solution beats the best pure SQL solution.

Splitting Strings: Proof!

In my previous post I used XMLTABLE and ora:tokenize to split a comma delimited string. Now I’ll apply that technique to multiple rows, and show that it’s faster than other methods.

Test data

In my tests, I configure the length of the substring, the number of substrings per row and the total number of rows I should get as output. Each input string is unique because it starts with the ID of the row: this way I avoid any caching that might reduce the number of function calls.

drop table t purge;

create table t cache as
with parms as (
  select 9 str_len, 5 num_subs, 100000 num_rows from dual
)
, str_row as (
  select listagg(n,',') within group(order by n) str
  from (
    select lpad(level+1,str_len,'0') n from parms
    connect by level <= num_subs-1
  )
)
select level id,
  lpad(level,str_len,'0') ||','||str str
from parms, str_row
connect by level <= num_rows/num_subs;

select * from t where id <= 11;
ID STR
1 000000001,000000002,000000003,000000004,000000005
2 000000002,000000002,000000003,000000004,000000005
3 000000003,000000002,000000003,000000004,000000005
4 000000004,000000002,000000003,000000004,000000005
5 000000005,000000002,000000003,000000004,000000005
6 000000006,000000002,000000003,000000004,000000005
7 000000007,000000002,000000003,000000004,000000005
8 000000008,000000002,000000003,000000004,000000005
9 000000009,000000002,000000003,000000004,000000005
10 000000010,000000002,000000003,000000004,000000005
11 000000011,000000002,000000003,000000004,000000005

 

Notice the CACHE keyword when I create the table. Before my tests, I access the entire table to make sure it is all in the buffer cache.

The “substr+instr” technique

This is the technique from my “New, Improved IN Lists” post. All I need to do is apply it to multiple rows.

One way to do that is to use the 12c LATERAL() clause. If you are not yet in 12c, try
TABLE(CAST(MULTISET(...) AS SYS.ODCIVARCHAR2LIST))

select a.id, b.subs from t a,
lateral(
  select substr(
    str,
    pos + 1,
    lead(pos,1,4000) over(order by pos) - pos - 1
  ) subs
  from (
    select instr(str, ',', 1, level) pos
    from dual
    connect by
      level <= length(str) - nvl(length(replace(str, ',', '')), 0) + 1
  )
) b;

The “tokenize” technique

This one is easy to adapt to multiple rows:

select id, subs from t, xmltable(
  'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
  passing str as X
  columns subs varchar2(4000) path '.'
);

[Update 2016-08-02: in a comment, Todd Hershiser points out that the second parameter in ora:tokenize is a regex expression. In order to use a regex “metacharacter” like “|” as a delimiter, I need to escape it with a backslash. I decided to put the backslash in everywhere since it doesn’t do any harm.

On the other hand, if the delimiter is ‘&’ then this solution cannot be used.]

The “regexp_substr” technique

This technique is fairly popular, no doubt because it is concise. For multiple rows, I use the “CONNECT BY ... PRIOR SYS_GUID()” technique that I explained in Chunking tables 7: prior sys_guid().

select id, regexp_substr (str, '[^,]+', 1, level) subs
from t
connect by level <= length (regexp_replace (str, '[^,]+')) + 1
and id = prior id
and prior sys_guid() is not null;

Test Results

As you can see here, the “substr+instr” solution is slightly better than “tokenize” when there are fewer than 8 substrings per string. As the number of substrings increases, the “tokenize” solution scales much better. As for the “regexp_substr” solution, it is on average 20 times slower than “tokenize”.

Splitting Strings: Surprise!

In my post New, Improved IN Lists!, I split one string into multiple rows. Now I want to split multiple input strings – but first, I’ve rediscovered an even faster technique!

In this post I’ll introduce the technique, and in the next post I’ll do some testing and comparing.

XMLTABLE with XMLTYPE

  • (Warning: the XML services in the Oracle database have evolved over the last several versions. Some of the solutions here may not work in older versions. Also, prior to version 12c a DBA could decide not to install “XML DB” at all! I have only tested in version 12.1.0.2.)

Quite a few folks have used the XMLTABLE function to solve this problem. One way is to change the string into an XML document: there is one root node that contains one child node per substring.

var txt varchar2(20);
exec :txt := 'A,BB,CCC,DDDD,EEEEE';

select '<r><c>'||replace(:txt, ',', '</c><c>')||'</c></r>' txt
from dual;

<r><c>A</c><c>BB</c><c>CCC</c><c>DDDD</c><c>EEEEE</c></r>

Using the XPATH expression ‘/r/c/text()‘, XMLTABLE will go through the child nodes and produce one row per substring.

select subs from xmltable(
  '/r/c/text()'
  passing xmltype('<r><c>'||replace(:txt, ',', '</c><c>')||'</c></r>')
  columns subs varchar2(4000) path '.'
);
SUBS
A
BB
CCC
DDDD
EEEEE

 

The main drawback of this solution, aside from performance, is that the input string cannot be a full 4000 bytes long.

[Update 2016-08-01: Marc Bleron rightly commented that I could use a CLOB as an input string and have way more that 4000 bytes. The limitation I mention is only true when the input string is a VARCHAR2.]

XMLTABLE with an XQUERY sequence

If I put double quotes (or single quotes) around all the substrings, then the result is an XQUERY sequence. XMLTABLE will simply output that sequence one row per item.

select '"'||replace(:txt, ',', '","')||'"' str 
from dual;

"A","BB","CCC","DDDD","EEEEE"

with data as (
  select '"'||replace(:txt, ',', '","')||'"' str 
  from dual
)
select xmlcast(column_value as varchar2(4000)) subs
from data, xmltable(str);
SUBS
A
BB
CCC
DDDD
EEEEE

Again, this solution breaks if the input string is too long.

Tokenize!

Why doesn’t Oracle just provide a function to split these confounded strings? It does!

Oracle XQuery function ora:tokenize lets you use a regular expression to split the input string … into a sequence of strings.

select subs from xmltable(
  'ora:tokenize($X, "\,")'
  passing :txt as X
  columns subs varchar2(4000) path '.'
);

How simple is that? Well, not as simple as I would like. The nasty Oracle developers have decided that this function should raise an exception if the delimiter is not present in the input string. In other words, ‘A,BB’ is OK but just ‘A’ will produce “ORA-19176: FORX0003: regular expression matches zero-length string”.

Marc Bleron, who has published the ora:tokenize solution, worked around this problem by concatenating an extra comma to the input string. I worked out this alternative that allows for a full 4000 byte VARCHAR2 input string:

select subs from xmltable(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
  passing :txt as X
  columns subs varchar2(4000) path '.'
);

[Update 2016-08-02: in a comment on the next post, Todd Hershiser points out that the second parameter in ora:tokenize is a regex expression. In order to use a regex “metacharacter” like “|” as a delimiter, I need to escape it with a backslash. I decided to put the backslash in everywhere since it doesn’t do any harm.

[Update 2016-08-05: There is a problem with this solution if the ampersand & is involved. You have to convert it to &amp; in the input string and the delimiter.]

What’s Next?

In the next post I’ll compare the performance of these solutions with the substr+instr approach, and with a popular regexp_substr approach.