原帖由 Yong Huang 于 2011-5-6 05:55 发表
Jonathan Lewis's "Cost-Based Oracle Fundamentals" pp.66-67 talks about it.
Yong Huang
Could you please show mepart of the text you mentioned above in "Cost-Based Oracle Fundamentals"pp.66-67?
Because I'vechecked the content you said in "Cost-Based Oracle Fundamentals" pp.66-67,
what I saw in page 66 is as follows(part of it):
We have crossed a partition boundary—Oracle has noted that we will be hitting partitions 1
and 2. Checking the table and column statistics, we find that between them the partitions have
160,000 rows, and the column has 400 distinct values with a range from 0 to 399, from which we
want the range 150 to 250. Let’s apply the same formula as last time: 160,000 * ((250 – 150)/399
+ 2/400 ) = 48,100. The result is not even close.
We could check to see whether Oracle has done two sets of arithmetic, one for the range
150 <= part_col < 200 and one for 200 <= part_col <= 250, and then added them. It hasn’t—this
would give the result 40,800.
The answer comes from the table-level statistics. There are 1,000,000 million rows, with
1,000 different values, and a range of 0 to 999, giving us 1,000,000 * ((250 –150) / 999 + 2/1,000)
= 102,100.
With multiple-known partitions at parse time, Oracle uses the table-level statistics.
The subject Jonathan talked about in pp.66-67 is about How to get cardinality with partitioned objects,
but my testing case is talking about how to calculate effective index selectivity on range predicates when there is a frequency histogram in place.
Anyway , thanks for your comment
|