Re: index skip scan
Date: Tue, 6 Jan 2009 11:12:58 -0800 (PST)
Message-ID: <a66649d7-551d-40d9-b29a-7d7d57f7128e_at_l38g2000vba.googlegroups.com>
There are no histograms.
The indexes are in the same tablespace, so the block sizes are the same.
In the CBO book, it says that the effective table selectivity is ix_sel_with_filters which is the same as the ix_sel for the indexes. But that doesn't quite work in the functions.
That would give me a cost of
INV_TRANS_QTY_IND2
cost = (2 + ceiling(33244 * 7.9620e-04) + (1765553 * 7.9620e-04)) *
0.50
cost = (2 + 27 + 1406) * 0.50 =717.5 ---This one is pretty close
INV_TRANS_QTY_IND3
cost = (2 + ceiling(29916 * 1.6282e-04) + (2607782 * 1.6282e-04)) *
0.50
cost = (2 + 5 + 425) * 0.50 = 218 -- this one is not
So I don't think the formula is working for skip scans.
The selectivity doesn't seem right.
For inv_trans_qty_ind2, if I use
(val1-val2)/(high value-low value)+ 2/num distinct
(Dec 5- Dec 1)/(Jan 1 2013 - Jan 1 1993) + 2/4022
4/7035+2/4022
.000568586+.00497265 = .001065 instead of .0007962
Or is the IX_SEL calculated differently? Received on Tue Jan 06 2009 - 13:12:58 CST