Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Provided Answers: 1

    Unanswered: Storing RTF in LOBs.

    Hey all.

    I'm designing a small (5 table) database in Oracle. The developers need to be able to store RTF (Rich Text Format) strings in some of the table.

    Is CLOB most appropriate, or would I be better off using BLOB?

    Also, the records in one table will need to store two RTF strings, but not for every record. Only about one third of the records in this table will require RTF fields. Should I create the LOB fields in this table, or as a separate table that is linked to this table? I'd think the simplest development approach would be to store them inline, but I am concerned about what performance impact this may have.


    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  2. #2
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    I stand open to being corrected, but as I understand it, typical LOB (whether LOB, BLOB or CLOB) storage by default will store up to 4k inline within the row. Inline meaning as part of the table itself on disk. If every query on the table includes the LOB, and most LOBS are below 4k this can result in apparently good lob performance.

    The fact that only a third of your rows are expected to include a LOB would suggest that you create a separate table (in a fully normalised approach this is general advice regardless of the datatype anyway).

    On 8.1.7 (I haven't tried on other versions) I have seen some strange performance behaviour - both hits and improvements by moving LOB storage out of line. I haven't quite put my finger on the reasons as yet (not enough time) but I suspect it is to do with block size (physical storage) and particular tables and their "LOB density" (I just made up that term - sorry!).

    I would strongly suggest you experiment with some representative data, moving entire storage out of line, partially and fully inline (iff possible) etc etc.

    The benefit of out of line of course is that you can meddle with the LOB storage parameters without affecting your main table storage parameters. Downside is the extra join, upside is non lob column queries being much faster.

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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