-
Migrating Longs to CLOBS
I'm updating a database designed circa 7.3 and moving the LONGs and LONG RAWs into CLOBs and BLOBs.
Is the 'best' practice to create a LOB specific Tablespace and store the LOBs in this. Is it a big performance hit to retrieve from the LOB doing this, if the records PK is in a different Tablespace?
What are the disadvantages of leaving CLOBS as columns in the tablespace with the rest of the record?
Also - the description of the LOBS in dba_segments is very ugly (system generated names) can this be changed at all (at table create time perhaps?).
-
Sorry to have to bump this one back up to the top.
Really interested in your comments on this LOB query.
-
Hi, were you "Jack" on Ask Tom? If not, here is Tom's answer to the naming bit:
Code:
In order to "name them", you do that in the create:
ops$tkyte@ORA9IR2> create table WMS_CLOB (
2 wmsObjectId NUMBER(15,5),
3 wmsObjectType VARCHAR2(20),
4 wmsClob CLOB
5 ) tablespace users
6 storage ( INITIAL 256K NEXT 256K PCTINCREASE 0 )
7 LOB (wmsClob) STORE AS my_Nice_Lob_Name
8 ( TABLESPACE tools
9 DISABLE STORAGE IN ROW
10 STORAGE (INITIAL 32K NEXT 32K )
11 CHUNK 4
12 INDEX my_nice_index_name (TABLESPACE manual
13 STORAGE ( INITIAL 16K NEXT 16K )
14 )
15 )
16 /
Table created.
ops$tkyte@ORA9IR2> select segment_name, segment_type, tablespace_name from
user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLES
------------------------------ ------------------ ------
WMS_CLOB TABLE USERS
MY_NICE_INDEX_NAME LOBINDEX TOOLS
MY_NICE_LOB_NAME LOBSEGMENT TOOLS
Now, note the index is in the same tablespace as the LOBSEGMENT. That'll always
-- always be true now. You cannot have them in two different places.
Also -- please, please use LMTs!! no more initial, no more next, no more
pctincrease, no more minextents, no more maxextents. Consider them "legacy
things we do not bother with in 2004"
-
If the LOB's are likely to be an order of magnitude bigger than the rest of the row - then you may want to have them in a TS with a bigger extent size?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|