CBO rows estimate way out in 2 table join - what to do?

I have two tables - account and custProductDetails. Each table contains (amoung other columns) account_num and customer_ref and you can join from account to custProductDetails using an index on custProductDetails that contains customer_ref and account as the leading columns.

Account has about 10K rows, custProductDetails about 70Million.

I know that my query with filters will return close to 10K rows on the account table, and when it joins that to the custHasProduct table, it will return the majority of the rows in the table too (69Million when I did a count(*) on the table) - however Oracle believes the query will only return about 7K rows.

This query is then used as part of another query which chooses a terrible access path because of this.

Using the formula in Cost Base Oracle, I know the selectivity Oracle uses as part of a join is:

((rows(account) - num_nulls(account.account_num)) / rows(account)) *
((rows(custProdDetails) - num_nulls(custProdDetails.account_num) /
rows(custProdDetails)) /
greater(num_distinct(account.account_num, custProdDetails.account_num)

multiplied by the result for the same calculation on customer_ref (as we have two join columns).

Putting all my numbers in gives me an answer close to that which Oracle is calculating at around 7K, but it should be more like 70Million!

I have played around with histograms on some of the columns, but am not getting anywhere fast!

Is this a common join problem - what can I do to make Oracle guess more accurately? I am very reluctant to stick an ORDERED hint into the query, as hope to get the optimizer to make the correct decision somehow.

Thanks,

Stephen. Received on Mon Oct 29 2007 - 11:43:47 CDT