Datamodel changes in the Library 8

sn_questions: object_type_id renamed to target_object_type_id, because this name makes the semantics much clearer

sn_content: removed long_content, made content a clob
Newsgroup posting to make sure that this is definitely not a performance risk:

From: tkyte@us.oracle.com
Subject: Re: clobs and possible performance issues
To: Dirk Gomez 
Date: Sat, 14 Jul 2001 11:58:54 -0700 (PDT)

In article , Dirk says...
>
>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

I'd get rid of the content column (varchar2(4000)) and just go for the clob. 
Clob data, by default, will be stored inline upto your existing 4000 bytes (you
can control inline or out of line storage) so if the text is small, the clob
will be stored there, if the text is large, it'll be moved out of line into the
lob segment and stored there.

You are in effect mimicking what we already do under the covers with clobs.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Removed the PL/SQL function sn_object_type_view_p and replaced it with a dummy called km_dirks_temporary_permission_p that always return true.

Dirk Gomez