Oracle-L: RE: what is wrong with this idea ...

From: Grabowy, Chris <cgrabowy_at_fcg.com>
Date: Wed, 14 Aug 2002 06:43:42 -0800
Message-ID: <F001.004B4664.20020814064342@fatcity.com>

Sounds like a bit of denormalization. Which during development you may have to do for performance reasons. Checkout www.dmreview.com for some articles on this. Or Ralph Kimball's site.

I believe one of the design rules state that you would want to avoid creating those STATUS_A_CNT, etc. fields. What happens when you need new status counts?? Then you have to add more fields?? Sounds bad. Isn't that a violation of First Normal Form??

Also, I would imagine that parsing a single column to get all the status counts is a lot easier and faster then having to check multiple columns. Dunno. You would have to gen up the code to see.

BTW, I believe there are more parsing functions then the standard fare in the book that may help in this situation. Like OWA_PATTERN, here is a snippet from my notes...

OWA_PATTERN
An example from Jared Still...

declare

tstr varchar2(100) := 'this c34ontains s0239everal 2340 numeric 882dig2its';
begin

dbms_output.put_line( tstr);

remove the digits owa_pattern.change( tstr, '\d', '', 'g'); dbms_output.put_line( tstr);
remove the extra spaces owa_pattern.change( tstr, '\s+', ' ', 'g'); dbms_output.put_line( tstr); end; /

A link to documentation on the package...

http://otn.oracle.com/doc/windows/was.30/admdoc/docs/cart/pspatt.htm

And here is a link to asktom.oracle.com that may help...

http://asktom.oracle.com/pls/ask/f?p=4950:8:995447::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:3570878994858,%7Bowa_pattern%7D

Scripts to execute to create the package in the database.

$ORACLE_HOME/rdbms/admin/pubpat.sql
$ORACLE_HOME/rdbms/admin/privpat.sql

-----Original Message-----
Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Grabowy, Chris
INET: cgrabowy_at_fcg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------