MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Cleaning up wasted LOB space

with 9 comments

After replacing the contents of a BLOB or CLOB column, you will see the size grow because old indexes and segments aren’t deleted or removed from the index. The only way to get rid of the old information is to perform some simple table maintenance. The following provides an example of dumping redundant or obsolete space and indexes.

You should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. Such extremes as the regular expression in that query aren’t needed when DBAs ensure that LOBs are created with named segments. In this test case, this is the starting point:

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416

You create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.

ALTER TABLE item ADD (item_temp CLOB);
UPDATE item SET item_temp = item_desc;

When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.

ALTER TABLE item DROP COLUMN item_desc;
ALTER TABLE item ADD (item_desc CLOB);
UPDATE item SET item_desc = item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the temporary column after making the change.

ALTER TABLE item DROP COLUMN item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

9 Responses to 'Cleaning up wasted LOB space'

Subscribe to comments with RSS or TrackBack to 'Cleaning up wasted LOB space'.

  1. Looks like the last step is posted twice.

    tim

    14 Jan 10 at 3:33 pm

  2. Thanks for alerting me to the error. I fixed it. Drat that cut-and-paste. ;-)

    maclochlainn

    14 Jan 10 at 7:35 pm

  3. Hi Mr. M
    We have some humongous CLOBS. Have you ever run the updates using parallelism? Say a degree of 8?

    Patrick

    26 Jul 11 at 9:19 am

  4. No, I’ve not tried that. Let everybody know how it goes?

    maclochlainn

    26 Jul 11 at 1:25 pm

  5. Why update twice? Why not just drop the original column after the first update and then rename the temp column to the original name.

    Adam

    29 Aug 11 at 9:02 am

  6. No reason, probably a rut. I’ve modified the post to suggest that. Thanks.

    maclochlainn

    29 Aug 11 at 10:16 am

  7. […] index after mass DELETEs. Just a guess, but looks very similar to your case. If this is it, I would rebuild the lob column entirely. (Moving a lob column might also rebuild the lob index – I’m not sure). Answered by […]

    Updating BLOB field due to slow sequentials

    27 Jan 12 at 5:00 pm

  8. Hi,

    A very good example … now here is my problem. We’ve dropped and recreated the table. Now it has a new OBJECT_ID, how do we drop the LOBSEGMENTS of the table when it used to have the previous OBJECT_ID :(-

    Newbie01

    29 May 13 at 12:07 am

  9. You have orphaned segments? I’ll try to write a diagnostic query tonight for that, but that should be a bug.

    maclochlainn

    29 May 13 at 10:38 am

Leave a Reply