|
Hm, yep, it seems it works only in insert select
command (I tried an update with subquery) but didn't succeed).
In this case try following: 1) if you got partitioning option and have enough
space to store a temporary copy of your table in database, then:
SQL> create table t (id number,
long_col long);
Table created.
SQL> insert into t select rownum, name
from sys.obj$ where rownum <=5;
5 rows created.
SQL> commit;
Commit complete.
SQL> desc
t;
Name Null? Type ----------------------------- -------- -------------------- ID NUMBER LONG_COL LONG SQL> select * from t;
ID LONG_COL
---------- -------------------- 1 I_COBJ# 2 C_FILE#_BLOCK# 3 SEG$ 4 I_UNDO1 5 I_COL1 SQL> create table t_tmp (id,
clob_col)
2 partition by range (id) 3 (partition t_tmp_p1 values less than (maxvalue)) 4 nologging 5 as select id, to_lob(long_col) from t; This command creates partitioned t_tmp
table with only one partition which holds all rows
Nologging is useful here, you can
add PARALLEL as well.
Table created.
SQL> truncate table t drop
storage;
We truncate t because we have a copy of the
data in t_tmp and that way drop column will be faster
Table truncated.
SQL> alter table t drop column
long_col;
Table altered.
SQL> alter table t add (lob_col
clob);
We can only exchange partitions between
tables with same number of columns, their datatypes and
order (note that column names don't matter, at least in 9.2 they
don't).
Table altered.
SQL>
SQL> alter table t_tmp exchange partition t_tmp_p1 with table t without validation; Using without validation reduces CPU usage
during exchange operation, because no check is done whether all rows in segment
really belong to specified partition (in this example we have only one partition
for all rows anyway).
If we had any indexes on T table, these
will be marked unusable (with exception of the LOB index we have now) and have
to be rebuilt. If we would have built a matching LOCAL index on t_tmp for all
indexes on T for some reason, we could do the exchange using "including indexes"
clause, that way index segments get exchanged as well and no rebuild is
needed.
Table altered.
SQL>
SQL> drop table t_tmp; Table dropped.
SQL>
SQL> desc t; Name Null? Type ----------------------------- -------- -------------------- ID NUMBER LOB_COL CLOB SQL> select * from t;
ID LOB_COL
---------- -------------------- 1 I_COBJ# 2 C_FILE#_BLOCK# 3 SEG$ 4 I_UNDO1 5 I_COL1 SQL> :)
So in this example we only use double
amount of disk space temporarily. We do only one cycle of IO -> inserting
into t_tmp from t. The exhange partition doesn't copy any data, it just updates
data dictionary and segment header blocks to reflect new exchanged
extent locations.
Comments:
* You might want to specify different storage
options (tablespaces) for LOB and table segments.
* It's reasonable to create the partitioned table
to tablespace where you want your data to remain, that way you avoid additiona
IO which would be needed for "alter table move tablespace".
2) If you don't have partitioning option or no
extra disk space, then we have to do the job in step-by-step in batches.
Unfortunately exchange partition doesn't work for exchanging only parts of
non-partitioned table, thus we have to use regular direct load insert commands
for the job:
SQL> drop table t;
Table dropped.
SQL> create table t (id number,
long_col long);
Table created.
SQL> alter table t
nologging;
We can't use nologging in hint in 8i (in 9i
it works), thus have to alter table in order to save IO.
Table altered.
SQL> insert /*+ APPEND */ into t select
rownum, name from sys.obj$ where rownum <=5;
5 rows created.
SQL> alter table t logging;
Table altered.
SQL> desc
t;
Name Null? Type ----------------------------- -------- -------------------- ID NUMBER LONG_COL LONG SQL> select * from t;
ID LONG_COL
---------- -------------------- 1 FILE$ 2 BOOTSTRAP$ 3 I_COBJ# 4 I_CON2 5 SEG$ SQL> alter table t add clob_col
clob;
Table altered.
SQL> insert into t (id,
clob_col) select id, to_lob(long_col) from
t;
We insert into clob_col but select from
long_col.
5 rows created.
SQL> select * from t;
ID
LONG_COL
CLOB_COL
---------- -------------------- -------------------- 1 FILE$ 2 BOOTSTRAP$ 3 I_COBJ# 4 I_CON2 5 SEG$ 1 FILE$ 2 BOOTSTRAP$ 3 I_COBJ# 4 I_CON2 5 SEG$ 10 rows selected.
Here we have two copies of each
row
And we should ger rid of
them:
SQL> delete from t where
long_col is not null;
5 rows deleted.
SQL> select * from t;
ID
LONG_COL
CLOB_COL
---------- -------------------- -------------------- 1 FILE$ 2 BOOTSTRAP$ 3 I_COBJ# 4 I_CON2 5 SEG$ SQL> alter table t drop column
long_col;
Table altered.
SQL> select * from t;
ID CLOB_COL
---------- -------------------- 1 FILE$ 2 BOOTSTRAP$ 3 I_COBJ# 4 I_CON2 5 SEG$ SQL> :)
Comments:
* You have to create a foolproof criteria for
deleting old copies of records (just checking for not null might erase some
records which are null in source as well)
* You can use parallel inserts if your hardware
permits
* Insert +append to long column didn't work anymore
in 9.2.0.4, in 8.1.7 it works ok (second test is done on 32bit 8.1.7.1
Solaris)
* I personally think, that if you're dealing with
one or few tables, it's easier to recreate tables, that way you'll save yourself
from significant IO occuring during delete.
* Do the job in relatively small batches, that way
you'll save disk space (if you grow your table to 2X it's size, there's no way
to reduce it without reorganization).
Cheers,
Tanel.
----- Original Message ----- From: Siddharth Haldankar To: Multiple recipients of list ORACLE-L Sent: Thursday, September 11, 2003 4:19 PM Subject: RE: converting long to lob - the easiest and fastest way ? Thanks Tanel, But to_lob does not work in update clause, it only works in select clause, that is the restriction it has. With Warm Regards Siddharth Haldankar Cisco Systems Inc. ODC Zensar Technologies Ltd. # : 4128374 [EMAIL PROTECTED] [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tanel Poder Sent: Thursday, September 11, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: converting long to lob - the easiest and fastest way ? Sorry, I didn't notice in the end of your post, that you don't want to recreate your table. Then try this one: add a new clob type column to your table using alter table add. make an update statement: update tableA set col1=null, newcol=to_lob(col1); use alter table drop column col1 afterwards and if needed, rename newcol to col1 using alter table rename column (or whatever the syntax was). You might want to do this in batches and commit meanwhile if your table is big. Also, if your table is big, then you should test which mode lob segments are faster for your update - nocache nologging or cache logging. Oracle might be able to optimize serial new chunk write requests to batches, but I don't know whether it's true. It depends on the size of your current LONGs as well.. Tanel. ----- Original Message ----- From: Siddharth Haldankar To: Multiple recipients of list ORACLE-L Sent: Thursday, September 11, 2003 1:14 PM Subject: converting long to lob - the easiest and fastest way ? Hi Gurus, How do I convert a long datatype to clob datatype. Say Table A No number Col1 long Would like to convert to Table A No number Col1 clob Alter table A modify (col1 clob); Gives an error as inconsistent datatypes. Could you give me an example of this with lob_storage_clause. I would like to convert the existing data in clob type also. The to_lob works only for select statements, cannot go through that pain of table creation. Thanks in advance. - Siddharth |
- RE: (long and boring) converting long to lob - the eas... Tanel Poder
- RE: (long and boring) converting long to lob - th... Siddharth Haldankar
