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
