在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屋！