oracle 优化查询效率

oracle 优化查询效率
select t.dwmc,t.zgjl,t.zbjl,t.zqjl,t.xljl,t.skjl,t.bgjl,
t.bqjl,t.bgjl2,t.pxjl,t.ccjl,
(zbjl+zqjl+xljl+skjl+bgjl+bqjl+bgjl2+pxjl+ccjl) as hz
from (select code_mean as dwmc,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and b.zgzt in('0101','0102','0103','0104','0205','0206','0105')) zgjl ,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0102' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zbjl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0103' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zqjl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0205' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xljl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0206' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) skjl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0101' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0104' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bqjl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0105' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl2,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0201' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) pxjl,
(select count(*) from tableoneb, tabletwo a where a.leader_code=b.leader_code and a.tabletwo99<'5' and b.zgzt='0202' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) ccjl
from zb02_jgmc where code like '500107%' ) t
如何提高查询效率.请说明具体.谢谢.

SELECT T.DWMC,
T.ZGJL,
T.ZBJL,
T.ZQJL,
T.XLJL,
T.SKJL,
T.BGJL,
T.BQJL,
T.BGJL2,
T.PXJL,
T.CCJL,
(ZBJL + ZQJL + XLJL + SKJL + BGJL + BQJL + BGJL2 + PXJL + CCJL) AS HZ
FROM (SELECT CODE_MEAN AS DWMC,
(SELECT CASE WHEN B.ZGZT IN('0101', '0102', '0103', '0104', '0205', '0206', '0105') THEN COUNT(*) AS zghl,
CASE WHEN B.ZGZT IN('0102') THEN COUNT(*) AS ZBJL,
CASE WHEN B.ZGZT IN('0103') THEN COUNT(*) AS ZQJL,
CASE WHEN B.ZGZT IN('0205') THEN COUNT(*) AS WXJL,
CASE WHEN B.ZGZT IN('0206') THEN COUNT(*) AS SKJL,
CASE WHEN B.ZGZT IN('0101') THEN COUNT(*) AS BGJL,
CASE WHEN B.ZGZT IN('0104') THEN COUNT(*) AS BQJL,
CASE WHEN B.ZGZT IN('0105') THEN COUNT(*) AS BQJL2,
CASE WHEN B.ZGZT IN('0201') THEN COUNT(*) AS PXJL,
CASE WHEN B.ZGZT IN('0202') THEN COUNT(*) AS CCJL
FROM TABLEONEB, TABLETWO A
WHERE A.LEADER_CODE = B.LEADER_CODE
AND A.TABLETWO99 < '5'
AND B.SZDWDM = CODE
AND TO_CHAR(B.RQ, 'yyyy-MM-dd') =
TO_CHAR(SYSDATE, 'yyyy-MM-dd')
) A
FROM ZB02_JGMC
WHERE CODE LIKE '500107%') T