Re: LONG vs. Multiple VARCHAR2

Speaking as someone who uses long fields extensively, I agree with some of the disadvantages that have been listed and I could add many more. However, I use SQL*TextRetrieval and I find that long fields are not such a big problem. I can index and search (quite efficently) on my long fields and if I want I can even store the long fields outside the database (which I do). This is just my .02 though. I would like to add that it is much easier to make a long field out of separate fields than it is to separate a long field into separate long fields.

Jane

Contractor - Yuk Hon "Johnny" Chan (jychan_at_corp.hp.com) wrote:
: 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 Wed Aug 09 1995 - 00:00:00 CEST