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

Home -> Community -> Usenet -> c.d.o.server -> Re: multi-valued field?

Re: multi-valued field?

From: Allen Kirby <akirby_at_att.com>
Date: 1997/01/07
Message-ID: <32D24CBC.3CE6@att.com>#1/1

Tony Vuong wrote:

> Assuming now, that I have a column field that is multi-valued, one value
> is separate from another by a delimiter. For example, my rows might look like
>
> Record Multi-Values
> 1 a, b, c, d
> 2 a, b
> 3 d, e
> 4 b, c
>
> During runtime, I would like to achieve a way of "breaking-up", or
> "component-erizing" the Multi-Values into a dynamically created components
 

> Id Single-Value
> 1 a
> 1 b
> 1 c
> 1 d
> 2 a
> 2 b
> 3 d
> 3 e
> 4 b
> 4 c
>
> The question is whether I can do the break-up with only SQL statements (without
> procedural SQL like PL/SQL)? If not, then can I do it with procedural-SQL?

Let me get this straight. First you denormalized the table to store repeating groups in one column. This, of course, eliminates the possibility of using an index on that column, or using it as a foreign key to another table. It also adds overhead and application complexity in that anything that directly accesses that field must have the logic to encode/decode that field. It's not clear at all that you are really saving much in the way of performance here. Selecting a few rows from the same table or doing a simple join to another table for a few rows isn't normally going to hurt performance much.

Now that you've designed this neat data storage feature, you want to access the data as if it were normalized, which you are finding out requires even more customized code. If you need performance enhancements that bad, you probably need a bigger and/or faster box to run this on.

Sorry to sound so sarcastic, but I've been on the raw end of designs like this many times both as a developer and a support person. TRY it with normalized data first and see if you can make it work with a little tuning. Get a bigger box. Try to avoid this if possible.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.
Received on Tue Jan 07 1997 - 00:00:00 CST

Original text of this message

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