奇怪的Global索引，各位版主分析

Yong Huang

原帖由 必有我师 于 2009-3-29 20:45 发表
...
2. 在计算代价公式的第三部分中CF*(table selectivity)，
   2.1 CF是针对整个索引，因为索引是非分区的，根据CF的定义，它是针对表中的所有记录而得到
   2.2 表选择率是基于单个分区的，假设我选出分区中的所有记录，即selectivity=1时，此时得到的代价是CF，即为根据rowid获取整个表的代价。(但实际上我们只需要根据rowid获取单个分区的记录)
   2.3 根据上面两点，我认为在计算代价时的第三部分是不合适的。

I see what you mean. I did give some thought to this. Let's see if we can conclude this way.

The "table" selectivity we talked about is probably indeed single partition's selectivity (let's call it PS). So the whole table's selectivity is TS=PS*#partitions. This means that given any key in index, the probability to find the table block having this key in the whole table is TS, and that to find it in one of the partitions is PS. OK?

Now the "第三部分中CF*(table selectivity)". It should be changed to CF * PS, not CF * TS. Note that PS is TS/#partitions, so this cost is (CF*TS)/#partitions. This last expression answers your question, doesn't it? It says give me all blocks in the entire table that have all the index keys (CF), out of those give me those that meet the probability of table selectivity (CF*TS), and out of those give me only one of all partitions (assuming there's no data skew).

By the way, have you noticed ix_sel (index selectivity) and ix_sel_with_filters ("table" selectivity; drop the quotation marks if the table is not partitioned) usually are the same or very close? In your case the former is 3.4 times as large as the latter. How many partitions do you have? 25? I don't know how to explain this value 3.4. I thought it was related to the number of partitions.

Yong Huang

必有我师

原帖由 Yong Huang 于 2009-3-31 02:01 发表


I see what you mean. I did give some thought to this. Let's see if we can conclude this way.

The "table" selectivity we talked about is probably indeed single partition's selectivity (let's call it PS). So the whole table's selectivity is TS=PS*#partitions. This means that given any key in index, the probability to find the table block having this key in the whole table is TS, and that to find it in one of the partitions is PS. OK?
Yes

Now the "第三部分中CF*(table selectivity)here really is PS, not TS.". It should be changed to CF * PS, not CF * TS. Note that PS is TS/#partitions, so this cost is (CF*TS)/#partitions. This last expression answers your question, doesn't it? It says give me all blocks in the entire table that have all the index keys (CF), out of those give me those that meet the probability of table selectivity (CF*TS), and out of those give me only one of all partitions (assuming there's no data skew).
It should be changed to CF * TS, not CF * PS. Note that TS is PS/#partitions, so this cost is (CF*PS)/#partitions.

By the way, have you noticed ix_sel (index selectivity) and ix_sel_with_filters ("table" selectivity; drop the quotation marks if the table is not partitioned) usually are the same or very close? In your case the former is 3.4 times as large as the latter. How many partitions do you have? 25? I don't know how to explain this value 3.4. I thought it was related to the number of partitions.

Yong Huang

我认为ix_sel和ix_sel_with_filter之所以不同是因为索引字段中的from_date,to_date，在查询条件中是不等值的，而对于不等值查询条件，索引中的后续条件将无法用到，就这个查询而言to_date将用不到，而只是作为来filter体现。

[ 本帖最后由 必有我师 于 2009-3-31 13:25 编辑 ]

必有我师

表分析的时候不做直方图就可以用到索引了，原因是基数估计精确了。也就是说是因为做了直方图导致优化器估计基数不准确导致？真的有点搞不懂直方图在搞什么鬼？
BTW：计算Cost时与原来一致，仍然使用的是单个分区的选择率和整个索引的CF，关于这点还是有点想不通。

必有我师

已经被确认是一个BUG，提交到Oracle development了。

Yong Huang

原帖由 必有我师 于 2009-4-9 19:16 发表
已经被确认是一个BUG，提交到Oracle development了。

Bug number please!

Yong Huang

必有我师

BUG No.: 8417859