关于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_ROWS Cost=43592665 Cardinality=2457 Bytes=270270
 HASH JOIN Cost=43592665 Cardinality=2457 Bytes=270270
  VIEW Object owner=PORTAL_DATA Cost=21705301 Cardinality=2457 Bytes=135135
  SORT GROUP BY Cost=21705301 Cardinality=2457 Bytes=105651
  NESTED LOOPS Cost=21691981 Cardinality=66218330 Bytes=2847388190
  TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=xq_jzw Cost=790 Cardinality=2458 Bytes=46702
  TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=paper Cost=8825 Cardinality=26941 Bytes=646584
  VIEW Object owner=PORTAL_DATA Cost=21887364 Cardinality=2457 Bytes=135135
  HASH GROUP BY Cost=21887364 Cardinality=2457 Bytes=95823
  NESTED LOOPS Cost=21874044 Cardinality=66218330 Bytes=2582514870
  TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=xq_jzw Cost=790 Cardinality=2458 Bytes=46702
  TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=paper Cost=8899 Cardinality=26941 Bytes=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 b
where a.is_xq = 'Y'
      and instr(b.ad,a.an) =1
group by a.an

------解决方案--------------------

问题出在xq_jzw a, paper b两表的连接上，连接条件instr()这里代价很大。结果对吗？连接后的结果是不是有很多重复的记录