在Oracle中，可以将以逗号分隔的非常大的字符串(clob)转换为具有更好性能的表 [英] In Oracle, is it possible to convert very large string (clob) separated by comma in to table with better performance

问题描述

我需要使用逗号定界符将非常大的Clob字符串转换为表.

I need to convert very large clob string in to table by comma delimiter.

以下功能需要很长时间.有没有返回表的快速函数.

Below function takes very long time. is there any fast function which returns table.

create or replace 
FUNCTION        UDF_STRSPLIT2 (
   P_STR     IN CLOB,
   P_DELIM   IN VARCHAR2 DEFAULT ';' ,
   P_LIKE    IN INT DEFAULT 0
)
   RETURN MYTABLETYPE
AS
   L_STR    CLOB DEFAULT P_STR || P_DELIM ;
   L_N      NUMBER;
   L_DATA   MYTABLETYPE := MYTABLETYPE ();
BEGIN
   LOOP
      L_N := INSTR (L_STR, P_DELIM);
      EXIT WHEN (NVL (L_N, 0) = 0);
      L_DATA.EXTEND;
      L_DATA (L_DATA.COUNT) :=
         LTRIM (RTRIM (SUBSTR (L_STR, 1, L_N - 1)))
         || CASE WHEN P_LIKE = 0 THEN '' ELSE '%' END;
      L_STR := SUBSTR (L_STR, L_N + LENGTH (P_DELIM));
   END LOOP;

   RETURN L_DATA;
END;

推荐答案

您可以使用DBMS_LOB(而不是常规的instr/substr)将其加速一个数量级:

You can speed this one up by an order of magnitude with DBMS_LOB instead of regular instr/substr:

CREATE OR REPLACE FUNCTION DROPME$STRSPLIT2 (
   P_STR     IN CLOB,
   P_DELIM   IN VARCHAR2 DEFAULT ';' ,
   P_LIKE    IN INT DEFAULT 0
)
   RETURN SYS.ODCIVARCHAR2LIST
AS
  L_DATA SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST ();
  L_STR  CLOB := P_STR || P_DELIM;  
  L_SUBSTR VARCHAR2(4000);
  L_STEP PLS_INTEGER := 0;
  L_THIS INT := 1;
  L_PREV INT := 0;
  L_END CHAR := CASE P_LIKE WHEN 0 THEN NULL ELSE '%' END;
BEGIN  
  LOOP
    L_STEP := L_STEP + 1;
    L_THIS := DBMS_LOB.INSTR(L_STR, P_DELIM, L_PREV + 1, 1);    
    EXIT WHEN L_THIS = 0;
    L_SUBSTR := 
    TRIM(
      DBMS_LOB.SUBSTR(
        L_STR, 
        L_THIS - L_PREV - 1,
        L_PREV + 1
      )
    );        
    L_PREV := L_THIS;    
    L_DATA.EXTEND();
    L_DATA(L_STEP) := L_SUBSTR || L_END;
  END LOOP;
  RETURN L_DATA;
END;

测试用例，处理60kb CLOB 10次:

Test case, process 60kb CLOB 10 times:

您的功能:

18:15:50 SQL> l
  1  DECLARE
  2    VAL CLOB;
  3    RESULT SYS.ODCIVARCHAR2LIST;
  4  BEGIN
  5    SELECT C INTO VAL FROM DROPME$C;
  6    FOR I IN 1 .. 10 LOOP
  7      RESULT := DROPME$STRSPLIT1(VAL);
  8    END LOOP;
  9* END;
18:15:54 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.56

升级功能:

18:17:12 SQL> l
  1  DECLARE
  2    VAL CLOB;
  3    RESULT SYS.ODCIVARCHAR2LIST;
  4  BEGIN
  5    SELECT C INTO VAL FROM DROPME$C;
  6    FOR I IN 1 .. 10 LOOP
  7      RESULT := DROPME$STRSPLIT2(VAL);
  8    END LOOP;
  9* END;
18:17:14 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87

已更新.确保它们提供相同的输出:

Upd. to make sure they provide the same output:

18:20:08 SQL> l
  1  SELECT * FROM TABLE(
  2    DROPME$STRSPLIT1('a;b;c;d;f')
  3* )
18:20:10 SQL> /

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

并升级

18:20:16 SQL> l
  1  SELECT * FROM TABLE(
  2    DROPME$STRSPLIT2('a;b;c;d;f')
  3* )
18:20:20 SQL> /

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

这篇关于在Oracle中，可以将以逗号分隔的非常大的字符串(clob)转换为具有更好性能的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！