split CLOB-Feld in die Tabelle Struktur

Habe ich Feld in einer Oracle-Datenbank vom Typ CLOB. Ich möchte split dieses Feld in mehrere Spalten und Zeilen. Hier ist ein Beispiel für den Inhalt:

4:true5:false24:<p>option sample 1.</p>4:true22:<p>option sample 2.</p>5:false23:<p>option sample 3.</p>5:false22:<p>option sample 4.</p>5:false

Das Ergebnis sollte wie folgt Aussehen:

- ID-EBENE ANSWER_OPTION WERT 
1 3 option Beispiel 3 falsch 
1 4 option Probe 4 false 
2 3 option Beispiel 3 falsch 
4 3 option Beispiel 3 wahr 
3 2 option Beispiel 2 falsch 
1 2 option " Beispiel 2 wahr 
2 1 option Beispiel 1 wahr 
2 4 option Probe 4 false 
4 die option " 1 Beispiel 1 falsch 
2 2 option Beispiel 2 falsch 
4 option 2-Beispiel 2 falsch 
1 1 option Beispiel 1 falsch 
3 4 die option Probe 4 false 
4 4 option Probe 4 false 
3 3 option Beispiel 3 falsch 
3 1 option Beispiel 1 wahr

Wir haben die folgenden Anweisung erstellt, die das Ergebnis oben.

with guest_string as 

(    select qsn.id                                id 

     ,      dbms_lob.substr( qsn.guest, 2000, 1 ) answer_options

     from   mneme_question qsn

     where qsn.id < 10 

)

select distinct id
,      level
,      substr(regexp_substr( answer_options'<p>[^<]+', 1, level, 'i'), 4) ANSWER_OPTION
,      substr(regexp_substr( answer_options, '(true|false)', regexp_instr( answer_options, '</p>', 1,  1), level, 'i'), 1) VALUE
from   guest_string
connect by regexp_substr( answer_options, '<p>[^<]+', 1, level, 'i') is not null

Das problem mit diesem code ist es dauert viel zu lange, um die split-alle Aufzeichnungen, die wir haben. Wir hatten es abgeschnitten auf 10 Zeilen (5 Zeilen nehmen, 0,25 sec, 10 dauert 16 Sekunden, 15 Zeilen dauert etwa über 2,5 Minuten). Wir haben derzeit 30000 Zeilen, und Sie werden wachsen. Im moment können wir nicht ändern, die software zu ändern, das datamodel, so werden wir zu tun haben, diese ad hoc.

Unserer aktuellen Ansatz ist das erstellen einer Prozedur, die aufgerufen wird, für jeden Datensatz, aber es wäre besser, um eine schnellere Analyse. Hat jemand einen Vorschlag, wie Sie ein Skript erstellen, können Sie dies in einer angemessenen Zeit. Wir können dies tun in der Nacht, aber vorzugsweise sollte es nicht länger dauern als 2 Minuten.

BTW wir in der Zukunft bauen könnte, irgendeine Art von Mechanismus, um zu bestimmen, welche Datensätze bereits analysiert, aber auch die erfordert irgendeine form der Aufdeckung von bereits geparsten Felder, die sich geändert haben in der Zwischenzeit. Wir haben keine Zeit zu tun, noch, so jetzt müssen wir rohe Analyse so schnell wie möglich.

Dank

InformationsquelleAutor Sigur | 2012-03-12

oracle10g sql

Hier ist ein anderer Ansatz.

Kann der code nicht so schön, wie Sie können, und Sie werden wahrscheinlich haben, um zu beheben einige kleine Dinge, die...

Ich habe es auf 11g (konnte Sie nicht finden, 10g) und verwendet Ihre Eingabe als Werte in meinem clob-Spalte.

Für 10 Zeilen (die alle über den gleichen Eingang wie Sie gab als Beispiel),

original Abfrage: 9.8 Sek.

neue Abfrage: 0,08 Sekunden

Habe ich eine weitergeleitete-Funktion, hier ist der code:

create or replace type t_parse is object(idd number, levell number, answer_option varchar2(128), valuee varchar2(4000));
/
create or replace type tab_parse is table of t_parse;
/
create or replace function split_answers return tab_parse
  pipelined is
  cursor c is
    select * from mneme_question;

  str_t   clob;
  phraseP varchar2(128);
  phraseV varchar2(8);
  i1s     number;
  i1e     number;
  i2s     number;
  levell  number;

