Why use LONG?

   I have a table like this:
Table1 (

   some varchar2(15),
   searchable number,

   info             varcha(10),
   message      long

where the LONG field contains variable length text in ranging in size from a about 50 characters upto 30K+. This table is getting chained frequently killing preformance.

Wouldn't something like this be better

Table1 (

   some varchar2(15),
   searchable number,

   info            varcha(10),
   msgid         number  unique index

Table2 (

   msgid number fk(table1.msgid),    sequence number,
   message varchar2(80
)

Then retrieving the full dataset becomes something like: SELECT some,searchable,info
FROM table1
WHERE info='X';

SELECT message
FROM table1,

            table2,
WHERE (table1.msgid=table2.msgid) AND

               (table1.info='X')
ORDER BY sequence;

with a front-end tool concatenating the message rows from table2 together.

Why should I use the LONG type over the one-many table? Should the LONG

type provide better preformance if tuned properly? With the one-many table
I am not limited on the size of my message, and I can search the message body for
values. Can someone tell me why I'm wrong to want to use the one-many table
over the LONG type? Received on Wed Mar 17 1999 - 07:34:15 CST