【产品名称】
oceanbase私有云
【产品版本】
3.2.1
【问题描述】
背景：
业务找过来反馈一条sql前台跑了四分钟超时断了，问可以优化吗
语句如下
SELECT DISTINCT (SUBSID) SUBSID, REGION, SERVNUMBER FROM (SELECT A.SUBSID, A.SERVNUMBER, A.REGION FROM aaaa.TTTT1 A, aaaa.TTTT222 B WHERE A.STATUS NOT LIKE 'US2%%'AND B.CERTID ='371100111111111111’AND B.CERTTYPE in (‘cccc’,‘aaaaa’,‘wwww’,‘qqqqq’,‘ffffff’,‘cccccc’,‘11111’,‘222222’,‘33333’) AND A.CUSTID = B.CUSTID AND A.PRODID NOT IN (‘qweqwr’,‘ewrewr.post’,‘ert435’,‘trewer.post’,‘cvdfds’,‘shtyuyt.post’,‘sdaasc’,‘gtrhth.post’,‘pg.ewrj56111’, ‘pg.5676876hfg.111’,‘pp.hgikigru.500’,‘pp.23fsdfdsg.111’,‘pg.hjoighjzfdgd.111’) AND A.NETTYPE NOT IN (‘wefds’,‘werwef’,‘32r’,‘sdfdv’,‘cxv-VPN’,‘gf’,‘hgfhd’,‘sdf’,‘dsf’,‘rt’,‘jhj’,‘mn’,‘sdf’,‘jm’,‘aj’,‘vcc’, ‘56g’,‘dsf’,‘lk;’,‘vsd-NET’,‘fdsfw’,‘ewrfd’,‘3234’) AND (A.NETTYPE <>‘M2M’ AND LENGTH(A.SERVNUMBER) <> 13) UNION ALLSELECT A.SUBSID, A.SERVNUMBER, A.REGION FROM aaaa.TTTT1 A, aaaa.TTTT222 B WHERE A.STATUS NOT LIKE 'US2%%‘AND B.CERTID =‘371100111111111111’AND B.CERTTYPE in (‘kjerwk’,‘rewl;’,‘oiweuo’,‘450e8f0e’,‘oijfosdjf’,‘msdknfl’,‘mvlmlsdv’,‘sadf’) AND (A.USERID = B.CUSTID AND A.CUSTID <> A.USERID) AND A.PRODID NOT IN (‘ewrsd’,‘sdf.post’,‘dsf’,‘sdf.post’,‘dsv’,‘sdg.post’,‘sdg’,‘wef.post’,‘pg.sdf.iptv.111’,‘pg.sdf.sdf.111’,‘pp.sdf.dsg.500’,‘pp.wer.sdfwe.111’,‘pg.we.dsg.111’) AND A.PRODID NOT IN (‘M2M.sdf’,‘M2M.sdf.post’,‘M2M.wer.rz’,‘M2M.2’,‘M2M.4.post’,‘M2M.4’,‘M2M.5.post’,‘M2M.6’,‘M2M.7.post’, ‘pp.dt.er.111’,‘pp.dt.wer.111’,‘pp.dt.df.ewr’,‘pp.dt.asd.lh.111’,‘pp.dt.asd.111’,‘pp.dt.asd.asd.111’,‘pp.dt.pre.asd.111’,‘pp.dt.yy’, ‘pp.dt.asd’,‘pp.dt.asd.lhzq’,‘pp.dt.ad.111’,‘pp.dt.asd.111’,‘pp.eo.asd.111’,‘pp.eo.ads.111’,‘pp.eo.asd.111’,‘pp.eo.sad.111’,‘pp.eo.asd’, ‘pp.ott.111’, ‘pp.asd.111’,’ pp.qwe.111’,‘p p.dt.qwe23.111’) AND (A.NETTYPE <>‘M2M’ AND LENGTH(A.SERVNUMBER) <> 13)); 分析：
这语句看起来挺长其实不复杂，from中是两段sql进行了union all，两端sql表都一样只是条件不同
先拿出第一段看下
单独跑跑了两分钟，走了nest loop，两个表数据量分别是700w、1.2亿，我以为我找到问题点了，两段sql全加上hash的hint（/*+use_hash(A,B) */），分别跑了一下
第一段耗时9.75s执行计划如下（因有字数限制所以前几个执行计划的谓词信息我就不放出来了，直接截图了）：
第二段耗时6.33s执行计划如下：
到这里我以为问题解决了，两段sql单独执行时间都可以，两端union all跑了下，十分钟没出结果，看下执行计划
发现又走了nl而且还用到了temp，当时没想明白，求助了下ob的工程师，建议加下/*+NO_REWRITE */试试，测试发现执行时间17s
执行计划如下
这个执行计划和时间就符合预期了
原因及总结：
ob改写后的执行计划还是对两张表进行连接查询，并没有保留我在每一段中添加的hash jion，这也是为什么我单条sql测试都走了hash，union之后变成了nest loop，ob优化器认为合成一个连接查询代价会小一点，还是复用了原来的执行计划，但实际上hash代价更低，当我用no_rewrite让他不再复用缓存计划之后就找到了合适的执行计划。
发现新问题，和解决新问题的过程，真的让人心情愉悦，各位同仁共勉。
行之所向，莫问远方。