Beiträge

How to copy LOBs

The Issue

Assume we must copy a table that contains a LOB. There can be different reasons for this. For example, if the table is too fragmented or if there is no more space in the current tablespace.

The straightforward approach with Insert / * + append parallel (n) enable_parallel_dml * / simply to copy the data fails, however. It takes a long time at first. You can see that the time is spent inserting and not reading. How you copy the LOBS is crucial. The rest of the time is comparatively negligible.

The Insert .. Select approach has another problem: There are no partial results. Either you succeed or you have to start all over again.

Searching the Internet I found a blog by Marek Läll that deals with the subject of copying LOBs [1].

The core of the matter is that the LOB locator (LOB address) has not yet been determined at the time of the insert. A new LOB must be created, which means you also need a new locator. As a result, the LOB to be copied is first written to the TEMP tablespace. Then the new locator is determined and then the LOB is copied again from the TEMP tablespace to the actual target. Läll now suggests another way: First an empty LOB is written, its locator is then read via SELECT .. FOR UPDATE. The source LOB can be copied there in one go. This saves you from writing and reading the LOB, which is important. As already mentioned, the most important thing for efficiency is how the LOBs are treated.

I implemented the code in PL / SQL and optimized it a bit, rewritten it to BULK and avoided the SELECT .. FOR UPDATE via the returning clause.

The code is very efficient compared to other approaches I tested. In my real application, I can easily get to 1.5 million rows / hour with a parallel 24.

Copy Part

To illustrate my code let us assume this simple table:

CREATE table doc_table( doc_id Number,
                        document BLOB); 

The table has a BLOB column called 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;
/

Annotations:

1.       The anonymous block is parallelized in the next step via DBMS_PARALLEL_EXECUTE. It would be illogical to parallelize again within a parallel process.

2.       The start and end id must be used in the central select. They must not be declared, they are set from the DBMS_PARALLEL_EXECUTE framework.

3.       A direct path write would be possible here using the APPEND_VALUES hint. I refrained from it so as not to provoke an exclusive table lock. I have not tested whether this actually would be the case. I am so satisfied with the performance of the solution described here that I consider a test to be unnecessary.

4.       The return saves a SELECT .. FOR UPDATE.

5.       DBMS_LOB.COPY is the fastest way to copy and seems to use a direct path.

Parallelize

I could have called a stored function via parallel SQL in order to parallelize the approach. The decision to use DBMS_PARALLEL_EXECUTE was rather by instinct. There are some objective points for PL/SQL parallelization, however. E.g. a stored function would have resulted in many context switches. DBMS_PARALLEL_EXECUTE allows you to stay in PL / SQL. In addition, the code below will also work with Standard Edition.

I also did some tests with parallel SQL and functions, but never got them to scale.  I would not exclude that there is a better approach than the one I present here. However, compared to the alternatives I have seen I rather like the approach presented below.

Here is the code for parallelization, I highlighted the anonymous block that we discussed in the previous paragraph.

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;
/

Annotations:

  1. There are several ways to divide the work. The chunking query is the most flexible. The search conditions here must also be found again in cursor c1.
  2. Ntile (10) means that the result of the query will hopefully be divided into 10 equal sections.

 

References:

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:

  1. 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.
  2. 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.
  3. 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.
  4. Das Returning erspart einen SELECT .. FOR UPDATE.
  5. 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:

  1. 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.
  2. Ntile (10) heißt, dass das Ergebnis der Abfrage in 10 hoffentlich gleich große Abschnitte einteilt.

Quellen:

Audit-Spalten mit dem Oracle SQL Developer Data Modeler automatisieren

In vielen Projekten erlebe ich es, dass Entwickler Audit-Spalten verwenden um entsprechende Informationen leicht zugänglich zu haben: Wer hat eine Datenzeile wann angelegt – und wann wurde dieselbe Zeile zuletzt von wem wieder bearbeitet? Wurden Tabellen früher noch direkt per DDL-Script erzeugt (CREATE TABLE my_test (id NUMBER NOT NULL, etc.), kommen hier heutzutage eher Modellierungstools wie der Oracle SQL Developer Data Modeler zum Einsatz:

 

Über das Icon “Spalte am Ende der Liste hinzufügen“ plus wurden hier nacheinander die vier Audit-Spalten (CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON) hinzugefügt und die Spalteneigenschaften definiert.

Die wenigsten Projekte bestehen aber aus nur einer einzigen Tabelle – bei drei oder sieben Tabellen mag das manuelle Hinzufügen der einzelnen Spalten und die ebenfalls manuelle Konfiguration derselben ja noch funktionieren. Aber was, wenn der Entwickler zwei oder drei Dutzend Tabellen modellieren will?

Q: Geht das nicht einfacher?

A: Natürlich geht das – über die Icons „Ausgewählte Spalten kopieren“  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.

Allerdings stößt auch diese Variante ab einer gewissen Tabellenanzahl an ihre Grenzen. Bei mehreren Hundert Tabellen werden wahrscheinlich die wenigsten Entwickler die Geduld aufbringen, bei jeder Tabelle die Icons drücken zu wollen. Hoffnungslos aufwändig wird es, wenn dann nicht neue Spalten hinzugefügt werden sollen – sondern beispielsweise der Datentyp bereits definierter Spalten geändert werden soll, etwa weil [CREATED|MODIFIED]_BY künftig bis zu 100 Zeichen speichern können soll.

Q: Geht das nicht einfacher?

A: Natürlich geht das – viel einfacher sogar. 😉

Hier kommen im Data Modeler die sogenannten Transformationen ins Spiel – im Menü unter Extras > Entwurfsregeln und Transformationen > Transformationen zu finden:

Speziell die Transformation mit dem Namen „Table template – uses column name“ ist hier sehr nützlich:

Um die Wartung von Audit-Spalten zu automatisieren, muss nun eine Tabelle angelegt werden, die nur die gewünschten Audit-Spalten enthält und deren Name in der Transformation referenziert wird, im Beispiel also MY_JOURNAL_TEMPLATE:

Beim Anwenden der Transformation werden dann alle im Model vorhandenen Tabellen durchgelooped und bezüglich der Spalten an die Definition in der Template-Tabelle angepasst. Gibt es die Audit-Spalten noch nicht, werden sie neu hinzugefügt. Gibt es sie bereits, werden ihre Attribute bei Bedarf auf die Werte der Template-Tabelle aktualisiert. So ist es dann beispielsweise auch möglich mit wenigen Klicks eine Datentyp-Änderung für ein Projekt mit mehreren Hundert Tabellen auszuführen. Um zu überprüfen ob eine Spalte aus einem Template stammt, kann man sich in den „Spalteneigenschaften“ die „Dynamischen Eigenschaften“ anschauen. Wenn die Spalte aus einem Template heraus erstellt oder angepasst wurde, wird im Rahmen der Transformation der unter p_name angegebene Wert (in diesem Beispiel also ctemplateID) nämlich als Schlüssel in die Eigenschaften eingetragen.