CBO规则下的优化器还是要按"规则"办事

最近遇到了几个生产案例, 整理了其中两个, 把它们做成了test case, 分享给大家.

test case 1: 关于降序索引

表:

create table t3 as select * from dba_objects;

索引:

create index idx_t3_object_id_desc on t3(object_id desc);

模拟业务SQL:

select * from

(select object_id,object_name

from t3

where object_id is not null

order by object_id desc

)where rownum<=10;

这个sql 不能使用上面创建的desc降序索引, 加hint也不行. 如果把上面索引里面的desc去掉, 就可以正常使用索引了.

如果一定要使用desc索引(有的时候可能会有多字段参与order by,而且有desc和acs不同方向), 可以试试下面两个方法:

如果object_id 没有null值: alter table t3 modify object_id not null;

如果object_id有null值,就创建(object_id desc,0) 的组合索引

其实最好的方法就是创建object_id 字段上的普通索引, 不要加desc, 不用与0做联合, 也不需要modify object_id not null(谓词条件已经有了), 这个观点我在以前的文章 <79-不要看到有order by xxx desc就创建desc降序索引-文末有赠书福利> 里面提到过.

另外, like 'xxxxx%' 也用不了降序索引.

test case2: with as写法

有些人把with as 的写法当成了SQL优化的方法,好像用了这个语法就能让SQL效率提高, 下面这个案例是把一个复杂的生产案例做了简化, 让大家了解一下with as的写法, 在某些情况下还能起到恶化的作用:

表:

create table t1 as select * from dba_objects;

索引:

create index idx_t1_object_id on t1(object_id);

模拟业务SQL:

with tmp as

(

select * from t1 where owner='SYS'

)

select count(*) from tmp where object_id=100

union all

select count(*) from tmp where object_id=200;

上面SQL, 因为tmp被使用了两次, 被优化器自动做了materialize, 不能使用object_id字段上的索引, 效率很差.

这个SQL的写法, 如果要达到最佳性能, 就不能让tmp做materialize, 通过加inline hint实现:

with tmp as

(select /*+ inline */* from t1 where owner='SYS')

select count(*) from tmp where object_id=100

union all

select count(*) from tmp where object_id=200;

这个test case大家可以通过比较执行计划很容易看出优劣. 关于with as的其他两篇文章, 请回顾: < 87-with as写法的5种用途 > 与 <记一个Enq: ss - contention性能问题处理 >

上面两个案例, 从11g到19c, 都是一样的情况.

上面两个案例我认为优化器应该能够做出最好的选择, 实际并不如我们想象的那么美好. oracle数据库有公认的最强大的优化器, 强大如此, 也有一些可以改进的地方. oracle 的优化器是CBO (costed based optimizer), 实际上也是按照现有的规则在"办事", 没有考虑到的地方, 就要靠我们人脑来做补救.

(完)