关于sql语句的优化问题
sql语句如下所示：
select a.an, a.num, b.num
from (select a.an, count(distinct b.cp) as num
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an) a,
(select a.a, sum(bn.cp) as num
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an) b
where a.n = b.n
该语句的执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWSCost=43592665Cardinality=2457Bytes=270270
HASH JOINCost=43592665Cardinality=2457Bytes=270270
VIEWObject owner=PORTAL_DATACost=21705301Cardinality=2457Bytes=135135
SORT GROUP BYCost=21705301Cardinality=2457Bytes=105651
NESTED LOOPSCost=21691981Cardinality=66218330Bytes=2847388190
TABLE ACCESS FULLObject owner=PORTAL_DATAObject name=xq_jzw Cost=790Cardinality=2458Bytes=46702
TABLE ACCESS FULLObject owner=PORTAL_DATAObject name=paperCost=8825Cardinality=26941Bytes=646584
VIEWObject owner=PORTAL_DATACost=21887364Cardinality=2457Bytes=135135
HASH GROUP BYCost=21887364Cardinality=2457Bytes=95823
NESTED LOOPSCost=21874044Cardinality=66218330Bytes=2582514870
TABLE ACCESS FULLObject owner=PORTAL_DATAObject name=xq_jzw Cost=790Cardinality=2458Bytes=46702
TABLE ACCESS FULLObject owner=PORTAL_DATAObject name=paperCost=8899Cardinality=26941Bytes=538820
请问：该语句怎么优化一下，让其执行速度比较快，xq_jzw数据量17万，paper数据量300万
[解决办法]
- SQL code
select a.an, count(distinct b.cp) as c_num sum(bn.cp) as s_num,from xq_jzw a, paper bwhere a.is_xq = 'Y' and instr(b.ad,a.an) =1group by a.an
[解决办法]
问题出在xq_jzw a, paper b两表的连接上，连接条件instr()这里代价很大。结果对吗？连接后的结果是不是有很多重复的记录