Re: what is wrong with this idea ...
but you dont know how many records can be there in child table . Also if the status changes for child record you will have to take care of updating teh parent one also . -bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 6:18 AM 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: BigP INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ... - thanks
This is to thank all those who replied to my post - Thomas, Dennis, Jack, Melissa, Manavendra, Jerome, Steven , Stephen and Michael (sorry if I missed someone). I have been able to create the aggregate which IMO will speed up the reports so hopefully the developers will go along. thank you all for your responses. 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
what is wrong with this idea ...
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ...
Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ...
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: what is wrong with this idea ...
With individual columns you save on the CPU cycles/time involved to parse the string and extract each character. Additionally, if you ever need to update this string, it would be tedious. IMO, its always better to have atomic data since one can then leverage the features of the DBMS. With Warm Regards, Manav. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 6:18 AM 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Manavendra Gupta INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ...
Title: RE: what is wrong with this idea ... Gene, You would be denormalizing to hopefully improve performance. The only way to tell is to test and test some more. Personally I don't see how putting the info in the parent table with either method will improve things. I bet updates and inserts will take longer. using a INSTR (or SUBSTR) command. That will be slow. A function based index MIGHT help but I doubt it. (the list of status codes is fairly static) If the status codes were chiseled in stone, I MIGHT consider doing something like that. But things always change and even 'stones' erode. Adding or even subtracting one status code could require updating a lot of forms, reports, and SQL statements each time. From a performance standpoint, it might be much better to tune what you already have. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Gurelei [SMTP:[EMAIL PROTECTED]] 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
RE: what is wrong with this idea ...
Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: what is wrong with this idea ... 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Gurelei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: what is wrong with this idea ...
-- Gurelei [EMAIL PROTECTED] 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. I would avoid composite fields at all costs. Performance is one reason: you have to perform a substr to get at the foreign key value. Function indexes can help there but properly normalizing the data will do a better job. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ...
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. [EMAIL PROTECTED] -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 __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ...
Gene, you asked what was wrong and I think everyone gave you their opinion. you know by now that what you are told today will change next month. once you begin down this path, you will never stop. you know that as soon as you set this up, and they have written a ton of reports, that a new process will come along and will update the status field. then, you are stuck trying to update the parent record because it would be too much work for them to go back and fix their reports. don't give away the farm today if it will possibly cause you to purchase it back at twice the price later! rules that I always follow: 1). keep the design as clean as possible 2). *never* design database tables to make programmers lives easier, while violating rule #1. it's totally up to you. it sounds like your organization should entertain re-designing the warehouse for reporting needs. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 14, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: what is wrong with this idea ... 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
Re: what is wrong with this idea ...
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:RE: what is wrong with this idea ...
I agree with Tom, from a practical point of view. Some 2 years ago this young whippersnapper came on board to design a data warehouse for us. One of his ideas was to concatenate a bunch of columns from the operational data into one column in the warehouse, store it as a separate table with an ID and foreign key it into the fact table. Well guess what we're breaking back up! Reason, you can't put all of the variables inside the mess he created. BTW, he did not just concatenate single characters, but strings, like 'High Temp'||'Nominal input voltage'||'Nominal Load'||.. Ad nauseum Dick Goulet Reply Separator Author: Mercadante; Thomas F [EMAIL PROTECTED] Date: 8/14/2002 7:28 AM Gene, you asked what was wrong and I think everyone gave you their opinion. you know by now that what you are told today will change next month. once you begin down this path, you will never stop. you know that as soon as you set this up, and they have written a ton of reports, that a new process will come along and will update the status field. then, you are stuck trying to update the parent record because it would be too much work for them to go back and fix their reports. don't give away the farm today if it will possibly cause you to purchase it back at twice the price later! rules that I always follow: 1). keep the design as clean as possible 2). *never* design database tables to make programmers lives easier, while violating rule #1. it's totally up to you. it sounds like your organization should entertain re-designing the warehouse for reporting needs. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 14, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Gene, You ask what's wrong with this? The main challenge I see is: how are you going to maintain this field? If a status gets updated in the child table, are you supposed to update the parent table? And to do that, you will need to requery the child table to get all the status's of all children records whenever one status changes. Pretty silly really, Why not do this - create a function that, given the PK of the parent table, queries all the child records and concatenates all the status values into one string. Then either create a view for the duhveloper to use to return what he wants to return at query time. Something like this: Create or replace function get_child_status(in_pk_column) return varchar2 is cursor c1 is select status_code from child_table where pk_column=in_pk_column; ret_string varchar2(10); begin for c1_rec in c1 loop ret_string := ret_string || c1_rec.status_code; end loop; return ret_string end; The duhveloper can then: select parent_pk_column, get_child_status(parent_pk_column) from parent_table; Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Subject: what is wrong with this idea ... 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: [EMAIL PROTECTED] Fat City
RE: what is wrong with this idea ...
Dennis, Yes, at this moment I'm debating whether to go with one combined string or several separate fields. I'm not thrilled by any of the options, but the former really doesn't look good to me. What I'm trying to avoid is every transaction having to run a query against a large (tens of millions rows) child table in order to count the statuses. Another - totally different - option would be to create an aggregate (I think someone did mention that), but I haven't had much sucess designing an appropriate aggregate yet Gene --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Is your tradeoff between four separate columns or a single column with concatenated values? __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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. [EMAIL PROTECTED] -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 __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Freeman, Robert INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: what is wrong with this idea ...
Title: RE: what is wrong with this idea ... If you are using 8i+ consider using materl. views. They are wonderful and make maintenance of denormalized objects much less of a chore. I am oh so happy with them. There are some limitations but still. -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: what is wrong with this idea ... Dennis, Yes, at this moment I'm debating whether to go with one combined string or several separate fields. I'm not thrilled by any of the options, but the former really doesn't look good to me. What I'm trying to avoid is every transaction having to run a query against a large (tens of millions rows) child table in order to count the statuses. Another - totally different - option would be to create an aggregate (I think someone did mention that), but I haven't had much sucess designing an appropriate aggregate yet Gene --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Is your tradeoff between four separate columns or a single column with concatenated values? __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
