Database efficiency question

From: Beth Beckman <bbeckman_at_lanl.gov>
Date: Thu, 28 Jan 1999 11:36:40 -0700
Message-ID: <36B0AE38.F44E4B59@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:

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?
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 - 12:36:40 CST