| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: what is wrong with this idea ...
I'd prefer to build a normalized ODS structure and then build
any denormalized structures on top of it. Thus, I'd keep the
Parent to Child relationship and put each status in it's own
row, with a temporal time stamp. If you need some denormalized
view of that (because of performance) then I'd build on top
of it whatever structure (e.g. snowflake) you need.
Keep the underlying data store as normalized as you can. It makes detail analysis and drill down much easier IMHO.
HTH RF
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author
Oracle9i RMAN Backup and Recovery (Oracle Press - Oct 2002)
Oracle9i New Features (Oracle Press)
Mastering Oracle8i (Sybex)
The avalanche has begun, It is too late for the pebbles to vote.
-----Original Message-----
Sent: Wednesday, August 14, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L
Gene - What is the motivation here? Are you trying to save space in a fact table with many, many rows? I agree that in data warehousing we sometimes violate the normal rules we adhere to in OLTP databases, and I think some of your responses have been assuming OLTP rules. For example, someone pointed out that updating might cause inconsistencies, etc. Well, in a data warehouse you usually write once, read many. You may never update the data, depending on the warehouse. Is your tradeoff between four separate columns or a single column with concatenated values?
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, August 14, 2002 8: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
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).
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).
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 - 11:23:43 CDT
 |
 |