Oracle语句中如果有2个LEFT JOIN ON 执行效率会不会很慢 该如何优化

[爱你是一种习惯]

上图分别为执行计划和SQL语句
SQL 语句我再粘贴一份

select t2.subjtypename,
       t2.balanorient,
       decode(t2.balanorient, '借', t3.debitamount, '贷', t3.creditamount) as mount,
       decode(t2.balanorient,
              '借',
              t3.creditamount,
              '贷',
              t3.localcreditamount) as local
  from (select t1.subjtypename,
               t.subjcode,
               t.subjname,
               t.pk_accsubj,
               decode(t.balanorient, '0', '借', '1', '贷') as balanorient
          from BD_ACCSUBJ t
          left join Bd_Subjtype t1
            on t.pk_subjtype = t1.pk_subjtype
         where t.pk_fibook = 'ee6867c616c841a4b91ac07dbad77f4a') t2
  left join GL_BALANCE t3
    on t2.pk_accsubj = t3.PK_ACCSUBJJ
 order by t2.subjcode

[weixin_30364005]

执行计划不太好，几个表的联接顺序有问题。去掉嵌套加hint看看？

[阿健来了]

这样会不会好点呢

select t1.subjtypename,
       t2.balanorient,
       decode(t2.balanorient, '借', t3.debitamount, '贷', t3.creditamount) as mount,
       decode(t2.balanorient,
              '借',
              t3.creditamount,
              '贷',
              t3.localcreditamount) as local
  from (SELECT t.subjcode,
               t.subjname,
               t.pk_accsubj
               decode(t.balanorient, '0', '借', '1', '贷') as balanorient
          from BD_ACCSUBJ t
         where t.pk_fibook = 'ee6867c616c841a4b91ac07dbad77f4a') t2
  left join GL_BALANCE t3
    on t2.pk_accsubj = t3.PK_ACCSUBJJ
  left join Bd_Subjtype t1
    on t2.pk_subjtype = t1.pk_subjtype
 order by t2.subjcode

[阿健来了]

感觉可以不用嵌套

[阿健来了]

select t1.subjtypename,
       t.balanorient,
       t.subjcode,
       decode(t.balanorient, '0', t3.debitamount, '1', t3.creditamount) as mount,
       decode(t.balanorient, '0', t3.creditamount, '1', t3.localcreditamount) as local
  from BD_ACCSUBJ t         
  left join GL_BALANCE t3
    on t.pk_accsubj = t3.PK_ACCSUBJJ
  left join Bd_Subjtype t1
    on t.pk_subjtype = t1.pk_subjtype
 where t.pk_fibook = 'ee6867c616c841a4b91ac07dbad77f4a'
 order by t.subjcode