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:

0 Kommentare

Dein Kommentar

An Diskussion beteiligen?
Hinterlasse uns Deinen Kommentar!

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.