问题描述

接客户运维人员通知，需对开发提供的两个SQL进行优化。SQL如下：

SQL1：fmcp81ncj094z

select * from ( select row_.*,rownum rownum_ from (
            select (select institutions_name from zt_institutions where institutionsid=b.zba003) DISTRICTNAME,(select codename from code where parentid=(select codeid from code where codename='业务类型') and codevalue=b.zba004) BUSINESSTYPE,
             b.zba001 SERIALNUM,c.aac002 SPONSORIDCARD,c.aac003 SPONSORNAME,(select codename from code where parentid=(select codeid from code where codename='性别') and codevalue=c.aac004) SPONSORSEX,
             (select codename from code where parentid=(select codeid from code where codename='民族') and codevalue=c.aac005) SPONSORNATION,b.zba019 AUDITSTATE,b.zba031 PAYSTATE,
             (select codename from code where parentid=(select codeid from code where codename='国籍') and codevalue=c.zca036) SPONSORNATIONALITY,
             c.zca037 VALIDDATE,(select codename from code where parentid=(select codeid from code where codename='职业') and codevalue=c.zca038) SPONSOROCCUPATION,
             (select codename from code where parentid=(select codeid from code where codename='银行') and codevalue=c.zca010) BANKCODE,c.aac001 SPONSORSINUM,c.aae005 SPONSORPHONE,
             c.zca008 AGENTIDCARD,c.zca006 AGENTNAME,c.zca009 AGENTPHONE,to_char(c.zca012,'yyyy-mm-dd') TRANSACTTIME,(select user_name from zt_user where to_char(userid)=b.zba030) OPERATOR,
             (select codename from code where parentid=(select codeid from code where codename='证件类型') and codevalue=c.zca022) CREDENTIALSTYPE,zkbs MAKECARDSTATE,
             (select case when (select count(*) from zc01_fj where zba001=c.zba001 and zcf003='05' and zcf005='qrb.jpg')>0 then '1' else '0' end from dual) SURETABLESTATE,
             (select case when yac181='2' then '1' else '0' end zt from ac29 where yac005=c.yac005 and rownum=1) BADCARDSTATE
            from zb01_a b inner join zc01_a c on b.zba001=c.zba001 where b.zba005='3' and b.zba006='1' and c.isdelete='0' and b.thhk is null
                    and b.zba003 in (select institutionsid from zt_institutions start with to_char(institutionsid)= :1  connect by prior institutionsid = pid)
                and to_char(c.zca012,'yyyy-mm-dd') >= :2 
            order by c.zca012 desc,c.rowid
        ) row_ where rownum < :3 ) where rownum_ >= :4 

SQL2：1c57k4jmahtx0：

select count(*) from zb01_a b inner join zc01_a c on b.zba001=c.zba001 where b.zba005='3' and b.zba006='1' and c.isdelete='0' and b.thhk is null         
                and b.zba003 in (select institutionsid from zt_institutions start with institutionsid= :1  connect by prior institutionsid = pid)              
            and to_char(c.zca012,'yyyy-mm-dd') >= :2 

SQL执行计划分析

SQL1：执行计划

Plan hash value: 3467801272

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                 |       |       | 21205 (100)|          |