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

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_at_iinet.net.au

--
http://www.freelists.org/webpage/oracle-l