Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?

pamelafluente_at_libero.it schrieb:
> I have begun doing some attempts. I think the first step is to attain a
> "partioning" of the relationships so that we are able to identify the
> tables which will go in each subqueries.
> The final subqueries clearly might have tables in common.
>
> I was thinking to proceed as follows:
>
> 1. Identify all the tables which have at least one field in the report
> which has applied on it a replication sensitive aggregate function and
> that are, say, on side 1 of a 1-N relationship

What is a replication sensitive aggregation function?

> 1. For each ot the above tables
> Start from that table and "navigate" recursively through
> relationships 1-N until you find on side N a table which has a field (a
> pure dimension) in the report. When you find it stop, cutting out that
> table.
>
> 2. At this point you should have a partion of tables and relationships.
> By construction, within this partition there cannot be issues due to
> record replication: because a 1-N situation with an used field on side
> N is not possible.

How do you define a partition (of tables and relationships)?

What do you mean by "record replication"?

> 3. Now to build the subqueries,
> for each partition
> for each table in the partition which have at least one field
> in the report which has applied on it a replication sensitive aggregate
> function navigate recursively through relationship always in direction
> N-1 until you cannot go farther (for instance you find 1-N) *allowing
> to exit from partition*. Make a union all of these subqueries.

How many rows such a report will have (assuming you know the number of rows in all the tables)? As far as I understand, the final result set is a union so the number of record is a sum of all subqueries. How many records returns each subquery?

Could you demonstrate how this procedure works using the following example. There is a table of Companies and Products with many-to-many relationship stored in CP table. If I select one field from Companies and one field from Products (say, company_name and product_name), then what report your procedure will generate?

Now let us make it more complex. Assume that I want to have a field with the number of products produced by this company. How your procedure will generate the report?

And finally, I want to add a field with the number of companies producing this product (say, chips are produced by 5 companies while beer by only 3 companies). What happens in this case?

> -P

--
http://conceptoriented.com