Re: index skip scan
Date: Tue, 6 Jan 2009 09:47:12 -0800 (PST)
Message-ID: <f42e2fb5-0fae-4f21-873d-262bb3038da4_at_o4g2000pra.googlegroups.com>
On Jan 6, 12:27 pm, "jerni..._at_nospam.kochind.com" <jerni..._at_kochind.com> wrote:
> It shows a smaller index selectivity on the skip scan. How does that
> get calculated when the first column of the index is not a criteria?
This is the formula from "Cost-Based Oracle Fundamentals" page 62, which was originally created by Wolfgang Breitling: cost = blevel + ceiling(leaf_blocks * effective selectivity) + ceiling (clustering_factor * effective table selectivity[t])
I did not ask for the table selectivity, so if my algebra is correct
(note that the 0.50 is from the value for optimizer_index_cost_adj):
INV_TRANS_QTY_IND2
cost = (2 + ceiling(33244 * 7.9620e-04) + (1765553 * t)) * 0.50 =
726.76
cost = (2 + 27 + 1765553t) * 0.50 = 726.76
cost = 14.5 + 882776.5t = 726.76
t = 8.0684e-4
INV_TRANS_QTY_IND3
cost = (2 + ceiling(29916 * 1.6282e-04) + (2607782 * n)) * 0.50 =
497.31
cost = (2 + 5 + 2607782n) * 0.50 = 497.31
cost = 3.5 + 1303891n = 497.31
t = 3.7872e-4
In the above, the value for t should be roughly the same, unless Oracle or I made a calculation error. What is the effective table selectivity indicated in the 10053 trace?
Note that there are fewer leaf blocks in the INV_TRANS_QTY_IND3 index, which is the two column index, which probably means that the INV_TRANS_QTY_IND2 index experienced a greater percentage of 50/50 block splits than did the INV_TRANS_QTY_IND3 index.
Are there any histograms on the columns? On Oracle 10g, histograms may be created automatically when statistics are gathered.
Page 62 of the above referenced book states "The mechanics of a skip scan (which will appear in a later volume) require Oracle to go up and down the branch levels. Not only is the arithmetic different, but the strategy for pinning buffers changes too." It could very well be that the formula I referenced does not apply to skip scans.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Jan 06 2009 - 11:47:12 CST