Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: CLOB vs VARCHAR - which uses less disk space

    Hi,

    I have to save some big texts into database. Now I have a table with column defined as CLOB with 10 MB. In that table is also some other columns, but are not problematic. In table is 300.000 rows which occupy 22 GB of disk space. Disk space is the problem. Would it be better to define VARCHAR field instread of CLOB? Would VARCHAR reduce the disk space used?

    My system: DB2 v8 fp2 on Windows 2000

    Thanks,
    Grofaty
    Last edited by grofaty; 06-17-04 at 04:49.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by grofaty
    Hi,

    I have to save some big texts into database. Now I have a table with column defined as CLOB with 10 MB. In that table is also some other columns, but are not problematic. In table is 300.000 rows which occupy 22 GB of disk space. Disk space is the problem. Would it be better to define VARCHAR field instread of CLOB? Would VARCHAR reduce the disk space used?

    My system: DB2 v8 fp2 on Windows 2000

    Thanks,
    Grofaty
    It would, but not significantly... you no longer need the LOB locator. I also think DB2 makes a better effort to "stuff" the page full with VARCHARs than LOBs (although someone would have to confirm that).

    I found a very significant difference in INSERT and SELECT performance when using VARCHAR instead of LOB, presumably due to the bufferpool usage. VARCHARs are much faster to access. Of course, if your system is memory constrained, you could fill up the bufferpool with these large VARCHARs leaving little room for anything else... it's a balance.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    So LOB fields does not use bufferpool and VARCHAR fileds does?

    Thanks,
    Grofaty

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by grofaty
    Hi,

    So LOB fields does not use bufferpool and VARCHAR fileds does?

    Thanks,
    Grofaty
    Correct. (except LONG VARCHAR, it doesn't)
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    J Petruk, thank you very much.

    Thanks,
    Grofaty

Posting Permissions

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