对于超多关联的select SQL，性能优化好像能做的事情很少

14个join的select语句,头大！
能做性能优化修改的地方有以下几个：
1.where 和on条件中尽可能按照index顺序来排列条件，并且尽可能不在条件左边使用函数，比如substr可以用like 替代。如果非得使用函数，则把带有函数的条件排到后面。
2.按照业务逻辑能用inner join的地方不使用left join，这个语句有个地方inner join ls换成left join，时间要增加8倍！分别是30秒和240秒
3.join 后面的表尽可能不使用子查询

insert into bills
  select
    *--简化
  from  ixqdwdel d
  inner join (select s.entity,s.deal_no,s.step_open_date,
        nvl(nvl(s.offering_date,s.rel_date_b),s.rel_date_a) rel_date
        from ixqdwstp s where s.entity=entity_c and s.step_id = 'ISS000'
        and s.step_status='4') iss
        on iss.entity=d.entity and iss.deal_no=d.deal_no
  inner join ixqdwcna c --customer info
        on d.customer_id=c.customer_id and d.entity=c.entity
  inner join (select * from (select x.entity,x.currency_code,x.rate_1/10000000 rate,x.rate_2/10000000 rate2,
            dense_rank() over (partition by x.entity,x.currency_code order by x.date_key) d,x.date_key
             from ixqap098 x
            where x.entity=entity_c ) a where a.d=1) x
        on d.entity=x.entity and d.deal_curr=x.currency_code
  left join (select * from
       (select l.*,dense_rank() over(partition by l.deal_no order by substr(l.step_id,4,3) desc)seq
       from ixqdwolc l where l.entity=entity_c) l where l.seq=1) l --LC/LG
       on d.entity=l.entity and d.deal_no=l.deal_no
  left join ixqdwicd i on d.deal_no=i.deal_no and d.entity=i.entity and i.step_id='ISS000'

  left join ixqdwdpr p
       on d.entity=p.entity and d.deal_no=p.deal_no and p.step_id='ISS000' and p.party_code='ISB' 
  left join ixqdwexs be
       on p.party_id=be.party_id and be.entity=p.entity and p.party_ext=be.extension_no
  left join ixqdwdpr rp
       on d.entity=rp.entity and d.deal_no=rp.deal_no and rp.step_id='ISS000' and rp.party_code='RMB'

  left join ixqdwexs ber
       on rp.party_id=ber.party_id and ber.entity=rp.entity and rp.party_ext=ber.extension_no
  left join (select * from (select s.*,dense_rank() over(partition by s.deal_no order by s.time_stamp desc) seq
       from ixqdwstp s where s.entity=entity_c and s.step_status='4'
       and (s.step_id like 'PAY%' or s.step_id like 'ACP%')) s where seq=1) doc
       on d.entity=doc.entity and d.deal_no=doc.deal_no
  inner join (select * from (select s.*,dense_rank() over(partition by s.deal_no order by s.time_stamp desc) seq
       from ixqdwstp s where s.entity=entity_c and s.step_status='4') s where seq=1) ls
       on d.entity=d.entity and d.deal_no=ls.deal_no
  left join (select trim(ld.owner_id) owner_id,ld.limit_key,ld.limit_no,
            ld.limit_categ_desc,ld.deal_no,ld.entity from (
       select ld.*,lm001.limit_categ_desc,dense_rank()
       over(partition by ld.entity,ld.deal_no order by ld.limit_key) seq from
       ixqdwlde ld inner join ixqlm001 lm001
       on ld.entity=lm001.entity and ld.limit_key=lm001.limit_categ_code
       where ld.entity=entity_c and lm001.funded_unfunded='U'
       ) ld where seq=1 )lm
      on d.entity=lm.entity and d.deal_no=lm.deal_no     

  left join ixqdwchg ch on d.entity=ch.entity and d.deal_no=ch.deal_no
          and ch.entity=entity_c and (ch.charge_id like '90%1')
          and ch.step_type ='ISS'
  left join ixqap129 ap129--
            on ch.entity=ap129.entity and ch.charge_id=ap129.debit_credit_id
            and ch.charge_curr = ap129.currency_code


  where d.entity=entity_c
  and d.product in ('01','02','11','12')
  and d.last_rel_step_id not like 'BKF%'
  and length(trim(d.customer_id))=10
  and translate(trim(d.customer_id),'\1234567890','\') is null
  ;