朋友发来一个SQL:
select distinct owner from tbig where owner is not null;
已知tbig表很大, owner 的唯一值个数比较少, 问如何利用owner字段上的普通索引,让上面的查询做到最优(不考虑位图索引和物化视图的方法)
(tbig表几千万记录以上, 如果要模拟, 可以通过dba_object几次复制, 生成几十万记录就可以了, 然后创建owner字段上的索引)
一开始看到这个sql也是没什么思路, 我在客户的生产系统上看到过一个完全类似的SQL. 出现这种SQL, 我的第一反应是设计上的问题, 好的设计应该存在一个很小的表, 只保存owner信息, 上面sql改从这个小表查就OK了. 所以说最好的优化还是设计出来的.
但是现在就是要优化这个看起来没有什么优化思路的SQL. 经过提示说可以通过递归实现, 我就参考了PG数据库的一个类似优化写法, 生成了oracle的写法, 大概只需要100多个buffer 就能得到结果:
with tmp (owner) as
(
select min(t.owner) as owner from tbig t
union all
select (select min(t.owner) from tbig t where t.owner > s.owner)
from tmp s
where s.owner is not null
)
select owner from tmp
where owner is not null
;
有个学员也给出了他的写法:
with r(owner) as
(
select owner from (select t.owner from tbig t where t.owner is not null order by t.owner) where rownum = 1
union all
select t.owner from r join tbig t on t.owner > r.owner where rownum = 1
)
select * from r;
这个写法看起来只比上面写法稍差了一点(150多buffer), 但是存在一个比较严重的问题, 那就是如果索引失效, 结果集就不对了, 这种改写是不可接受的，但是解题思路还是非常正确的.
下面再把这个sql"简化"一下, 看看你能不能在上面的基础上, 用最优的方法得到下面的结果(owner 字段定义是可为null):
select distinct owner from tbig;
就是上面的SQL去掉了where owner is not null, 如果owner有null值，也要返回.有兴趣的朋友可以在留言区留言或加本人微信(ora_service)讨论，可能你的方法比我能想到的方法更好.
补充:
这种sql, PG和oracle都要用到复杂的递归才写法能优化, 而mysql根本不需要任何改写, 就能达到PG和oracle复杂优化写法的效果. 其实oracle如果要实现这个功能应该也比较简单, 只是没有去搞而已 ,oracle的 index skip scan 很多年前就有了.
全文完
