Re: Why use LONG?

I store my long fields on a separate table. If you make this table generic
it can then be reference from any other tables that have a message column.
I've used this technique to store graphics. All tables that require a graphic column (may be more than one) just point to the graphic table. All
my graphics are then stored in one place.

I believe Oracle 8 blobs allow you to store the blob information separate
from the main data. So it now allows you to do it without creating another
table. Once less physical design decision.

Steven Franklin wrote:

> 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 Thu Mar 18 1999 - 06:04:01 CST