Hello colleagues,
I've got a "small" problem, I have to copy millions of blobs from one table to another one.
Target table:
PAGE
----------------------
ID NOT NULL NUMBER(19)
DOCUPLOADCODE VARCHAR2(255)
MIMETYPE NOT NULL VARCHAR2(255)
THUMBNAILMIMETYPE VARCHAR2(255)
VERSION NUMBER(19)
SCANPAGEMETADATA_ID NUMBER(19)
PAGEFILE BLOBTHUMBNAIL BLOBPAGEFILE_CDBREF NUMBERTHUMBNAIL_CDBREF NUMBERThe columns
PAGEFILE_CDBREF and
THUMBNAIL_CDBREF are referencies to the
source table and the columns
PAGEFILE and
THUMBNAIL contains the BLOBs.
Source table looks like this:
ODMM_CONTENTSTORE
----------------------
ID NOT NULL NUMBER(20)LOCKEDFORSESSION NUMBER(20)
MEDIAID NUMBER(20)
LASTACCESSDATE NUMBER(20)
CONTENTTYPE NUMBER(10)
HSMLOCATION VARCHAR2(2000)
BFILECONTENT BINARY FILE LOB
GLOBALINDEXEDBLOB BLOB
EMAILINDEXEDBLOB BLOB
EMAILINDEXEDBLOB_T BLOB
INTERMEDIABLOB BLOBINTERMEDIABLOB_T BLOB
ID is the reference for
PAGEFILE_CDBREF and
THUMBNAIL_CDBREF and
INTERMEDIABLOB is the data for
PAGEFILE and
THUMBNAIL.
The update looks like this:
update
page dst
set
thumbnail = (select intermediablob from odmm_contentstore src1 where src1.id = dst.thumbnail_cdbref)
, pagefile = (select intermediablob from odmm_contentstore src2 where src2.id = dst.pagefile_cdbref);
The table page contains 17 Mio. rows and all of them have to be updated.
Any ideas how I can accelerate this?
Kind regards,
Heinrich