Re: Storing Binary Data in varchar2 fields

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EE8E349.E32FAD2C_at_exxesolutions.com>...
> Ed Barker wrote:
>
> > We have a need to define a table that will store a variable amount of
> > binary data in a column. The length will be less than 4000 bytes.
> >
> > I have been told that there is overhead involved when using blob
> > fields. This is an OLTP app with heavy traffic (select, update).
> > Performance in updating and selecting this data is critical. This
> > binary data will never be part of our selection criteria, but we will
> > always be returning it in our queries and we will be updating it.
> >
> > Since the length of our data is small, we were hoping to avoid that
> > overhead by making the column varchar2. However, I have read some
> > information that indicates that I can not store binary data in a
> > varchar2 field because Oracle will perform translations.
> >
> > I have also read that the raw datatype is merely varchar2 without any
> > translation, which sounds like what I want. However, the docs
> > indicate that blobs should be used as of 8i (we are using 9.2).
> >
> > My questions are:
> >
> > - Is there really a performance hit when using blobs?
> >
> > - Can I safely use varchar2?
> >
> > Thanks for any help.
>
> Go to http://tahiti.oracle.com and as your seach criterion put in "RAW"
> AND "DATATYPE"
One other piece of info - We are using ProC.

After researching this further, I have concluded that:

• I can store binary data in a varchar2 field provided that the character set on the client matches that of the server. otherwise, translation will occur that mangles the binary data.
• using varchar2 will be more efficient that using blobs. I base this on the following:
  • It looks like the ProC interface requires two operations to work with blob data. You have to select the lob locator, and then either read or write it.
  • When inserting a row or updating its data, you have to treat the lob separately from the other data in the row. For example, to insert a row, you must first insert the row, then select it's lob locator, then write to the lob.

These items lead me to believe that performance will be better by using varchar2, which can be worked on in a single operation - cutting down on trips to the database always improves performance, in my experience.

Not to mention, it looks like less coding.

If my conclusions are correct, it seems strange (i.e. a deficiency in the product) that there is no data type similar to varchar2 but that tells Oracle to not perform any character translation. raw seemed to serve this purpose, but the docs say not to use it anymore.

Can any of the gurus confirm or refute my conclusions? Received on Tue Jun 17 2003 - 05:22:22 CDT