Re: NL cost question

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: jclarke@xxxxxxxxxxxxxxx
  • Date: Tue, 01 Feb 2005 14:43:09 -0700

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.


Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: