with as写法,学名common table expression,简称CTE, 顾名思义,主要目的是把公共表达式提取出来重复使用. 在我之前的文章 87-with as写法的5种用途 中有描述. 很多开发人员和DBA经常误以为with as写法是一种天生的自带优化功能的写法(包括很多AI),实际上理解错了. 下面来看一个简单例子(生产案例简化而来):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
;
说明:
t1表owner='SYS'的记录数占比较多;
object_id字段上存在高效索引.
在oracle数据库中(版本19.19), 上面这个SQL的CTE写法不但起不到优化作用, 反而起到了负面作用,执行计划如下:高效的执行计划应该是下面这样的, 那个inline hint就是这个SQL的优化密码 :oracle数据库cte写法的上面表现是一个叫"_with_subquery"的隐含参数控制的, 默认值是"optimizer", 另外两个可选值分别是materialize和inline. 如果数据库中还存在下面SQL,则不管设置哪个参数, 如果不加hint,都不能让下面这个SQL与前面SQL同时高效执行, 前面那个SQL需要inline,下面这个SQL则适合materialize:with tmp as
(
select owner,count(*) from t1 group by owner
)
select * from tmp where owner='SYS'
union all
select * from tmp where owner='PUBLIC'
;
==========================================同样的两个SQL我拿到oceanbase数据库上执行, 在默认参数情况下,都能自动生成高效执行计划:我在oracle数据库(19c)遇到很多需要改写才能高效执行的SQL, 拿到oceanbase库上执行, 不用改写也能得到高效执行计划, 看来ob优化器在查询转换方面还是花了很多心思的, 有点让我刮目相看了.之前已经有两篇相关文章介绍了oceanbase相较于oracle优化器在查询转换方面有优势的一些场景,后续还有10多个场景, ob表现也都比oracle优秀, 有时间我会慢慢写出来.
