ARTICLE DETAIL

资讯详情

深耕网站建设、视觉设计与SEO优化的一线实战洞察。

union all语句优化改写经验分享

union all语句优化改写经验分享

1、问题
前面几个案例是因为提前生成HEAP TABLE,条件没有提前过滤导致性能问题,今天想跟大家分享一个案例是适合做HEAP TABLE优化的。原语句如下:

SELECT * FROM(SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM(SELECT T1.CODE,casewhenT1.CODE='-9999'then'合计'elset2.cname end cname, nvl(firstnum,0)firstnum, nvl(secondnum,0)secondnum, nvl(thirdnum,0)thirdnum, nvl(fourthnum,0)fourthnum, nvl(fifthnum,0)fifthnum from(select'-9999'code, sum(case whenstatus=1then1else0end)firstnum, sum(case whenstatus=2then1else0end)secondnum, sum(case whenstatus=3then1else0end)thirdnum, sum(case whenstatus=4then1else0end)fourthnum, sum(case whenstatus=5then1else0end)fifthnum from t1 where c1 like'A1%'union allselectcode, sum(case whenstatus=1then1else0end)firstnum, sum(case whenstatus=2then1else0end)secondnum, sum(case whenstatus=3then1else0end)thirdnum, sum(case whenstatus=4then1else0end)fourthnum, sum(case whenstatus=5then1else0end)fifthnum from t1 where c1 like'A1%'group by code)t1 leftjoint2 ont1.code=t2.code ORDER BY T1.CODE)INNER_TABLE)OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=200AND OUTER_TABLE_ROWNUM>0

计划:

从计划上对t1表过滤重复两次,从语句中可以看到

select'-9999'code, sum(case whenstatus=1then1else0end)firstnum, sum(case whenstatus=2then1else0end)secondnum, sum(case whenstatus=3then1else0end)thirdnum, sum(case whenstatus=4then1else0end)fourthnum, sum(case whenstatus=5then1else0end)fifthnum from t1 where c1 like'A1%'union allselectcode, sum(case whenstatus=1then1else0end)firstnum, sum(case whenstatus=2then1else0end)secondnum, sum(case whenstatus=3then1else0end)thirdnum, sum(case whenstatus=4then1else0end)fourthnum, sum(case whenstatus=5then1else0end)fifthnum from t1 where c1 like'A1%'group by code

是union all两部分,一部分是合计,另一部分是分组统计,此时我想到可以先分组统计,再把分组统计结果进行合计,分组统计的结果可以放到临时表中即heap table,从数学角度上讲就是提取公因子。
2、改写

SELECT * FROM(SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM(SELECT T1.CODE,casewhenT1.CODE='-9999'then'合计'elset2.cname end cname, nvl(firstnum,0)firstnum, nvl(secondnum,0)secondnum, nvl(thirdnum,0)thirdnum, nvl(fourthnum,0)fourthnum, nvl(fifthnum,0)fifthnum from(with temp as(selectcode, sum(case whenstatus=1then1else0end)firstnum, sum(case whenstatus=2then1else0end)secondnum, sum(case whenstatus=3then1else0end)thirdnum, sum(case whenstatus=4then1else0end)fourthnum, sum(case whenstatus=5then1else0end)fifthnum from t1 where c1 like'A1%'group by code)select'-9999'code, sum(firstnum)firstnum, sum(secondnum)secondnum, sum(thirdnum)thirdnum, sum(fourthnum)fourthnum,sum(fifthnum)fifthnum from temp t1 union allselect* from temp)t1 leftjoint2 ont1.code=t2.code ORDER BY T1.CODE)INNER_TABLE)OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=200AND OUTER_TABLE_ROWNUM>0

计划:

结果符合预期,性能提升一倍以上。

3、小结

使用union all和group by rollup(前面案例中有提过-单表查询-group by rollup优化)进行数据统计的sql优化思路就是提取公因子,利用临时表(HEAP TABLE)减少扫描次数,从而提升性能。
前面讲过优化不是单向,前面是讲HEAP TABLE如何拆解优化,而这次是如何用HEAP TABLE减少扫描次数优化,优化做的事不过是对症下药。

4、附加测试数据

create table t1(id int primary key,c1 varchar2(20),status int,code varchar2(20));insert into t1selectlevel,'A'||to_char(round(dbms_random.value(1,10000),0)),round(dbms_random.value(1,5),0),'0'||to_char(round(dbms_random.value(300,322),0))from dual connect by level<=300000;commit;dbms_stats.gather_table_stats(USER,'T1',null,100);create table t2(id int primary key,cname varchar2(20),code varchar2(20));insert into t2selectlevel,'A'||to_char(round(dbms_random.value(300,322),0)),'0'||to_char(round(dbms_random.value(300,322),0))from dual connect by level<=10000;commit;dbms_stats.gather_table_stats(USER,'T2',null,100);create index IDX_DM_T1_C1 on T1(c1);create index IDX_DM_t2_CODE on T2(CODE);
返回列表