Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: CLOB space usage

    We're considering using a CLOB datatype to store HTML code, as it will exceed 4K. I'm having trouble finding documentation on how CLOB data is stored, and what kind of space considerations we should set aside.

    For example, does a CLOB allocate space in larger chunks as needed, or does it only reserve the amount of space initially required, like a varchar2 field?

    Or is there a new alternative in 10g that I'm not aware of?

    Using 10g

  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    I think you are correct to use a CLOB, although a BLOB would actually work too. Parsing strings into 4K chunks and storing a rows in a separate table is an option too, but that's basically what CLOB does for you. CLOBs do it all behind the scenes, and the unit of measure is larger too. I'm not aware of new data type in 10g that would be better for this purpose.

    When working with CLOBs it is critical to understand a table can have many segments. This was not true in Oracle7 and before, and even now is only used in some cases like CLOBs and BLOBs. When you create a table that has a CLOB, you can tell Oracle to either:
    1. store the first 4000 bytes in-line with the row and store all extra bytes out-of-line in an overflow segment, or
    2. store all CLOB data out-of-line in the overflow segment.
    If you are usign partitioning, then you will probably have to force the out-of-line storage, which I tend to do all the time anyways.

    The CLOB's width is dynamic and uses space, or releases space, as you update the column. The difference is the unit of measure used to grab space. A normal column is inside a table's extent, and the column's unit of measure is bytes. A CLOB column's overflow segment is a table itself, so the unit of measure becomes an extent. See the segment's extent sizing data for more info about how this impacts your database.

    There is also a "chunk size" with a maxsize of 32 KB. This is a performance tuning issue more than a database developer issue. It tells Oracle the max amount of data to buffer between the DB and the app. You can set it per CLOB and then tell your app developers so they know how big to make their variables.

  3. #3
    Join Date
    Dec 2003
    Is there a way to determine how much space any one particular CLOB is using?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts