这个sql怎么优化？数据量大的时候速度超慢？？？
select bu.business_code ,business.businname,bu.area_code,area.areaname,
  count(bb.detail) khsm,
  sum(nvl(translate(bb.detail, '.0123456789'||bb.detail, '.0123456789'), 0)) khmoney,
  count(aa.errsufxz) sufxsm,  
  sum(nvl(translate(aa.errsufxz, '.0123456789'||aa.errsufxz, '.0123456789'),0)) sufxmoney from TASKDEFINED bu inner join area_config area
  on bu.area_code=area.area_code
  inner join BASEDICTIONARY business
  on business.businid=bu.business_code
  left join 
  CHECKDOCUMENT cc 
  on(bu.area_code=cc.area_code and cc.disabled='0'and bu.business_code=cc.business_code 
  and cc.CHECK_DATE=to_date('2012-05-28','YYYY-MM-DD') )

  left join
  (
  select e.checkdocument_id,
  decode (cc.document_type,1,e.acc_amount) detail,
  decode (cc.document_type,2,e.acc_amount) xzd
  from (select * from checkDocumentDetail partition (checkDocumentDetail_201205))) e,CHECKDOCUMENT cc 
  where e.checkdocument_id=cc.checkdocument_id  
  )bb 
  on bb.checkdocument_id=cc.checkdocument_id and cc.disabled=0 and cc.CHECK_DATE=to_date('2012-05-28','YYYY-MM-DD')
  left join (
  select e.checkdocument_id,
  decode(e.err_code||e.op_status, ''03''||5, e.acc_amount) errsufxz
  from ERRDOCUMENTDETAIL e 
  ) aa
  on cc.checkdocument_id = aa.checkdocument_id and cc.document_type = 3 and cc.disabled=0 and cc.CHECK_DATE=to_date('2012-05-28',''YYYY-MM-DD'')

  where bu.disabled=0 
  and bu.BUSINESS_CODE in (select bus.businid from BASEDICTIONARY bus where bus.BUSINTYPEID='BUSINESS_CODE'
  and bus.PRIVILEGE=220)
  group by bu.business_code, bu.area_code,business.businname,area.areaname
  order by bu.area_code

------解决方案--------------------
我对SQL不在行，不过再长的SQL都执行过，这个正常——————
------解决方案--------------------
楼主应该尝试着将这样一步完成的sql分部执行。
或者查看一下，执行计划时候使用了索引
------解决方案--------------------
1、把IN语法改成EXISTS
and bu.BUSINESS_CODE in (select bus.businid from BASEDICTIONARY bus where bus.BUSINTYPEID='BUSINESS_CODE'
and bus.PRIVILEGE=220)

and exists select 1 from BASEDICTIONARY bus 
where bu.BUSINESS_CODE=bus.businid 
and bus.BUSINTYPEID='BUSINESS_CODE'
and bus.PRIVILEGE=220

2、用了LEFT JOIN 和 GROUP BY 
试着把联立条件建立对应的索引试试。

------解决方案--------------------
指定索引 /*+index(A) INDEX(B)*/
------解决方案--------------------
数据量？执行计划？索引？