Separate Tablespace for LOBs ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Separate Tablespace for LOBs ?

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    One of our applications will heavily make use of LOBs in the tables underneath. One of my users asked me if we should keep the LOB-heavy-tables in a separate tablespace.

    I don't know what the advantages will be to keep them in a separate tablespace.

    Currently they are in a tablespace that has other BIG non-LOB-tables as well and all the tables have been created with the uniform Extent_sizes.

    Could anyone point out a few advantages of keeping LOB-tables in a separate tablespace ?

    Thanks,
    - Rajeev
    Rajeev Suri

  2. #2
    Join Date
    Mar 2001
    Posts
    71
    Hi,

    We have LOB fileds in our table. We always keep LOB in separate tablespace than Table. Also, we use DISABLE
    STORAGE IN ROW clause for LOB to make sure all contents
    of LOB are stored outside table. We have OLTP and some queries do FTS on table. If first 4000 bytes are stored
    in table, FTS will be costly.

    HTH,

    Let me know if you need more info.

    np70

  3. #3
    Join Date
    Nov 2000
    Posts
    245

    The whole purpose is spilt the disk I/O into multi-disks,
    if the datafile for this new tablespace locate on same disk
    then there is no difference create sperate tablespace.

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    jm,
    the disk i/o thing is true for any objects not just for LOBs.

    np70,
    Could you explain what do you mean by --

    "If first 4000 bytes are stored in table, FTS will be costly. "

    Thanks,
    - Rajeev
    Rajeev Suri

  5. #5
    Join Date
    Mar 2001
    Posts
    71
    Rajeev,

    If you DON'T specify DISABLE STORAGE IN ROW for your LOB
    clause, Oracle will put lob contents in row itself. If
    length of LOB column becomes more than 4000 bytes, Oracle
    will move contents out of row.
    If you DISABLE STORAGE IN ROW CACHE, only LOB locator is
    stored in table, which takes less space.

    Having first 4000 bytes in Row itself will make Full Table
    Scan costly for that table.

    FTS-->Full Table Scan

    HTH

    np70

  6. #6
    Join Date
    Jul 2001
    Posts
    6
    Hmmmm. I think it depends on your application. I agree that full table scans would be more costly if part of the information is stored in-line with the table and part of it is stored elsewhere via a LOB locator. But, who designs applications that do full table scans?

    We store XML inside CLOBs with the first 4000 bytes in-line with the table and those CLOBs that are over 4000 in a separate tablespace (on a separate disk). We use InterMedia Text to index those CLOBs. So, searching for anything in the XML column results in a hit on the InterMedia index, not the table itself. Then, for most of the rows ids returned from the index data is returned from the table and in a few cases, where the CLOB is over 4000 bytes, a recursive call is made to get the data via the CLOB locator. Much faster.

    So, guess it just depends on your application.

    -Christopher

Posting Permissions

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


Click Here to Expand Forum to Full Width