what to use clob or varchar for more then 4000 char

[volk]

I have couple columns that require 40000 bytes.
What is better approach?
1. use Clob data type or
2. create second table with one to many relationship and store information as
varchar

PROCEDURE sp_test(inPar in LONG)is
str_1_var varchar(4000);
str_2_var varchar(32000);
no_of_cuts number(10);
begin
if inPar is not null then

no_of_cuts := ROUND_UP(length(inPar)/3500);

str_2_var := inPar;

For loop_index in 1..no_of_cuts loop
str_1_var := substr(str_2_var,tmp,3500);
str_2_var := substr(str_2_var,3501);
INSERT INTO TEST_INS(TEST_ID,FUND)VALUES(loop_index,str_1_var) ;
End loop;
End if;
end sp_test;


First approach is seems easy but it is not easy to work with in a future.
CLOB working slow with searching (like '%some_search%')
CLOB can store up to 2 GB - little bit overkill does it?
Second is sound better to me because it has power of using all Oracle string
function in search.
Only one inconvenience that string has to be cut and then concatenated back
together before displaying to user.


I would appreciate any opinion on this.

[tconley]

If the biggest concern is searching, with both clob and varchar you
can use Oracle's Text which will outperform '%searchstring%' in
most cases.

It's also very flexible in the way it can be configured (stop words, thesaurus,
case sensitive/insensitive, near operator, ...

I'm not aware of any Oracle string functions that don't work with clob.

[volk]

Thank you for reapply.
Could you give some examples for ?Oracle's Text? I am not sure I understand
completely.

Please correct me if I am wrong but it looks like all of them does not work.
For example:

SQL> select length(tmp_column)from tbl_test;
LENGTH(tmp_column)
------------
3500

SQL> select lenght(tmp_column_CLOB)from tbl_test;
select lenght(tmp_column_CLOB)from test_ins
*
ERROR at line 1:
ORA-00904: invalid column name

So to do any operation with clob you have to use DBMS_LOB.


Cold fusion acting very interesting.
If you are queering the table with CLOB all Cold Fusion string functions will
work until you need to work with CLOB that are more then 32000 bytes and you
have to specified in Cold Fusion Administrator ? data sources option ?Enable
long text retrieval (CLOB)? then all Cold Fusion string functions will stop
work. Imaging if somebody uses a lot of string functions before enabling this.

[tconley]

I don't have the problem with length you're describing using Oracle 9.2,
Coldfusion MX 6.1

We do use DBMS_LOB.Substring but that was more for performance on the
backed, if I remember correctly.

On Oracle Text from the documentation:
"Oracle Text provides indexing, word and theme searching, and viewing
capabilities for text" it's like Verity but built into the database

go here and search for "Text Application Developers Guide"
[url]http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=5[/url]

[volk]

We are using Oracle 8i and we do have those restriction. It looks like oracle
improving CLOB. Let me ask you design question: Will it be good idea to put
CLOB columns into another table and connect to main table with one to one
relationship so my main table will not have restriction? What do you think?
For example :

LOB Features Introduced in Oracle9i Release 2

Parallel Execution Support for DML Operations on LOBs

Support for parallel execution of the following DML operations on tables with
LOB columns is introduced in this release. These operations run in parallel
execution mode only when performed on a partitioned table. If the table is not
partitioned, then these operations run in serial execution mode.
_ INSERT AS SELECT
_ CREATE TABLE AS SELECT
_ DELETE
_ UPDATE
_ MERGE (conditional UPDATE and INSERT)
_ Multitable INSERT

[volk]

..