Re: LONG vs. Multiple VARCHAR2

Michael Contillo (contillo_at_kaiwan.com) wrote:
: My company is "upsizing" from Clipper to Oracle. We have one field
: where the user's can type in freeform text. For those familiar with
: xBase, this field is a MEMO field. In designing our Oracle tables I
: had thought that this field would be a LONG, however our consultant
: wants us to create a separate table where this freeform text would be
: "chunked" into several rows and stored in VARCHAR2 columns.
 

: I understand that one of the advantages of this is that a VARCHAR2 can
: be searched while a long cannot, but our users currently cannot search
: the notes, so this is not a compelling reason to go to the trouble of
: writing all of the "chunking" code that would be required to
: accomplish this.
 

: My question is: Given the situation, what are the advantages and
: disadvantages of using a LONG datatype to store freeform text?

Advantages of using LONG:

1. Simple to maintain and code against.

Disadvantages of using LONG:

1. Poorer performance.
2. No search ability.
3. Chained rows (rows which span over multiple Oracle db blocks. avoid for perf and robustness reasons.)

The advantage is very significant, as coding an interface to present and allow an end-user to manipulate several lines of text which appears as a single free form text is no trivial task. However, I would bite the bullet due to the disadv's (a slightly biased opinion since my primary hat is a DBA's)

Also, you may want to consider separating the long field out to a separate table to improve performance in table reads (unless your app always reads a long along with the rest of the fields, in which case it's somewhat mitigated).

Johnny Chan
Independent Oracle Specialist Received on Tue Aug 08 1995 - 00:00:00 CEST