Re: Join cardinality

"Pratap" <pratap_fin_at_rediffmail.com> wrote:
> 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.

What do you mean "could be"? Your query doesn't use bind variables, it is completely specified. Either its cardinality is around 25,000, or it is not.

> 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.

What drops, the actual cardinality, or the estimated cardinality? And whichever it is, why shouldn't that happen? It seems to me that that is what you would expect.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB