CLOB vs VARRAY

ddl.sql

create table t_lob_1_mb (id int, c_lob clob);

declare
   c clob;
   n_rows constant int:=100;
   n_len  constant int:=1e6;
begin
   c:=rpad('a',10000,'x');
   loop
     exit when length(c)>=n_len;
     c:=c||c;
   end loop;
   c:=substr(c,1,n_len);
   dbms_output.put_line(length(c));
   insert into t_lob_1_mb
      select level, c from dual connect by level<=n_rows;
   commit;
end;
/
create or replace function lob_to_varray(c clob)
  return sys.odcivarchar2list 
as
  res sys.odcivarchar2list;
  pragma UDF;
begin
  select substr(c,(level-1)*4000 + 1,4000)
     bulk collect into res
  from dual
  connect by level<=ceil(length(c)/4000);
  return res;
end;
/

output1.sql

SQL> set lobprefetch 32767
SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set timing on;
SQL> set arraysize 1000;
SQL> --set feedback only
SQL> set autotrace trace stat;
SQL> select id,c_lob from t_lob_1_mb where id<=25;

25 rows selected.

Elapsed: 00:01:15.93

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         57  consistent gets
       6450  physical reads
          0  redo size
   51645547  bytes sent via SQL*Net to client
       7010  bytes received via SQL*Net from client
         27  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         25  rows processed

SQL> select id,lob_to_varray(c_lob) c_varray from t_lob_1_mb where id<=25;

25 rows selected.

Elapsed: 00:00:30.90

Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
        176  consistent gets
     653950  physical reads
          0  redo size
   25102063  bytes sent via SQL*Net to client
       7983  bytes received via SQL*Net from client
         58  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
         25  rows processed

SQL>
SQL> select
  2    c_varray
  3  from t_lob_1_mb
  4       outer apply (
  5         select
  6           cast(
  7             collect(
  8                cast(substr(c_lob,(level-1)*4000 + 1,4000) as varchar2(4000))
  9                )
 10             as sys.odcivarchar2list
 11           ) c_varray
 12         from dual
 13         connect by level<=ceil(length(c_lob)/4000)
 14       )
 15  where id<=25;

25 rows selected.

Elapsed: 00:00:28.36

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        216  consistent gets
     653950  physical reads
          0  redo size
   25101932  bytes sent via SQL*Net to client
       8258  bytes received via SQL*Net from client
         58  SQL*Net roundtrips to/from client
         50  sorts (memory)
          0  sorts (disk)
         25  rows processed

SQL>
SQL> set autot off echo off;

test1.sql

set echo on;
set lobprefetch 32767
set long 10000000
set longchunksize 10000000
set timing on;
set arraysize 1000;
--set feedback only 
set autotrace trace stat;
select id,c_lob from t_lob_1_mb where id<=25;
select id,lob_to_varray(c_lob) c_varray from t_lob_1_mb where id<=25;

select 
  c_varray
from t_lob_1_mb 
     outer apply (
       select
         cast(
           collect(
              cast(substr(c_lob,(level-1)*4000 + 1,4000) as varchar2(4000))
              )
           as sys.odcivarchar2list
         ) c_varray
       from dual
       connect by level<=ceil(length(c_lob)/4000)
     )
where id<=25;

set autot off echo off;