begin
  for r in c loop
    str_t  := r.guest;
    levell := 1;
    while str_t is not null loop
      i1s := dbms_lob.instr(str_t, '<p>', 1, 1) + 3;
      if i1s = 3 then
        str_t := '';
      else

        i1e     := dbms_lob.instr(str_t, '</p>', 1, 1);
        phraseP := dbms_lob.substr(str_t, i1e - i1s, i1s);
        str_t   := dbms_lob.substr(str_t, offset => i1e + 4);

        i2s := dbms_lob.instr(str_t, 'true', 1, 1) ;
        if i2s = 0 then

          i2s := dbms_lob.instr(str_t, 'false', 1, 1) ;
          if i2s = 0 then
            str_t := '';
          else
            phraseV := dbms_lob.substr(str_t, 5, i2s);
            pipe row(t_parse(r.id, levell, phraseP, phraseV));
            levell := levell + 1;
          end if;
        else
          phraseV := dbms_lob.substr(str_t, 4, i2s);

          pipe row(t_parse(r.id, levell, phraseP, phraseV));
          levell := levell + 1;
        end if;
      end if;
    end loop;

  end loop;
  return;
end split_answers;
/

neue Abfrage sollte wie:

select * from table(split_answers);

Vielen Dank für die Bereitstellung einer alternativen Lösung. Wir machen Fortschritte mit der patttern der Aufruf einer Prozedur. Wir werden versuchen, Ihre Lösung als gut. Wir haben auch kommen mit einer verbesserten version des Codes.
Dies ist die verbesserte version:

with rn as (select rownum rn from dual connect by rownum < 10) select substr(regexp_substr(qsn.guest, '<p>[^<]+', 1, rn, 'i'), 4) ANSWER_OPTION, substr(regexp_substr(qsn.guest, '(true|false)', regexp_instr(qsn.guest, '</p>', 1,  1), rn, 'i'), 1) VALUE from mneme_question qsn cross join rn where substr(regexp_substr(qsn.guest, '<p>[^<]+', 1, rn, 'i'), 4) is not null

Es muss noch testen, aber es war schneller als das original
ok @BetaUser, dies sind meine Ergebnisse: für eine Tabelle mit einer clob-Spalte und die 20 Datensätze mit dem gleichen Wert (den von deinem post) hab ich: original qry: 342.219 ; Ihre neue qry: 0.375 ; mein qry: 0.11 , aber es ist eine seltsame Sache - die ursprüngliche und eigene Abfragen zurückgegeben, 80-Aufzeichnungen, aber Ihre neue zurückgegeben 380
Ok, vielen Dank für deine Hilfe. Leistung hat gegangen Weg. Ich denke, wir schaffen es von hier aus. Ich werde markieren Sie Ihr post als Antwort. Dank

InformationsquelleAutor A.B.Cade

Gibt es eine Funktion flüstern Rückkehr Pipeline Tabelle:

CREATE OR REPLACE FUNCTION split_clob(p_clob      IN CLOB DEFAULT NULL,
                                      p_varchar   IN VARCHAR2 DEFAULT NULL,
                                      p_separator IN VARCHAR2)
  RETURN varchar_set
  PIPELINED IS
  l_clob CLOB;
BEGIN
  l_clob := nvl(p_clob,
                to_clob(p_varchar));

  FOR rec IN (

                WITH vals AS
                 (SELECT CAST(TRIM(regexp_substr(l_clob,
                                                 '[^'||p_separator||']+',
                                                 1,
                                                 levels.column_value)) AS
                              VARCHAR2(320)) AS val
                    FROM TABLE(CAST(MULTISET
                                    (SELECT LEVEL
                                       FROM dual
                                     CONNECT BY LEVEL <=
                                                length(regexp_replace(l_clob,
                                                                      '[^'||p_separator||']+')) + 1) AS
                                    sys.odcinumberlist)) levels)
                SELECT val FROM vals)
  LOOP
    PIPE ROW(rec.val);
  END LOOP;
  RETURN;
END;

U können es verwenden, wie diese:

-mit-Tisch mit clob

  SELECT t2.column_value FROM my_table t, TABLE(split_clob(p_clob => t.clob_column,p_separator => ',')) t2

-oder varchar zu

SELECT * FROM TABLE(split_clob(p_varchar => '1,2,3,4,5,6, 7, 8, 9', p_separator => ','))

InformationsquelleAutor Michal Hože

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.