Join cardinality

Oracle 9.2.0.5

Table 1 - Calendar with following columns country_id
load_date
date_desc

Table 2 - Customer dimension (SCD type 2)

customer_id
from_date
to_date
country_id
latest_flag

Customer table is partitioned on country_id and sub partitioned on latest_flag that is 0 and 1 (Latest and history)

I fire queries like this -

select     *
from        customer cust,
              calendar cal
where     cal.country_id in ( '1', '2', '3' ) -- Literals
and        cal.date_desc = 'Last_Quarter'
and        cal.load_date between cust.from_date and cust.to_date
--and        cal.country_id = cust.country_id

Now the problem is -

1. The cardinality calculated by Oracle is incorrect. It shows only 60 odd rows coming from the customer table when in fact there could be 25,000. The access to customer table is done via a composite and compressed B-tree on from_date and to_date.
2. When I uncomment the country_id join, the cardinality reduces drastically which should not happen.

There are around 30 distinct values for countries and the data distribution is not even.

I gather statistics for granularity => 'ALL'. Histograms are gathered on country_id and all indexed columns.

How can I ensure that cardinality is properly calculated by Oracle. As such the above query won't create a problem. But when I add 2-3 more tables then the effect of incorrect cardinality can be seen in sub-optimal execution plans.

Regards,
Pratap Received on Thu Apr 06 2006 - 08:10:07 CDT