在Oracle 9i的CBO优化器中, 会对有绑定变量的SQL在硬分析时做一次Peaking, 这时如果分区表的各个分区的统计信息不一致不同能导致执行计划不同的话, Peaking就有可能出错了. 下面是我做的一个例子(9.2.0.5的库上测试的).
创建一个测试表:
SQL> create table t_partplan
2 (
3 col1 number not null,
4 col2 number not null,
5 col3 varchar2(40),
6 col4 varchar2(40)
7 )
8 partition by range(col1)
9 (
10 partition P1 values less than (5000),
11 partition p2 values less than (maxvalue)
12 );
Table created.
接下来插入一些数据, 建一个索引, 然后分析表, 将索引的一个分区联机重建.
insert into t_partplan
select OBJECT_ID, mod(object_id,100),
SUBOBJECT_NAME,TIMESTAMP from dba_objects
where object_id > 0;
create index idx_t_partplan on t_partplan(col2,col1) local;
ANALYZE TABLE t_partplan COMPUTE STATISTICS;
ALTER INDEX idx_t_partplan REBUILD PARTITION P2 ONLINE;
接下来来看按COL2去访问不同分区时的执行计划:
SQL> SELECT * FROM t_partplan PARTITION (P1) WHERE col2=99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=89
1 0 TABLE ACCESS (FULL) OF 'T_PARTPLAN' (Cost=8 Card=89
SQL> SELECT * FROM t_partplan PARTITION (P2) WHERE col2=99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=130
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_PARTPLAN'
2 1 INDEX (RANGE SCAN) OF 'IDX_T_PARTPLAN' (NON-UNIQUE)
如果你的SQL是只访问一个分区的, 有可能就会遇上这种问题, 我在重建索引时因为没有加"COMPUTE STATISTICS"选项, 而遇到了执行计划变坏的情况. 更多时侯我们是想不到, 不是做不到.