Migrating LONGs to LOBs

[MikeJones]

Hi everyone,

I'm currently in the process of migrating all our LONG data to the appropriate LOB. This process is very slow though...

This is the method I am using...

Create a table of the tables PK + the correct LOB

Insert all the data from the original table using TO_LOB on the LONG column.

Drop the LONG of the original table

Re-create it as the correct LOB

do an UPDATE statement to get the info back from the temp table to the original.

My problem is that this last statement can be very VERY slow, I'm talking over an hour to migrate 55K rows, the table is only a few meg in size.

Does anyone have any ideas? I've seen the Oracle documentation on LOB's mention things about deep copies, I think I would like copy just the LOB locator back to the original table on the update statement, but then when I drop the temp table would I drop the actual LOB? I'm finding this all very confusing and don't know where to start!

Help!! Please!

Mike.

 

[MikeJones]

I've found the solution, it was nothing to do with LOBs in the end.

 

[MikeLacey]

I'm interested -- what was the problem then? (and the solution) Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[MikeJones]

This is embarressing...

Well as I said above it was the update statement which moved the data back which was the problem...

Basically it looked like this...

UPDATE orig_table ot
set new_lob_column = (select data_column
from temp_mig_tab tmt
where tmt.pk_col = ot.pk_col)

There was no index on temp_mig_tab... so for every row I was doing a full table scan... Absolutley nothing to do with the LOB usage, just my own stupidity. Some of the other tables had HUGE lob's sort of 1M images but as there was only about 30 rows the full table scans were not a problem. It was this clue that lead me to the answer.

Now the whole process takes about 5 Minutes and migrates about 40 megs worth of data. The only thing I'd say about the whole process is it's a shame you can only use the TO_LOB function on inserts not updates. I wonder why Oracle made this restriction??

Shamefully,

Mike.