如何取消一个列的直方图

zergduan

嘿嘿 也对

goobaile

for column xxx size 1
这种方法不是太灵验，反正针对我这张表直方图信息一直未修改成功。
而且指定的size并不一定出来的严格就是那么多个bucket。

棉花糖ONE

1.size=1表示只收集最大值和最小值 (不知道你遇到不灵验的是啥情况)
2.如果你指定的size大于这个列的唯一值,那么收集出来的bucket等于你的唯一值
3.bucket的最大值是254

Yong Huang

goobaile,

Always tell us Oracle version!

Your first query has recursive calls in the trace. So it's an unfair comparison. Run the first query one more time, or run the two queries in reverse order (second one first).

I'm sure setting _b_tree_bitmap_plans to false can improve the plan for your first query. But before we do that, can you make sure the table and index stats are close to actual values? That is, num_rows, num_distinct, etc are what you expect.

It's semantically confusing to say whether a "histogram" with only two buckets is a histogram or not. I think most people say it is not. So go with that convention and forget about it. Your problem doesn't look like histogram-related anyway.

I've noticed that sometimes the same where condition appears as both filter and access items under predicate information. I can never understand that. It could be a bug. If anybody has an idea, please tell us.

Yong Huang

magic007

如前所述，这个问题的确不能武断地认为是直方图引起了问题，我个人建议在OLTP系统中是将_b_tree_bitmap_plans设置为FALSE的。

棉花糖ONE

|* 12 |         INDEX RANGE SCAN           | INDX_NTHWKGRN_GROUPNMC |  1979 |       |   178   (0)| 00:00:03 |

这一步怎么会走成index range scan了,什么版本的oracle ??

Yong Huang

原帖由 棉花糖ONE 于 2009-9-7 21:02 发表
|* 12 |         INDEX RANGE SCAN           | INDX_NTHWKGRN_GROUPNMC |  1979 |       |   178   (0)| 00:00:03 |

这一步怎么会走成index range scan了,什么版本的oracle ??

If you use a bind variable, even if it's preceded by a literal string in string concatenation, Oracle uses index range scan on the column. This is true since at least later versions of 9iR2 (could be even earlier).

In short

This can use index range scan on column:
where column like '%' || :bindvariable || '%'

This cannot:
where column like '%' || 'literal string' || '%'
or
where column like '%literal string%'

So the OP's SQL is quite correct, and clever!

It would be nice if Oracle could silently treat the latter SQLs as the former ones. But even in 11gR2, that's not happening.

Yong Huang

[ 本帖最后由 Yong Huang 于 2009-9-8 12:33 编辑 ]

zergduan

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Sep 9 10:09:32 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table xyz as select to_char(rownum) id,'ABCD' b,'EFGH' c from dba_objects;

Table created.

Elapsed: 00:00:00.03
SQL> alter table xyz add constraint pk_xyz_id primary key (id);

Table altered.

Elapsed: 00:00:00.02
SQL> set autotrace traceonly exp
SQL> select * from xyz where id='55';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'XYZ'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_XYZ_ID' (UNIQUE)



SQL> var a char
SQL> select * from xyz where id like '%'||:a||'%';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'XYZ'
   2    1     INDEX (RANGE SCAN) OF 'PK_XYZ_ID' (UNIQUE)



SQL> select * from xyz where id like '%'||55||'%';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'XYZ'



学习
~

棉花糖ONE

我原来注意过这问题,我当时测试的是select * from t where col like :1;的情况,但是我没想到这里已经有%直接开头了还显示range scan

goobaile

ORACLE 10.2.0.3
1.size=1表示只收集最大值和最小值 (不知道你遇到不灵验的是啥情况)
指定size为1然后收集，查询DBA_TAB_HISTOGRAMS里面这张表的这个列还是有两条记录，同时执行计划未有任何改变（刷新过执行计划），使用delete统计信息的方式比较彻底，查询没有结果了。
2、确实设置 _b_tree_bitmap_plans 可能对这个问题会有帮助，但现在应用SQL已经添加了hint提示走索引。
3、另外一点我判断直方图在起作用是这么判断的，不知是否正确：
输入常量时，会走较好的执行计划，用绑定变量时可能因为开启了绑定变量窥视，用了bitmap那个执行计划。

index range scan这个问题倒还没真正研究过，Yong Huang 的解释和 zergduan的实验，受益匪浅。