Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?
Date: 16 Sep 2006 14:50:41 -0700
Message-ID: <1158443441.404136.213310_at_e3g2000cwe.googlegroups.com>
Marshall wrote:
>
> I note that both TRANS and SUBTRANS have duplicate
> rows. Did you know that that is a really bad practice?
ok let's change to:
Item SubFact
IT1 1 IT2 2 IT2 3 IT2 4 Name Item Fact David IT2 1 Marshall IT1 2 Marshall IT1 3
[records are unimportant. In any case, in practice there can be transactions where one buys the same item and spend the same money. Here it "seems" record are equal because field key for trans are missing. But that's irrelevant to our purpose. I wanted to avoid discussion about contents because really immaterial here]
> So your question is how to handle the repetition of
> values from TRANS when aggregating over a column
> in TRANS with a non-idempotent aggregate?
The duplication I am talking about is the one caused by joining tables in 1-N relationship. When you make the join ONE-TRANS-SUBTRANS the values in TRANS are replicated due to the relationship 1-N with SUBTRANS . So (non idempotent) functions computed on TRANS consider the same values more times they should (duplicated values). This yeld a wrong result in the report for these functions.
Smart software is perfectly capable to split this query in subqueries, as also Alexander pointed out in the previous post. My original question was:
- how is this problem called in the theory (are there references to study?)
- what is the algorithm to do that (or is it some kind of industry secret that only few leader softwares implements?)
> What is wrong with "don't do that" as the answer?
That's not allowed :)) (It's allowed to have an algorithm that warn the user about some possible problem in the design)
I hope I finally succeeded to explain what I am talking about :)
-P Received on Sat Sep 16 2006 - 23:50:41 CEST