97- 优化select distinct owner from tbig

朋友发来一个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 很多年前就有了.

全文完