Re: Join cardinality

> The only logic that doesn't cause a problem
> is the logic that represents all your dates as the
> number of days since a fixed date.

Yes the logic is like that. Only difference is that the start point is different for each country.

> One workaround to this is to put the join predicate
> in place as:
> and cust.country_id = cal.country_id + 0

I will try that

> Potentially better still - you might consider using a
> partitioned view: Set up two separate tables with a
> UNION ALL view with 'instead of triggers'. The two
> tables could still be list partitioned by country.
>

Isn't it the same as sub-partitioned tables (partition by country and subpartition by latest flag or the reverse way) with local indexes? Actually there are reports that query the latest as well as the history data. That is the reason I have stuck to global indexes on one table only. Another reason for not using the partition views is that I have many dimensions (SCD type 2) exactly like customer table that are joined together. There can be as many as 4-5 such tables in a single query. I observed merge joins between the partitioned views in the (limited) test cases I executed and so shelved the idea.

The reason why I have created sub-partitions is that most of the reports run on latest data for one or many countries. So wanted that to be clustered in a partition. Received on Fri Apr 07 2006 - 08:25:58 CDT