Re: Conversion from LONG -> CLOB -> BLOB for 1 TB Table?

  • From: Nuno Souto <dbvision@xxxxxxxxxxxx>
  • Date: Wed, 17 Jan 2007 22:52:07 +1100

VIVEK_SHARMA wrote,on my timestamp of 17/01/2007 8:46 PM:


What is the ideal path to convert this LONG field to BLOB Datatype in Oracle ver 9.2 & 10gR2?



you should be able to use PL/SQL directly from LONG to BLOB
in both releases.  DBMS_LOB is your friend, plus the extended
LOB functionality of some of the native SQL functions themselves.

The latter is more important and useful with 10gr2: a bit
primitive with 9i.


Is Conversion from LONG to the intermediate CLOB & CLOB onwards to BLOB necessarily needed?


I don't think you want to go LONG->CLOB->BLOB at all:
CLOB is for characters and I believe you have jpgs?
If so, go directly LONG->BLOB: much faster and you won't
lose data.


What might be the performance issues faced as Table size is 1 TB?


Highly contingent on your storage architecture and how
you define the table with the LOB.  You can set it to
have LOBs smaller than 4K stored inline with other table
data and larger LOBs "out-of-line" in a "hidden" table
which you can allocate to a specific tablespace - and
therefore a specific datafile(s) and file system(s).

Note that ANY LOB stored "out-of-line" will go into
its own separate block.  If your block size is 8K
but the vast majority of your LOBs are 5K, you'll
be storing one LOB/block and wasting 2K of it. Give
or take a few dozen bytes for meta data: headers, that
sort of stuff.

You might need to consider a smaller block size, IF a
large percentage of your LOBs is slightly over the 4K
"jump to offline" boundary and nowhere near the 8K block
size.  By this of course I mean: test the smaller block
size with real data.  Start at 2K?

The "out-of-line" tablespace will also have REDO images
for the LOBs as these are not stored in the normal REDO
tablespace.  So make allowances if you are doing lots
of updates.

Performance can be quite bad if it is high update
rate data with lots of size changes.  If it is just
INSERT and SELECT then it can be quite reasonable.

Of course: don't expect OLTP levels.  But if you are
using LONG you already know that!

One thing: do NOT use ASSM tablespaces for LOB.
Not yet.  Soon.  Check the listed bug fixes for
the latest 9i and 10g patch releases.

DBMS_METADATA.GET_DDL is your friend when testing:
it'll show you all the proper syntax, options chosen
and defaults taken.  There are many!


Any Docs Links will help


Oracle's own docs are the best source, IMHO.
The "Supplied PL/SQL Packages" manual is quite
good for DBMS_LOB, in particular.

Asktom has lots of goodness as well.
And of course Metalick is always a good source
but needs lots of time to find where things are
hiding.

HTH

Cheers
Nuno Souto
dbvision@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l


Other related posts: