Re: NL cost question

Actually, rsc_io the rsc_io cost "formula" is something like

    rsc_io = lvls + IX_SEL*#LB + TB_SEL*CLUF

Depending on the index access type (scan, no sta/stp key, index-only, join-index, index-eq, ...) parts of that may be omitted, e.g. for index-only the tb_sel*cluf component does not apply, of course, and for index-eq it reduces to just lvls.

For your index access types, join-index and index-only, the ix_sel*#lb part ought to apply, and for join-index even the tb_sel*cluf component - at least I have seen values in 10053 traces that support that theory, certainly much higher than the index height (lvls). The IX_SEL values for the F and _ indexes are certainly high enough to make the ix_sel*#lb component "disappear". Check if the same is true for the E index.

John Clarke wrote:

> I've got a query that CBO is generating a "bad" plan for and was wonderi=
> ng whether anyone could shed some light ...
>
>
> Am I interpreting this correctly=3F And if so, what can be done about i=
> t short of hints and/or seeding bogus blevel statistics=3F Is this situ=
> ation the join uniformity assumption fallacy that I've read about, or am=
> I misinterpreting the statistics=3F
>

I don't believe this has anything to do with the "join uniformity assumption fallacy" since the CBO got the cardinality right. It is a matter that it has the choice of indexes that are equally suited and the cost is marginally different and the actually less suitable index happen to have a slightly lower cost.
What can you do about it? The only predicate that is responsible for choosing index E is business_unit and since you have another index with business_unit you could change the column order of the E index, making it ineligible for this join, or only through a skip-scan which should have a higher cost.
A negative effect of such an index change could be that queries with just business_unit as a predicate will be forced to use the ps_ index and that appears to have more levels and especially more #LB and a potentially a higher CLUF and will therefore perform worse.

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l