Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Database efficiency question

Re: Database efficiency question

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: Thu, 28 Jan 1999 23:04:49 -0000
Message-ID: <78rbii$i7d$1@remarQ.com>


If you're running Oracle8, VARCHAR2 datatype can hold 4000 bytes, which should be plenty. If you use less, that's fine; there's no significant space penalty. Better you have defined a longer string than you need... for now. Maybe in the future, you'll have longer strings. If you need more than 4000 bytes, then you'll have to use LONG or CLOB, but they aren't as easy to use as VARCHAR2. For one thing, they're not easily searchable, especially from SQL. --
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Beth Beckman wrote in message <36B0AE38.F44E4B59_at_lanl.gov>...
>Hi! I have a colleague who posed the following scenario and questions
>for comment, and I thought I would get the opinions of a broader
>audience. What is your experience and/or
>guess about the following scenario:
>
>I'm defining the schema for a new table and I'd like your thoughts on
>efficiency and speed issues.
>
>I'm setting up a table that has three columns -- object, attribute, and
>value. A simple example:
>
> Object Attribute Value
> ------------------------------------------------------------
> "http://foobar.com/doc.html" "author" "H. Simpson"
> "http://foobar.com/doc.html" "summary" "some possibly long
>string..."
>
>
>I'm trying to decide what datatype to use for the value column. This
>ordinariliy will be a string that could be expressed in 255 characters
>or
>less. About 10% of the time, though, it will be a string that could be
>quite long (more than 2000 characters).
>
>Questions:
>
>1. I could set the value field to be a LONGVARCHAR, which allows an
>"infinitely" long character string. Is there a lot of overhead involved
>with using this type rather than a VARCHAR, especially when the string
>is
>typically fairly short? Would DB performance be impacted?
>
>2. An alternative would be to have the value field be a reference or
>pointer to value tables that have a specific type associated with them.
>That is, I could have a VARCHAR table for strings < 2000 chars. I could
>have a LONGVARCHAR table for values that exceeded 2000 characters. I
>could
>have a BLOB table for image data. And so on. The value field in the main
>
>table would just point to entries in these datatype-specific tables. It
>seems to me that this extra redirection would hurt performance. But
>would
>it save on disk or memory usage?
>
>3. Should I be considering some of Oracle's OO capabilities for this?
>
>Thanks in advance for any thoughts you have on this matter.
>
>Beth
>
Received on Thu Jan 28 1999 - 17:04:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US