Wie man am besten einen LOB kopiert
Das Grundproblem
Wir müssen eine Tabelle, die einen LOB enthält, umkopieren. Das kann unterschiedliche Gründe haben. Beispielsweise wenn die Tabelle zu fragmentiert ist, oder wenn auf dem aktuellen Tablespace kein Platz mehr ist.
Der gradlinige Ansatz mit Insert /*+ append parallel(n) enable_parallel_dml */ die Daten einfach umzukopieren scheitert aber. Es dauert zunächst sehr lange. Man kann erkennen, dass die Zeit beim Einfügen und nicht beim Lesen anfällt. Hier wiederum ist das Kopieren der LOBS entscheidend. Der Rest der Zeit fällt vergleichsweise kaum ins Gewicht.
Noch ein Problem hat der Insert .. Select Ansatz: Es gibt keine Teilergebnisse. Entweder es geht alles gut, oder man muss wieder von vorne anfangen.
Beim Suchen im Internet fand ich einen Blog von Marek Läll, der sich mit dem Thema LOB kopieren befasst [1].
Der Kern der Sache ist, dass der LOB Locator (LOB Adresse) zum Zeitpunkt des Inserts noch nicht feststeht. Es muss ein neuer LOB angelegt werden, das heißt man braucht auch einen neuen Locator. In der Folge wird der zu kopierende LOB zunächst in den TEMP Tablespace geschrieben wird. Dann wird der neue Locator ermittelt und dann wird der LOB vom TEMP Tablespace noch einmal in das eigentliche Ziel kopiert. Läll schlägt jetzt einen anderen Weg vor: Es wird zunächst ein empty LOB geschrieben, dessen Locator wird dann über SELECT .. FOR UPDATE gelesen. Dorthin kann der Quell LOB in einem Zug kopiert werden. Dadurch erspart man sich einmal Schreiben und Lesen des LOB, was wichtig ist. Wie schon erwähnt zählt für die Effizienz vor allem wie die LOBs behandelt werden.
Ich habe den Code in PL/SQL realisiert und noch etwas optimiert, also auf BULK umgeschrieben und den SELECT .. FOR UPDATE über die returning Klausel vermieden.
Der Code ist sehr effizient verglichen mit anderen Ansätzen, die ich getestet habe. In meinem echten Anwendungsfall komme ich locker bei parallel 24 auf 1,5 Millionen Rows/Stunde.
Kopieren
Um das folgende Beispiel verständlich zu machen, erstelle ich einmal die folgende einfache Tabelle, die kopiert werden soll:
CREATE table doc_table( doc_id Number,
document BLOB);
Die Tabelle hat eine BLOB Spalte namens document.
DECLARE
TYPE t_BLOB_tab IS TABLE OF BLOB;
v_blob t_BLOB_tab;
v_blob_length NUMBER;
CURSOR c1 is
SELECT /*+ noparallel */ doc_id , document -- 1: noparallel hint
FROM doc_table
WHERE ROWID BETWEEN :start_id AND :end_id; -- 2: Start and End Rowid
TYPE c1_tab IS TABLE OF c1%rowtype;
v_c1_tab c1_tab;
c_limit PLS_INTEGER := 10000;
BEGIN
OPEN c1;
LOOP
FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
EXIT WHEN v_c1_tab.COUNT=0;
FORALL i IN 1 .. v_c1_tab.COUNT
INSERT INTO doc_table_new (doc_id , document P) -- 3: Conventional Insert
VALUES (v_c1_tab(i).doc_id, empty_blob())
RETURNING document BULK COLLECT INTO v_blob; -- 4: Loblocator of new LOB
FOR i IN 1 .. v_c1_tab.COUNT
LOOP
v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
IF nvl(v_blob_length,0) > 0 THEN -- 5: DBMS_LOB.COPY will throw an exception
DBMS_LOB.COPY(v_blob(i), -- for empty LOBS
v_c1_tab(i).document,
v_blob_length);
END IF;
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END;
/
Zu den Kommentaren:
- Der anonymous Block wird im nächsten Schritt über DBMS_PARALLEL_EXECUTE parallelisiert. Es wäre unlogisch innerhalb eines parallelen Prozesses noch einmal zu parallelisieren.
- Die Start – und die End id müssen im zentralen Select verwendet werden. Man darf sie nicht definieren, sie werden über DBMS_PARALLEL_EXECUTE gesetzt.
- Hier wäre über den Hint APPEND_VALUES ein direct path write möglich. Ich habe davon abgesehen, um nicht einen exclusive table lock zu provozieren. Ob dies tatsächlich der Fall wäre, habe ich nicht getestet. Ich bin mit der Perfomance der hier beschrieben Lösung so zufrieden, dass ich einen Test für unnötig erachte.
- Das Returning erspart einen SELECT .. FOR UPDATE.
- COPY ist die schnellste Art zu kopieren und scheint einen direct path zu verwenden.
Parallelisieren
Hier hätte man auch über paralleles SQL eine stored function rufen können, um die Lösung zu parallelisieren. Die Entscheidung für DBMS_PARALLEL_EXECUTE war eher instinktiv begründet. Jedoch wäre es bei einer stored Function zu sehr vielen context Switches gekommen. DBMS_PARALLEL_EXECUTE erlaubt es in PL/SQL zu bleiben.
Einige Tests mit parallelem SQL waren in der Tat auch wenig effizient.
Hier noch der Code zum Parallelisieren, den anonymen Block, den wir besprochen haben, habe ich markiert.
DECLARE
l_sql_stmt CONSTANT VARCHAR2 ( 20000 ) :=
q'[DECLARE
TYPE t_BLOB_tab IS TABLE OF BLOB;
v_blob t_BLOB_tab;
v_blob_length NUMBER;
CURSOR c1 is
SELECT /*+ noparallel */ doc_id , document
FROM doc_table
WHERE ROWID BETWEEN :start_id AND :end_id;
TYPE c1_tab IS TABLE OF c1%rowtype;
v_c1_tab c1_tab;
c_limit PLS_INTEGER := 10000;
BEGIN
OPEN c1;
LOOP
FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
EXIT WHEN v_c1_tab.COUNT=0;
FORALL i IN 1 .. v_c1_tab.COUNT
INSERT INTO doc_table (doc_id , document)
VALUES (v_c1_tab(i)."doc_id", empty_blob())
RETURNING document BULK COLLECT INTO v_blob;
FOR i IN 1 .. v_c1_tab.COUNT
LOOP
v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
IF nvl(v_blob_length,0) > 0 THEN
DBMS_LOB.COPY(v_blob(i),
v_c1_tab(i).document,
v_blob_length);
END IF;
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END; ]';
l_chunk_sql CONSTANT VARCHAR2 ( 10000 ) := -- 1: chunking statement. Breaks the input data
q'[SELECT min(r) start_id, max(r) end_id -- into equal size pieces
FROM (
SELECT ntile(10) over (order by rowid) grp, rowid r –- 2: 10 chunks will be produced
FROM doc_table -- this can be equal or a multiple
) -- of the parallel_level
GROUP BY grp]';
l_try INTEGER;
l_status INTEGER;
l_task_name CONSTANT VARCHAR2( 20 ) := 'BLOB_MOVE';
BEGIN
BEGIN
dbms_parallel_execute.drop_task( l_task_name );
EXCEPTION
WHEN others then
null;
END;
dbms_parallel_execute.create_task( l_task_name );
dbms_parallel_execute.create_chunks_by_sql(l_task_name, l_chunk_sql, true);
dbms_parallel_execute.run_task(
task_name => l_task_name,
sql_stmt => l_sql_stmt,
language_flag => dbms_sql.native,
parallel_level => 10 –- 3: that many processes will be generated
);
dbms_output.put_line( 'DONE..' || dbms_parallel_execute.task_status(l_task_name));
END;
/
Zu den Kommentaren:
- Es gibt mehrere Möglichkeiten die Arbeit aufzuteilen. Die Chunking Query ist die flexibelste. Die Suchbedingen hier müssen auch im Cursor c1 noch einmal zu finden sein.
- Ntile (10) heißt, dass das Ergebnis der Abfrage in 10 hoffentlich gleich große Abschnitte einteilt.
Quellen:
- Marek Läll, binary/textual data insert into BLOB/CLOB columns – use 50% less database (IO) resources, Marek Läll / Technical blog about Oracle database: binary/textual data insert into BLOB/CLOB columns – use 50% less database (IO) resources (mareklall.blogspot.com)
wurden hier nacheinander die vier Audit-Spalten (CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON) hinzugefügt und die Spalteneigenschaften definiert.
und „Kopierte Spalten am Ende einfügen“
. Hiermit werden die ganzen Spalten samt ihrer vollständigen Definition kopiert und in der Ziel-Tabelle eingefügt.
