Re: what is wrong with this idea ...

From: Jack Silvey <jack_silvey_at_YAHOO.COM>
Date: Wed, 14 Aug 2002 06:58:44 -0800
Message-ID: <F001.004B4774.20020814065844@fatcity.com>

Gene,

One of the basic rules of third normal form is to not store multiple (non-atomic) values or logic inside a column like this. The reason is that you end up storing the same values more than once, wasting space, and you have to substr the value to get your logic out of it, which could prevent index lookups, resulting in potentially nasty table scans and bad performance.

If you have a concatenated index on the FK and status columns of the child table, that status lookup should be rapid. With an index that contains all the columns to be looked up, your query might be totally resolved in the index itself and not need a table visit at all.

An extension of this idea would be storing the child records on an IOT, with all low-usage columns in the overflow. This would help ensure that the developer could resolve any set of highly-used columns rapidly, with the downside of relatively slow lookups for low-usage columns. Research this one first, though, since IOT tables come with their own set of challenges.

hth,

Jack

• Gurelei <gurelei_at_yahoo.com> wrote:
  > Hi. We have a table in our data warehouse which
  > keeps
  > info about calls made. This table has a child table
  > with some detailed information about parts of the
  > call. There may be any number of "parts" within a
  > call
  > (1 to many) and every part has a status.
  >
  > MY developer wants to add a string field to the
  > parent
  > table which will concatinate all the statuses for
  > all the parts within this call. For example if
  > a call has 4 parts and their statuses are
  > "A","B","A"
  > and "F", the value of that field will be "ABFA".
  > Then
  > the developer will be able to query smalle parent
  > table instead of a large child table in order to see
  > how many calls had at least one part with status "A"
  > or statuses "A" and "F" etc by using a INSTR (or
  > SUBSTR) command.
  >
  > Would it be better (from performance/CPU standpoint)
  > to add several separate fields: STATUS_A_CNT,
  > STATUS_B_CNT (the list of status codes is fairly
  > static) instead? There is something about this
  > string
  > that rubs me the wrong way, but I can't put my
  > finger
  > on it.
  >
  > Any thoughts?
  >
  > thank you
  >
  > Gene
  >
  > __________________________________________________
  > Do You Yahoo!?
  > HotJobs - Search Thousands of New Jobs
  > http://www.hotjobs.com
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Gurelei
  > INET: gurelei_at_yahoo.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >
  

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  

  ---

Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Aug 14 2002 - 09:58:44 CDT