clobs and possible performance issues

Hi,

This is the current table layout:

create table sn_content (

        object_id
          integer 
            constraint sn_content_object_id_fk
              references sn_objects(object_id),
        question_id
          integer  
            constraint sn_content_question_id_fk
              references sn_questions(question_id),
        content
          varchar(4000),
        long_content
          clob,
        html_p
          char(1)
            constraint sn_content_html_p_ck 
            check (html_p in ('t', 'f')),
        constraint sn_content_pk 
          primary key (object_id, question_id)
);       

Either content or long_content is being filled with data depending on the size of content. If the content is > 4000 bytes, it is stored in the lief long_content and content is set to null and vice versa.

That has mainly historical reasons - and I would like to get rid of that.

So retrieving content is currently done in this way (in meta pl/sql syntax):

select content into the_content where object_id=:the_object_id and question_id=:the_question_id;

if content is null then
select long_content into the_contet where object_id=:the_object_id and question_id=:the_question_id;
end if;

Are there any performance implications when removing the content field? The overall amount of queries on sn_content would get smaller, but all queries would end up retrieving data from a clob or stuffing data into a clob. How do queries on tables with clobs perform, mainly inserts and selects? All lookups on sn_content are indexed, there are no range scans.

cheers Dirk Received on Sat Jul 14 2001 - 13:19:11 CDT