求SQL优化

Question

select sum(num) as value
  from (select count(1) as num
          from icas_ar_accounting_t t
         where t.coa_type = 1 and not exists
         (select 1
                  from ICAS_SELECTED_LINES_T s, icas_accounting_process_t p
                 where t.company_Code = s.AR_COMPANY_CODE and
                       t.ic_Code = s.AR_IC_CODE and 
                       t.account = s.AR_ACCOUNT and
                       t.currency_code = s.AR_CURRENCY_CODE and
                       s.transactionflow_id = p.transactionflow_id and
                       p.status = '-1') and t.company_Code = '0311' and
               t.ic_Code = '1081' and
               t.business_type in
               (select distinct b.match_name
                  from icas_base_business_type_t b
                 where b.type = 'AP' and b.business_type_name = '应付货款')
        union all
        select count(1) as num
          from icas_ar_accounting_t t
         where not exists
         (select 1
                  from ICAS_SELECTED_LINES_T s
                 where t.CUSTOMER_TRX_ID = s.AR_CUSTOMER_TRX_ID) and
               t.coa_type = 0 and t.company_Code = '0311' and
               t.ic_Code = '1081' and
               t.business_type in
               (select distinct b.match_name
                  from icas_base_business_type_t b
                 where b.type = 'AP' and b.business_type_name = '应付货款')
        union all
        select count(1) as num
          from icas_ar_accounting_t      t,
               ICAS_SELECTED_LINES_T     l,
               icas_accounting_process_t p
         where t.CUSTOMER_TRX_ID = l.AR_CUSTOMER_TRX_ID and
               l.transactionflow_id = p.transactionflow_id and
               p.status != '-1' and l.ar_amounting_remaining > 0 and
               t.coa_type = 0 and
               l.last_update_date in
               (select max(s.last_update_date)
                  from icas_selected_lines_t s, icas_ar_accounting_t t
                 where t.CUSTOMER_TRX_ID = s.AR_CUSTOMER_TRX_ID and
                       t.business_type in
                       (select distinct b.match_name
                          from icas_base_business_type_t b
                         where b.type = 'AP' and
                               b.business_type_name = '应付货款')
                 group by s.AR_CUSTOMER_TRX_ID) and t.company_Code = '0311' and
               t.ic_Code = '1081' and
               t.business_type in
               (select distinct b.match_name
                  from icas_base_business_type_t b
                 where b.type = 'AP' and b.business_type_name = '应付货款'))

Answer 1

用EXPLAIN PLAN 分析下SQL， 看看cost 是多少? Taod,pl/sql 都可以，在SQLPLUS 也行..SQL>EXPLAIN PLAN FOR 你的sql语句; 
如 
SQL>EXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=7369; 
然后 
SELECT plan_table_output 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 
查看结果就是前面SQL语句的执行计划。 IN和EXISTS：
EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
 IN、OR子句常会使用工作表，使索引失效：
如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

Answer 2

尝试改写了一下，结果可能不相同，因为原代码中使用union all未剔除重复记录。
select count(case when  t.coa_type            = 1
                  and   not exists( select 1 
                                    from  ICAS_SELECTED_LINES_T s, 
                                          icas_accounting_process_t p 
                                    where t.company_Code       = s.AR_COMPANY_CODE 
                                    and   t.ic_Code            = s.AR_IC_CODE 
                                    and   t.account            = s.AR_ACCOUNT 
                                    and   t.currency_code      = s.AR_CURRENCY_CODE 
                                    and   s.transactionflow_id = p.transactionflow_id 
                                    and   p.status             = '-1'
                                   )
                  then  1
                  when  t.coa_type           = 0
                  and   not exists( select 1 
                                    from  ICAS_SELECTED_LINES_T s 
                                    where t.CUSTOMER_TRX_ID    = s.AR_CUSTOMER_TRX_ID
                                  ) 
                  then  1
                  when  t.coa_type           = 0
                  and   exists ( select 1 
                                 from  ICAS_SELECTED_LINES_T      l,
                                       icas_accounting_process_t  p
                                 where t.CUSTOMER_TRX_ID          = l.AR_CUSTOMER_TRX_ID
                                 and   l.transactionflow_id       = p.transactionflow_id
                                 and   p.status                  != '-1' 
                                 and   l.ar_amounting_remaining   > 0 
                                 and   l.last_update_date         = (select max(s.last_update_date) 
                                                                     from  icas_selected_lines_t s
                                                                     where t.CUSTOMER_TRX_ID    = s.AR_CUSTOMER_TRX_ID 
                                                                     group by s.AR_CUSTOMER_TRX_ID
                                                                    )
                                )
                  then  1
             end
            ) as value
from   icas_ar_accounting_t t
where  t.company_Code                        = '0311' 
and    t.ic_Code                             = '1081' 
and    exists (select 1
               from   icas_base_business_type_t b 
               where  b.type                 = 'AP' 
               and    b.business_type_name   = '应付货款'
               and    b.match_name           = t.business_type
              ) ;

Answer 3

这个是共有的：
              t.business_type in 
              (select distinct b.match_name 
                  from icas_base_business_type_t b 
                where b.type = 'AP' and b.business_type_name = '应付货款'))如果这个条件很费时，可以用WITH子查询先写好, 后面就可以反复使用。l.last_update_date in (select max(s.last_update_date) from icas_selected_lines_t s, icas_ar_accounting_t t ....group by s.AR_CUSTOMER_TRX_ID)
这个我怀疑有BUG, 因为你里面用了group by, 外面却只有一个last_update_date条件。
应该是icas_ar_accounting_t不写（用外层的），构成一个相关子查询，不用GROUP BY, 不用IN, 用=(SELECT MAX...)
如果是我猜测的这个意思，可以用RANK()分析函数代替。其他共同的查询条件，如and t.company_Code = '0311' and t.ic_Code = '1081'， 如果很费时也可用WITH先写好。
另外通过建立复合索引提高效率

Answer 4

group by可以去掉，可以用分析函数实现
公共条件没有多次调用，在#3中只写了一次，不需要with